php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #31175 MySQL query returns no results when run via PHP
Submitted: 2004-12-18 05:53 UTC Modified: 2005-04-19 09:31 UTC
Votes:5
Avg. Score:4.4 ± 1.2
Reproduced:5 of 5 (100.0%)
Same Version:5 (100.0%)
Same OS:2 (40.0%)
From: vortexx at freemail dot hu Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 5.0.4 OS: Windows 2000 Server, SP4
Private report: No CVE-ID: None
 [2004-12-18 05:53 UTC] vortexx at freemail dot hu
Description:
------------
When a query containing CONCAT(...) with a string plus a reference to a certain database field is run via PHP, no results and nop errors are returned. The exact same query run via MySQL commandline returns results as expected.

With 5.0.2 the exact same script (with ALL PHP configs the same, even the same PHP.INI) returns results as expected. Thus I believe this is not MySQL related but rather related to PHP5.0.3 and the php_mysql.dll included with it?!

I also tried the latest PHP snapshot, but the same thing happens. 

Not sure what other info might be relevant and needed, I would be glad to provide whatever info you may find necessary.

Reproduce code:
---------------
<%
/// Omitting the mysql_connect part of the code...!

/// This returns no results under PHP5.0.3,
/// while under PHP5.0.2 it returns results as expected.
$query = "SELECT CONCAT(tel,'blabla') as tel from hotels where id='$id'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
print_r($row);

/// This, however, works!
/// Difference is that two ***fields*** are CONCAT-ed.
$query = "SELECT CONCAT(tel,name) as tel from hotels where id='$id'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
print_r($row);

/// This also works!
/// This time two ***strings*** are CONCAT-ed.
$query = "SELECT CONCAT('just test','blabla') as tel from hotels where id='$id'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
print_r($row);
%>

Expected result:
----------------
I should get the telephone number of the hotel joined with the string "blablabla".

Actual result:
--------------
Get no results from the MySQL query, but no errors either (MySQL or PHP).

Patches

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-01-29 21:10 UTC] tony2001@php.net
Can't reproduce.
Check your code and provide more info if you're still experiencing this problem.
 [2005-02-02 03:58 UTC] vortexx at freemail dot hu
Sorry, but what do you mean "can't reporduce"? 

That you:
- need more info to be able to do so?
If so, please fire away, let me know what else you need, I would be glad to provide any info necessary
- tried to reprodice but given the same circumstances the same code worked fine for you?

Since this is MySQL related obviously I cannot "copy and paste" the MySQL tables themselves here. I can only give info as to the nature of tables involved.

To reporoduce just try ANY MySQL query in PHP 5.0.3+ with a CONCAT of a field from a table plus a string:
SELECT CONCAT('this is a string',field_from_table) as test FROM whatever_table

The point is that whenever a SELECT with a CONCAT of a string and a table field is involved, the query returns nothing. If it's just a CONCAT of 2 or more fields from a table, then it's fine. If it's a CONCAT of 2 or more strings, no problem. As soon as it is a string plus a field, no results are returned. BEFORE 5.0.3 the same queries all worked just fine.

Is this any help? If not, please let me know what info I should send!
 [2005-02-02 06:48 UTC] georg@php.net
If mysql_query doesn't return a resultset for a select 
statement this has 2 reasons: 
a) The table is empty or the where clause didn't match any 
records 
b) an error occured, which should be checked by 
mysql_error() - in your case probably illegal mix of 
collations. 
 
Can't reproduce your error (tested with PHP 4 + 5, MySQL 
4.1.10 and 5.0.3 and different character sets on server 
and client side). 
 [2005-02-02 07:36 UTC] vortexx at freemail dot hu
"a) The table is empty or the where clause didn't match any 
records "
Table was not empty, and there was no WHERE clause. Moreover, the exact same query prodiced the expected results with PHP versions prior to 5.0.3.

"b) an error occured, which should be checked by 
mysql_error() - in your case probably illegal mix of 
collations."
Did test for an error - please see my original bug report: none occurred, at least mysql_error returned none.
Also please note: same query (on the exact same database, etc.) works fine with PHP versions prior to 5.0.3 .

I suspected perhaps this had something to do with the new php_mysql.dll included with the new (5.0.3+) PHP versions?! Since - ceteris paribus - the anomaly was directly linked to changing PHP versions. Once I changed back to the older version, it all worked fine again.

If there is any more info whatsoever that you think might be necessary, please let me know! THANKS!!!
 [2005-02-28 21:14 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5-win32-latest.zip


 [2005-03-08 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2005-04-19 03:14 UTC] vortexx at freemail dot hu
Hello again,

I just downloaded PHP 5.0.4 and MySQL 4.1.11, and the same situation persists as before:

In case of any queries that contain a CONCAT of a string and data from any MySQL table field, no results are returned via PHP, and also mysql_error(...) returns no error.
 [2005-04-19 03:29 UTC] vortexx at freemail dot hu
I apologise for the last message. Using PHP 5.0.4 and MySQL 4.1.11 the problem described in my original post is in fact gone.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Mon Aug 19 11:01:29 2024 UTC