PHP and MySQL showing different results with same query

Question

I have a MySQL query which works fine when executed directly on 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 a one-liner:

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;

The 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  |

The 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 isn't increasing like it should
b) because of a) it shows more rows than it should (should be limited to 3 per id)

I checked it multiple times, both queries are exactly the same. There's no user input or any difference in PHP.

I already checked similar questions, but non of them helped. The following queries are showing the same result on both MySQL and PHP:

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

Is anyone aware of an issue casing this difference?

Edit with further 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

Yup. The order of evaluation of expressions in a select clause is not guaranteed. So, the variable assignments can happen in different orders, depending on how the query is invoked.

You can fix this by putting all the variable assignments into a single 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

The specific explanation in the documentation is:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

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

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

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