1

EDIT: After feedback from my original post, I've change the text to clarify my problem.

I have the following query (pseudo code):

$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_query("SET NAMES 'utf8'; COLLATE='utf8_danish_ci';");

mysql_query("SELECT id FROM myTable WHERE name = 'Fióre`s måløye'", $conn);

This returns 0 rows.

In my logfile, I see this:

255 Connect     root@localhost on 
255 Query       SET NAMES 'utf8'; COLLATE='utf8_danish_ci'
255 Init DB     norwegianfashion
255 Query       SELECT id FROM myTable WHERE name = 'Fióre`s måløye'
255 Quit
  • If I run the query directly in phpMyAdmin, I get the result.
  • Table encoding: UTF-8
  • HTML page encoding: UTF-8
  • I can add records (from form input) where names uses accents (e.g. "Fióre`s Häßelberg")
  • I can read records with accents when using -> "name LIKE '$labelName%'"
  • The information in the DB looks fine

I have no clue why I can't select any rows which name has accent characters.

I really hope someone can help me.

UPDATE 1: I've come to a compromise. I'll be converting accents with htmlentities when storing data, and html_entity_decode when retrieving data from the DB. That seems to work.

The only drawback I see so far, is that I can't read the names in cleartext using phpMySQL.

Improve this question
3
  • 2
    Can you post the call to the function the way you have it in your script? And can you print $sql and see what it looks like? Commented Jun 13, 2009 at 9:35
  • Done. If I take the outputed sql and use it directly in PHPMyADmin, it works fine. I get the result I want. Commented Jun 13, 2009 at 10:15
  • What encoding do you use in your PHP file where you declare that query above? You need to use UTF-8 as well to have no conflicts. Commented Jun 15, 2009 at 14:52

6 Answers 6

Reset to default
4

I think you should rather return $result than $this->query.

Additionally you should be aware of SQL injection and consider using mysql_real_escape_string or Prepared Statements to protect you against such attacks. addslashes is not a proper protection.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

My DAL (Data Access Layer) is a class. See last post here: stackoverflow.com/questions/975452/… I must use $this->query to return the value. I'm not able to extend my DAL lass to use either mysqli or PDO (see link) - but I've tested for SQL injection, and so far it can't be done - but I will take proper actions against it.
Don’t you mean $this->query_result? But why do you need that anyway? $result has the same value as $this->query_result: the return value of $this->query().
If you are refferring to my other post, I've renamed $query_result to $query. And for the above code, the $result was just for testing ( mysql_num_rows($result) ). I've updated above example and removed $result.
Thanks Gumbo. You just made me realizea huge istake. Now I suddenlt have a variable and function which is called the same. Not god. I'm fixing this. Still, mys main problem is still the same.
2
+100

As other answers indicate, this very much seems like an encoding problem. I suggest turning on query logging ( http://dev.mysql.com/doc/refman/5.1/en/query-log.html ) as it can show you what the database really receives.

UPDATE: I finally found a page explaining the dirty details of PHP and UTF-8 (http://www.phpwact.org/php/i18n/charsets). Also, make sure you read this (http://niwo.mnsys.org/saved/~flavell/charset/form-i18n.html) to understand how you to get proper data returned from form posts.

Improve this answer

5 Comments

Curently the log only records connect /disconnect. I'm running MySQL on my Win XP. You know how to get more detailed logging info?
Just reading the manual. According to dotnot.org/blog/archives/2005/01/11/… there's no problem in logging statements. However, I have no MySQL installations (and even less XPs) so I cannot confirm this.
Brilliant Martin. I got the log working and can now see the cause of the problem. The input name = 'Church´s'. The  is a unicode issue I believe. But I don't know what else I can do to have it all in UTF8 - I thought I did have everything in utf8.
Thanks for the update Martin. Nothing happends to the input if I decode it. But if I encode it, I get some weird output. Anyways - I'm using htmlentities for now. At least I got it working and will look at this issue later down the road.
That's probably the wiser choice. Good luck and please let us know how it goes!
2

Try this query. If you get results, then it's an issue with your backtick character in the query

SELECT * FROM sl_label WHERE name Like 'Church%'
Improve this answer

6 Comments

That worked! One row was returned and I See tha retrieved name looks like this: Church\u00b4s. So how should I go around fixing the backtick character? Neither addslashes or mysql_real_escape_string escapes the character.
Use the same character encoding on the client side as the table is created.
It's using the same chr. encoding. Table is UTF8 and I can even try utf8_encode() on the input without any luck.
Thanks. But I'm not able to extend my DAL class to either PDO or mysqli. Doing that results in a blank page.
Sorry it looked like you were using PDO.
|
0

Maybe try checking for error messages after calling the query (if you aren't already doing this outside that function). It could be telling you exactly what's wrong.

As Artem commented, printing out the actual query is a good idea - sometimes things aren't exactly as you expect them to be.

Improve this answer

Comments

0

This might be an encoding issue, the ' in Church's might be a fancy character. PHPMyAdmin could be UTF-8, and your own PHP website could be iso-latin1.

Improve this answer

10 Comments

Looks like you are right. But I'm not sure how this can be solved. See my post update.
After opening the connection, execute mysql_set_charset('utf8'). Alternatively, run the SQL queries "SET CHARACTER SET 'utf8'" and "SET NAMES 'utf8'".
Yeah, I tried that. But that results in ALL my characters such as æ, ø and å turns into gibberish (when I do normal SELECTs from DB).
Assuming the gibberish is in the browser, try to inform the browser by adding "<?php header("Content-type: text/html; charset=utf-8"); ?>" to the top of your php file?
The header already has that. The problem isn't the unicode on my DB, That is fine. The problem is that mysql_query() adds the  before the ` - thus creating the name 'Church´s'. So the selects searches for 'Church´s', which of cours does not exist in DB.
|
0

I'm looking at this line

mysql_query("SET NAMES 'utf8'; COLLATE='utf8_danish_ci';");

and I think it might be an error. With the ';' you are sending two queries to the server, but COLLATE is a clause, not a legal statement on its own. Try:

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_danish_ci'");

If the COLLATE clause is not being accepted by the server, you might be having the problem of your label column having a danish_ci collation, but the statements coming in have the default (prob utf_general_ci). There would be no match for the accented characters, but the wildcard works because the representation for the basic ascii characters are the same.

Improve this answer

2 Comments

Hi Steven. ';' isn't the problem. I think I have found a solution. Not what I want, but it works. I'lll be using htmlentities for storing values and html_entity_decode when retrieving values.
*I meant Steve, not Steven :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.