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;');

Conservatoire

What does the num_rows() function tell you? – Higginbotham

it's not possible that one liner or formatted sql make so much difference, You should check your code. I feel something is there. only difference in your query is , star DESC but I don't think that will make differenc – Aniela

make sure your PHP code is pointing the same DB, where you are running this Query – Aniela

@Ram Sharma Mistake by creating this question (updated it now). It's 1:1 the same. Both the one liner, the original statement in MySQL and PHP. I also tried comparison using programs. – Conservatoire

@Amal Murali It returns 8 as there are 8 rows when executed via PHP. – Conservatoire

@tobi you should check db name/location. Which is connected through php and where you running query – Aniela

@Ram Sharma Both are connected to my local machine. It's the same server and database in both cases. – Conservatoire

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.

Darell

This sounds like the reason behind the problem. I tried it with your fix, but it's still as before and count isn't increasing. Are you aware of another solution, only returning a maximum of 3 rows from 0_lychee_photos joined with 0_lychee_albums. – Conservatoire

@tobi . . . Can you put an example on SQL Fiddle? – Darell

Answer 2

A simple way to solve this is set variables mysql in your PHP doc. Like this: $var = mysql_query("SET @nun := 0;");

Henig