Retrieve the last 10 records from mysql database table, problem.

Question

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

Answer 1

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 =.

Answer 2

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 or USING part in a LEFT 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.