Bug #18139 Updated: php-mysql returns nothing for a row with any null values in it

ID: 18139 Updated by: sander@php.net Reported By: pchandra@mi-corporation.com -Status: Open +Status: Bogus Bug Type: MySQL related Operating System: Windows XP, Redhat 7.2 PHP Version: 4.2.1 New Comment: Probably user error -> bogus Previous Comments: ------------------------------------------------------------------------ [2002-07-08 12:05:10] pchandra@mi-corporation.com No errors reported with those changes to my code. And oddly enough, the query works as it should now. I had made the following change to my database: update guess set guess='', confidence=-1, rank=0 where guess IS NULL in order to get around the problem last week. However, when I changed these entries back to NULL via: update guess set guess=NULL, confidence=NULL, rank=NULL where guess='' and ran my query again, it worked fine. Strange. I guess you can go ahead and close this one out since I can no longer reproduce the problem. Thanks for all your help. ------------------------------------------------------------------------ [2002-07-07 22:29:15] sniper@php.net Add 'error_reporting(E_ALL);' as first line in your script. Remove that '@' in front of the 'mysql_query()' and add 'echo mysql_error();' line after it. Do you get any errors then? ------------------------------------------------------------------------ [2002-07-03 11:57:39] pchandra@mi-corporation.com It actually does fail with PHP Linux, but I haven't tried against a Linux MySQL database. I realize that simpler queries that return NULL values do in fact work, as I found in the case of this one: "SELECT * FROM guess WHERE id_constraints=1 AND id_engine=1 AND id_glyph_mod=9008" The NULL values are handled properly here, but the query that I described earlier does not work for rows with NULLs in them (other rows are returned just fine). ------------------------------------------------------------------------ [2002-07-03 11:47:49] rasmus@php.net Tested on Linux and unable to reproduce. Perhaps a Windows-only problem. Someone please test on Windows. ------------------------------------------------------------------------ [2002-07-03 11:37:50] pchandra@mi-corporation.com I'm trying to run a rather complex query against a MySQL database housed on another machine (MySQL version: 3.23.47-nt). When I run this query from PHP 4.1.2/Linux, 4.2.0/Windows, or 4.2.1/Windows, like this: $result = @mysql_query($sql, $databaseConnection); $foo = mysql_fetch_assoc($result); print_r($foo); it returns nothing at all. The query is executed successfully and $result is valid, but $foo is completely empty. mysql_fetch_row() and mysql_fetch_array() also return nothing. It should return a single row with NULL in 3 of the columns and real data in the other columns. When I run the exact same query from either the MySQL console, the MySQL GUI, or the Perl DBI interface, that's what I get. Also, if I manually change the row that will be returned so that it contains no NULL values at all, then the query works from PHP as well. This indicates to me that somehow PHP's MySQL interface is not handling NULL values correctly. I am not using any specially compiled version of either PHP or MySQL; just the stock Windows installer versions of both. I don't know if this will be of any help, but here is the query: "SELECT hardware.id_hardware AS HardwareID, session.id_session AS SessionID, mod.id_mod AS ModID, glyph.id_glyph AS GlyphID, glyph.glyph AS Glyph, engine.name AS Engine, guess.guess AS Guess, guess.confidence AS Confidence, guess.rank AS Rank, glyph.seg_error AS SegError, glyph.is_cursive AS Cursive, glyph.european AS European, guess.id_constraints AS Constraints FROM glyph NATURAL LEFT JOIN glyph_mod NATURAL LEFT JOIN guess NATURAL LEFT JOIN engine, mod, hardware, session WHERE (glyph_mod.id_mod = mod.id_mod) AND (glyph.id_session = session.id_session) AND (session.id_hardware = hardware.id_hardware) AND (BINARY glyph.glyph IN ('A')) AND (engine.name IN ('Engine1')) AND (mod.id_mod IN (1,2)) AND (hardware.id_hardware IN (1,2)) AND (glyph.id_glyph IN (9008)) AND (guess.id_constraints IN (1)) AND (glyph.seg_error = 'false') AND (glyph.is_cursive = 'false') AND (glyph.european = 'false') ORDER BY hardware.id_hardware ASC, session.id_session ASC, glyph.id_glyph ASC, mod.id_mod ASC, engine.id_engine ASC, guess.rank ASC" Hope this is enough information, but feel free to e-mail me if you need more. Thanks in advance for looking into this. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=18139&edit=1