July 15th, 2013, 06:52 PM
Php mysql query (replace) question
I'm having a php problem running a query that runs without problem in mysql. I have a table with a field that has values that are giving me problems. The field holds string variables with data like "Bob Jones" <email@example.com>, but can also have the same value without the quotes i.e. Bob Jones <firstname.lastname@example.org>. I came up with a mysql query that strips off the quotes using the replace function.
select distinct(replace( replace(EmailFrom, ',', ''), '"', '' )) as EmailFrom from EmailHouseholdTerm where ID = 999 order by EmailFrom
The query works fine when I run it in phpmyadmin, but when I add it to php I get an error. Interestingly, if I copy the value of $query and paste it into phpMyadmin the query works fine. Can anyone give me a answer to what the problem is?
$query = "select distinct(replace( replace(EmailFrom, ',', ''), '\"', '' )) as EmailFrom from EmailHouseholdTerm
where ID = " . $ID ." order by EmailFrom";
echo $query . "<br/>";
$query = mysqli_real_escape_string($Conn,$query);
$result = mysqli_query($Conn,$query);
July 15th, 2013, 07:03 PM
You can't escape an entire query string. It'd end up escaping legitimate quotes, too. You escape each individual variable you're dynamically putting into the query, or alternatively use prepared statements.
Comments on this post
July 20th, 2013, 03:25 AM
generally replace into is used in php instead of replace....so change your query and try again.
July 20th, 2013, 06:56 AM
Originally Posted by sara_arunan
Thanks for the reply. The problem is and the reason I used mysql is because I can use the distinct keyword to prevent duplicates because using the replace in the select clause means the value is stripped of the quotes before the distinct is executed. Like I stated my problem has to do with the fact when I echo the query and copy and paste the result into phpmyadmin the query works, but in php I get an error. I think I've found a work around, which I'm going to try today.