#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    15
    Rep Power
    0

    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" <bob@xxx.com>, but can also have the same value without the quotes i.e. Bob Jones <bob@xxx.com>. 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?

    Tom


    $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);
  2. #2
  3. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    501
    Rep Power
    127
    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

    • ManiacDan agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0

    Smile


    generally replace into is used in php instead of replace....so change your query and try again.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by sara_arunan
    generally replace into is used in php instead of replace....so change your query and try again.
    Sara,
    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.

    Tom

IMN logo majestic logo threadwatch logo seochat tools logo