SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Blackpool, UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select query with htm code ' '' in data field

    Code:
    I have a mysql table with the following in a field 'Queen's'
    
    a simplified version of my code:
    
    $Loc = "Queen's";
    
    $query = 'SELECT PRODUCT_LOCATION FROM PRODUCTS WHERE (PRODUCT_LOCATION LIKE \"%$Loc%\")';
    
    $result = mysql_query($query);
    
    The problem is that the html code within the $Loc is getting 'converted' to the display character so the query doesn't match anything.
    
    I can't really change the html codes within the database as others use it and I don't know what implications changing the data might cause.
    
    Any ideas how I can get the query to work correctly?
    
    Thanks for any help.

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ched999uk View Post
    The problem is that the html code within the $Loc is getting 'converted' to the display character so the query doesn't match anything.
    Where is $Loc converted to the display character? And what do you mean by the 'display character'? Looking at your code you are not converting $Loc, you pass it straight to the query. How is ' stored in the database? Is it plain ' or ' ? Where does $Loc come from?

    You need to clarify your case.

  3. #3
    SitePoint Member
    Join Date
    Apr 2008
    Location
    Blackpool, UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Now my question has been moved to where it should be (not a mysql issue but a php issue) thanks.

    The problem I have is that in the database the apostrophe " ' " is stored as the html hash code. So when I set a php variable to be the query I put in the hash code but somehow the actual query that mysql is receiving doesn't have the hash code but the character that the browser would have displayed.

    The word 'Queen's' is stored in the database as the html hash code i.e. 'Queen & # 39 ; s' without spaces.
    $Loc is effectively hard coded.

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    So I think in this case you need to simply encode the string you are looking for before using it in the sql query. The encoding algorithm should be the same as the one used for putting the data into the database. You will have to make sure what characters are encoded in the db so that you can use the right algorithm. Let's assume it's htmlspecialchars() with ENT_QUOTES because single quotes are encoded (if not then you can use strtr() with arrays to replace your custom list of characters with html codes). So your code would be:
    PHP Code:
    $Loc "Queen's";
    $Loc htmlspecialchars($LocENT_QUOTES);

    // don't forget about mysql_real_escape_string!
    $query "SELECT PRODUCT_LOCATION FROM PRODUCTS WHERE (PRODUCT_LOCATION LIKE '%" .mysql_real_escape_string($Loc). "%')";

    $result mysql_query($query); 
    Sometimes the problem can be more complicated on web sites using a limited character set (for example ISO-8859-1), if people submit in a form characters from outside the character set range then the browser encodes them into #xx; codes so then it's not only ' and a few other characters but many more. Then that would be a bit more complicated situation if $Loc were a utf8 string with foreign characters. But I don't know what your case is no I will not go into that at the moment.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •