Mysql query works manually, but returns no results when run from code

Question

I use a very simple query with “Like” to find a 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 a valid product name, I get no results. If I copy the query (echoing from php to the browser) and run it manually in the mysql query browser, I get the result. Any idea where I'm wrong?

Notes:

1. There is no error from the request. Just no results. 2. I use the ezsql library 3. The code works well and performs other SELECTs with a successful database in the same area in the code.

Answer 1

There seems to be a problem with the encoding.

Try this request:

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

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

This will give you the binary stream that MySQL actually receives from your client and uses in comparison.

If you set the connection encoding in the ezSQL properties ezSQL , the string may be distorted (for example, question marks instead of UTF ).

In this case, the comparison will certainly fail.

Update:

Try forcing case insensitive case:

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

In addition, can you send a binary dump of both 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

Are the machines, databases, and user the same between scripts manually and start automatically? Is it possible to access the test server and the other to the production server (for example.)

Answer 3

Maybe this is an encoding error, and not sending the correct information through the code, so it searches for something, therefore, the absence of an error, but not that it is intended to be searched. Scan your code manually, repeat the bit and see if you find anything there that might be the problem.

PHP snippets can help if they look like an encoding error.

Hope this helps.