我有个奇怪的问题。当我运行这个脚本时,我从数据库中得到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的摘录:
如果没有匹配的行ON
或USING
中的右表
组成一行
设置为空的列用于
右边的桌子。你可以利用这个事实
在表中查找没有
另一个表中的对应项:
因此,您的查询更像这样:
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_highscores
AMCMS_highscores
不等于userkey
AMCMS_users
。这将产生许多匹配的行。要查看发生了什么,请从查询中删除userkey
子句。