I have come across a MySQL error or action that is bizarre to put it mildly and want
to know how can this be explained. Let me get to the heart of it quickly.
If I run this MySQL query inside MySQL server:
SELECT user_status, user_id FROM pn.users_main WHERE nick_name = ‘john_doe’ AND passwd = ‘mÖÜnam1111Äder##ßßüä’
MySQL returns 1 row found
However, this same MySQL when executed via Php, that is of course command:
where of course $query_login is the mysql_query of the above MySQL command returns ZERO found!
Now, if I change the Characters in above MySQL command from German Characters of:
what are called Umlat Characters in German to regular English Characters then the command
returns 1 when executed via Php too.
How can this be?
Is this a bizarre MySQL Error or Php Error?
This is not an error. Read about MySQL character set support - this is a pretty vast topic but after you have learned it you’ll find that MySQL’s support for various character sets is quite powerful but can be tricky if you are not aware how it works.
Basically, you should be concerned with 3 things in your case:
Character set of your connection to mysql server (=connection of php to mysql)
Character set and collation of your table columns (passwd)
Character set of your php file where your SQL resides
It is possible to connect to the same server with different character sets - that’s why you can experience situations like yours that the same query is executed in different ways. This is because the characters (especially the special characters) are translated differently between you (or php) and the server depending on what character set you choose for the connection.
Character set and collation of your column determines how strings are compared when you search for them so it is possible that under certain collations unlaut letters are treated equally to their non-umlaut counterparts. Same thing goes for upper and lower case.
Character set of your php file is important because this is what gets sent to the server.
In short the three character sets should be the same if you want things to work well - at least 1. and 3. should match. In your case I would suggest using utf8 everywhere to keep it simple and utf8 is the most popular Unicode encoding nowadays and is widely supported. So my suggestion is this:
Set up your connection character set in php with mysql_set_charset(‘utf8’) (btw. read the warning on that page - good idea to follow it )
Set the character set and collation of your passwd column to utf8_bin - for ‘_bin’ collations the strings must match exactly for searched phrases so this way you make sure that the entered password must be exactly like the one stored in the db (the case and umlauts must all be the same, etc.)
Make sure that your php editor is set to utf8 and save your files in that character set.
In this way you should have no surprises in php. But if you connect to mysql with other tools then the connection character set will also have to be set to utf8 if you want to have the same results as in php.
WOW!! We have to do all this for Php & MySQL to handle a MySQL command the same!
I mean if a SELECT inside MySQL says match found, and same MySQL executed via Php says match not found, then this is really an Error in Php.
But thanks a lot for your well written answer anyway.
No, this is not an error in php and in fact if you get different results then the queries are not the same. They may appear the same to you but because MySQL introduced connection character set (in MySQL 4.1) then depending on this setting what you send to mysql server will be received differently - so in your case the server actually executed two different queries - while you think it’s one and the same query.