mysql query works manually but returns no results when running from code

Question

I use a very simple query with "Like" to find product by its name

SELECT p_pid, p_name, p_cat
FROM products
WHERE p_sid=346 AND p_name LIKE 'product name here in utf-8 encoding, can be various languages'
LIMIT 1

When I run this query from code with valid product name, I get no results. If I copy the query (echoed by php to the browser) and run it manually in mysql query browser I do get a result. Any idea where I'm wrong?

Notes:

1.There is no error coming from the query. Just no results. 2. I'm using ezsql library 3. The code runs well and perform other SELECTs with the database successfully in the same scope in the code.

Answer 1

Seems to be the issue with encoding.

Try running this query:

SELECT HEX(CAST('product name here in utf-8 encoding, can be various languages' AS BINARY))

both in ezSQL and in the query browser and compare the results.

This will give you binary stream that MySQL really gets from your client and uses in comparison.

If you set wrong connection encoding in ezSQL properties, the string may get mangled (like, question marks instead of UTF symbols).

In this case comparison will of course fail.

Update:

Try to force case insensitive collation:

SELECT  *
FROM    table
WHERE   field LIKE 'product name here in utf-8 encoding, can be various languages' COLLATE UTF8_GENERAL_CI

Also, could you please post binary dump both of your string and the data contained in the field?

SELECT  p_pid, p_name, p_cat,
        HEX(CAST(p_name AS BINARY)),
        HEX(CAST('product name here in utf-8 encoding, can be various languages' AS BINARY))
FROM    products
WHERE   p_pid = @pid_of_product_that_should_match_but_it_doesnt

Answer 2

Could it be a coding error, not sending in the right information via the code, so it is searching for something, hence the lack of an error, but not what it is meant to be searching for. Scan through your code manually, re-do bits and see if you spot anything there that may be an issue.

Snippets of the PHP may help if it does appear to be a coding error.

Hope this Helps.

Answer 3

Are the machine, database and user the same between the manually and automatically run scripts? Could one be accessing the test server while the other accesses the production server (for example.)