我有个奇怪的问题。当我运行这个脚本时,我从数据库中得到10条记录,但它们都是完全相同的。我不知道我做错了什么,也不知道该怎么做。请帮帮我。
我有表AMCMS_highscores,AMCMS_用户,AMCMS_游戏我想看看里面的AMCMS_highscores表,得到最后10个记录,但只有领域的gameid是1997例如。如有任何帮助,我们将不胜感激。

$data = query("SELECT `AMCMS_highscores`.`primkey` , `AMCMS_highscores`.`gameid` , `AMCMS_highscores`.`score` , `AMCMS_users`.`username` , `AMCMS_highscores`.`status` , `AMCMS_highscores`.`userkey` , `AMCMS_games`.`primkey` , `AMCMS_games`.`gamename` FROM `AMCMS_highscores` , `AMCMS_games` , `AMCMS_users` WHERE `AMCMS_highscores`.`gameid` = '$gameid' AND `AMCMS_highscores`.`userkey` != `AMCMS_users`.`userkey` AND `AMCMS_highscores`.`gameid` = `AMCMS_games`.`primkey` AND `AMCMS_highscores`.`status`= 'approved' ORDER by `AMCMS_highscores`.`primkey` DESC LIMIT 0, 10");

Print "<table border cellpadding=3>";
while($info = mysql_fetch_array( $data )) {
    Print "<tr>";
    Print "<th>Score:</th> <td>".$info['score'] . "</td> ";
    Print "<th>ID:</th> <td>".$info['userkey'] . " </td></tr>"; }
Print "</table>";

以下是查询的格式化版本:
SELECT
    `AMCMS_highscores`.`primkey` , `AMCMS_highscores`.`gameid` ,
    `AMCMS_highscores`.`score` , `AMCMS_users`.`username` ,
    `AMCMS_highscores`.`status` , `AMCMS_highscores`.`userkey` ,
    `AMCMS_games`.`primkey` , `AMCMS_games`.`gamename`
FROM `AMCMS_highscores` , `AMCMS_games` , `AMCMS_users`
WHERE `AMCMS_highscores`.`gameid` = '$gameid'
AND `AMCMS_highscores`.`userkey` != `AMCMS_users`.`userkey`
AND `AMCMS_highscores`.`gameid` = `AMCMS_games`.`primkey`
AND `AMCMS_highscores`.`status`= 'approved'
ORDER by `AMCMS_highscores`.`primkey` DESC
LIMIT 0, 10

哎呀,我不是有意抄袭的,但我是偶然抄袭的。
当它带有“=”符号(highscores.userkey=users.userkey)时,我得到一个空查询,因此无法修复它:(
我希望这个问题更容易理解:)
SELECT highscores.primkey, highscores.gameid, highscores.score, users.username,
highscores.status, highscores.userkey, games.primkey, games.gamename

FROM AMCMS_highscores AS highscores, AMCMS_games as games, AMCMS_users as users

WHERE highscores.gameid = '$gameid' AND
      highscores.status = 'approved'

ORDER by highscores.primkey DESC LIMIT 0, 10

结果如下:
http://www.gamesorbiter.com/FB_app/play.php?gameid=1997
(游戏中)
顺便问一句,在发布查询时如何对其进行编码?我点击了“代码”按钮,只对代码的第一行进行了编码。

最佳答案

如果试图从AMCMS_users中选择在AMCMS_highscores中没有匹配行的记录,则需要执行LEFT JOIN,并测试联接表中的空值。以下是JOIN Syntax的摘录:
如果没有匹配的行
ONUSING中的右表
组成一行
设置为空的列用于
右边的桌子。你可以利用这个事实
在表中查找没有
另一个表中的对应项:
因此,您的查询更像这样:

SELECT
    `AMCMS_highscores`.`primkey` , `AMCMS_highscores`.`gameid` ,
    `AMCMS_highscores`.`score` , `AMCMS_users`.`username` ,
    `AMCMS_highscores`.`status` , `AMCMS_highscores`.`userkey` ,
    `AMCMS_games`.`primkey` , `AMCMS_games`.`gamename`
FROM `AMCMS_highscores`
LEFT JOIN `AMCMS_users`
    ON `AMCMS_highscores`.`userkey` = `AMCMS_users`.`userkey`
JOIN `AMCMS_games`
    ON `AMCMS_highscores`.`gameid` = `AMCMS_games`.`primkey`
WHERE `AMCMS_highscores`.`gameid` = '$gameid'
AND `AMCMS_highscores`.`status`= 'approved'
AND `AMCMS_users`.`userkey` = NULL
ORDER by `AMCMS_highscores`.`primkey` DESC
LIMIT 0, 10;

暂时忽略其他条件,查询将查看LEFT JOIN中的每一行,并将其连接到AMCMS_highscores中的每一行,其中AMCMS_highscoresAMCMS_highscores不等于userkeyAMCMS_users。这将产生许多匹配的行。要查看发生了什么,请从查询中删除userkey子句。

10-05 22:00