Select query with htm code ' '' in data field

PHP
1 

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

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

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

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:


$Loc = "Queen's";
$Loc = htmlspecialchars($Loc, ENT_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.