php和mysql查询并显示_PHP和MySQL使用相同的查询显示不同的结果

我有一个

MySQL查询,直接在我的本地

MySQL数据库上执行时工作正常,但在通过

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;

或作为一个班轮：

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;

结果：

| 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结果：

| 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)计数没有像它应该增加的那样

b)因为a)它显示的行数多于应有的数量(应限制为每个ID 3个)

我多次检查它,两个查询完全相同. PHP中没有用户输入或任何差异.

我已经检查了similar questions,但他们没有帮助.以下查询在MySQL和PHP上显示相同的结果：

SHOW VARIABLES LIKE 'character_set%';

SHOW VARIABLES LIKE 'collation%';

是否有人意识到存在这种差异的问题？

编辑更多信息：

$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); }

在执行查询之前,我也尝试过使用和不使用以下内容：

$database->set_charset('utf8');

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