Reputation: 43
I have a weird problem. When I run this script I get 10 records from the database but they are all exactly the same. I have no idea what I am doing wrong or how to fix it. Please help me.
I have tables AMCMS_highscores , AMCMS_users , AMCMS_games I want to look inside the AMCMS_highscores table, get the last 10 records but only where the field gameid is 1997 for example. Any help is appreciated.
$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>";
Here's a formatted version of the 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
OOPS, i didnt mean to copy that but i did by accident. When its with "=" sign (highscores.userkey = users.userkey) i get empty query so that didnt fix it :(
I hope this query would be easier to understand :)
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
Here is the result:
http://www.gamesorbiter.com/FB_app/play.php?gameid=1997 (under the game)
Btw, how do you code your query when you post it ? I clicked the code button and only the first line of the code got coded.
Upvotes: 1
Views: 4110
Reputation: 21659
If you are trying to select records from AMCMS_users
for which there are no matching row in AMCMS_highscores
, then you need to do a LEFT JOIN
, and test for NULL values in the joined table. Here's an extract from JOIN Syntax:
If there is no matching row for the right table in the
ON
orUSING
part in aLEFT JOIN
, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:
Your query would therefore look more like this:
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;
Ignoring the other conditions for a moment, your query is looking at every row in AMCMS_highscores
, and joining it to every row in AMCMS_highscores
where AMCMS_highscores
.userkey
is not equal to AMCMS_users
.userkey
. This will produce a lot of matching rows. To see what is happening, remove the LIMIT
clause from your query.
Upvotes: 2
Reputation: 2397
Same thing with aliases and a better format:
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.userkey != users.userkey AND
highscores.gameid = games.primkey AND
highscores.status = 'approved'
ORDER by highscores.primkey DESC LIMIT 0, 10
You didn't say what the table scheme was, but my guess is that the problem is in the WHERE-part, more specifically highscores.userkey != users.userkey
. It accepts only users who don't have same userkey as in the highscores table. Try changing the !=
to =
.
Upvotes: 1