Special characters in PHP MySQL statement

Question

I have some special characters stored in a MySQL database. Specifically Â®. I want to SELECT all entries with these characters. I am currently using this statement:

mysql_query("SELECT * FROM table WHERE description LIKE '%Â®%' OR name LIKE '%Â®%'");

It returns 0 entries. When i try something with %a% it returns a lot so I know everything is right, it is just some kind of a charset problem or something.

When I echo "Â®" it returns "å¨".

Also when I do the exact same query in phpmyadmin it works properly. Please help!

You should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article. — Matt
– Matt, Commented Aug 3, 2012 at 16:39
I think the db is fine, and you actually have an encoding problem on the PHP end. — bfavaretto
– bfavaretto, Commented Aug 3, 2012 at 16:40
Â® is a 'replacement character', and as such not recognizable by your database, which most likely has the å character stored. Try setting the connection encoding to the database encoding (maybe UTF-8) and then try again. Also, try searching for that 'proper character', instead of the 'replacement' one. Because THAT character seems like Multibyte gone Ascii to me. — ATaylor
– ATaylor, Commented Aug 3, 2012 at 16:42
No, the actual entries in the database have those exact characters. I looked in phpmyadmin. It's strange how it is not selecting them. Is there anyway to tell PHP not to convert the characters and leave them as is? — TJE
– TJE, Commented Aug 3, 2012 at 17:12
I checked the database again, and all the entries that show up with Â® are actually just a bunch of random numbers and letters. How can i search for this with PHP? — TJE
– TJE, Commented Aug 3, 2012 at 17:27

Abid Hussain · Accepted Answer · 2012-08-03 17:37:43Z

Read this its very help full to you

Just simply add those symbols to your text, and execute it as SQL query:

INSERT INTO tbl_name VALUES ("Here's my text: ©®");

When you want to display it one the website don't do anything with these symbols (but remember to escape at least <, >, & (using htmlspecialchars()) cause those has special meaning in XML/SGML (HTML) documents)

PS. Also remember to escape text passed to SQL query using mysql_real_escape_string() to avoid any SQL Injection problems. If your server has magic_quotes_gpc enabled disable it or at least filter your GET/POST/COOKIE data to its raw value. You should always consciously escape values. EDIT:

According to your comment... I don't remember whether magic_quotes_gpc are enabled by default but you can easily undone magic quotes effect. Just on the very beginning of your PHP code add something like this:

if (get_magic_quotes_gpc()) {
  array_walk_recursive($_GET, 'stripslashes');
  array_walk_recursive($_POST, 'stripslashes');
  array_walk_recursive($_COOKIE, 'stripslashes');
}

Now each GPC value should be always raw - without quotes - so you have to escape it manually before passing any variable into query.

Collectives™ on Stack Overflow

Special characters in PHP MySQL statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related