PHP and MySQL show different results with the same query

Question

I have a MySQL query that works fine when executed directly in my local MySQL database, but shows a different result when executed via PHP.

SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count FROM 0_lychee_albums AS a LEFT JOIN (SELECT id, album, thumbURL, @num := IF(@group = album, @num + 1, 0) AS count, @group := album AS dummy from 0_lychee_photos WHERE album != 0 ORDER BY album, star DESC) AS t ON a.id = t.album WHERE count <= 2 OR count IS NULL; 

or as single line:

 SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count FROM 0_lychee_albums AS a LEFT JOIN (SELECT id, album, thumbURL, @num := IF(@group = album, @num + 1, 0) AS count, @group := album AS dummy FROM 0_lychee_photos WHERE album != 0 ORDER BY album, star DESC) AS t ON a.id = t.album WHERE count <= 2 OR count IS NULL; 

Result:

 | id | title | public | sysstamp | password | thumbURL | count | | 71 | [Import] 01 | 0 | 1415091268 | NULL | cad008943372d984a9b74378874128f8.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415091268 | NULL | 7b832b56f182ad3403521589e2815f67.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415091268 | NULL | f058f379ce519f1d8a2ff8c0f5003631.jpeg | 1 | | 72 | [Import] 9n401238 | 0 | 1415091268 | NULL | a4d59377bed059e3f60cccf01a69c299.jpeg | 2 | | 73 | Untitled | 0 | 1415114200 | NULL | NULL | NULL | 

PHP result:

 | id | title | public | sysstamp | password | thumbURL | count | | 71 | [Import] 01 | 0 | 1415091268 | NULL | cad008943372d984a9b74378874128f8.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415091268 | NULL | 7b832b56f182ad3403521589e2815f67.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415091268 | NULL | f058f379ce519f1d8a2ff8c0f5003631.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415091268 | NULL | a4d59377bed059e3f60cccf01a69c299.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415092318 | NULL | 7b832b56f182ad3403521589e2815f67.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415092369 | NULL | cad008943372d984a9b74378874128f8.jpeg | 0 | | 72 | [Import] 9n401238 | 0 | 1415092369 | NULL | 84030a64a1f546e223e6a46cbf12910f.jpeg | 0 | | 73 | Untitled | 0 | 1415114200 | NULL | NULL | NULL | 

a) count does not increase, as it should be b) due to a) it shows more lines than it should (should be limited to 3 by id)

I checked it several times, both requests exactly match. There is no user input or any differences in PHP.

I already checked similar questions , but did not help them. The following queries show the same result for both MySQL and PHP:

 SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%'; 

Does anyone know of a problem that limits this difference?

Change additional information:

 $database = new mysqli($host, $user, $password, $database); $query = "SELECT a.id, a.title, a.public, a.sysstamp, a.password, t.thumbURL, t.count FROM 0_lychee_albums AS a LEFT JOIN (SELECT id, album, thumbURL, @num := IF(@group = album, @num + 1, 0) AS count, @group := album AS dummy FROM 0_lychee_photos WHERE album != 0 ORDER BY album, star DESC) AS t ON a.id = t.album WHERE count <= 2 OR count IS NULL"; $albums = $database->query($query); while ($album = $albums->fetch_assoc()) { print_r($album); } 

I also tried it with and without the following before executing the query:

 $database->set_charset('utf8'); $database->query('SET NAMES utf8;'); 

Answer 1

Yeah. The order in which expressions are evaluated in the select clause is not guaranteed. Thus, variable assignments can be performed in different orders, depending on how the request is called.

You can fix this by putting all the variable assignments in one expression. Try using this subquery for t :

  (SELECT id, album, thumbURL, (@num := IF(@group = album, @num + 1, if(@group := album, 0, 0) ) ) as count FROM 0_lychee_photos CROSS JOIN (SELECT @num := 0, @group := NULL) vars WHERE album <> 0 ORDER BY album, star DESC ) t 

Specific explanation in the documentation :

As a rule, apart from SET statements, you should never assign a value to a user variable and read the value within the same expression. For example, to increase a variable, this is normal:

 SET @a = @a + 1; 

For other operators, such as SELECT, you can get the results that you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do the second lesson:

 SELECT @a, @a:=@a+1, ...; 

However, the evaluation order of expressions involving a user is undefined.

Answer 2

An easy way to solve this is to set the mysql variables in your PHP document. Like this: $ var = mysql_query ("SET @nun: = 0;");