help with a small script

[Jleagle]

i have a games site and im trying to make a ranking system in order of how many first places people have. So far i have this..

[code]
<?php
//getting user IDs
$query1 = mysql_query("SELECT * FROM users ORDER BY username ASC");
while($row = mysql_fetch_array($query1)){
    $user_id = $row['id'];
    $firsts = 0;

    echo GetName($user_id).' - ';

    //getting game IDs
    $query2 = mysql_query("SELECT * FROM games ORDER BY id ASC");
    while($row = mysql_fetch_array($query2)){
        $game_id = $row['id'];

        //get reverse games
        if ($row['reverse'] == 'on'){
            $reverse='ASC';}else{$reverse='DESC';
        }
        
        $query4 = mysql_query("SELECT user_id FROM scores WHERE user_id = $user_id ORDER BY score $reverse");
        //check user has any scores
        if (mysql_num_rows($query4) > 0){
            //get first place user from the game
            $top_user = mysql_result($query4, 0);
        }
        //add up users score
        if ($top_user == $user_id){ ++$firsts; }
    }
    echo $firsts;
    echo '<BR><BR>';
}
?>
[/code]

what i want is a username and amount of first places on each row, of the loop.

what the current code does is loop the usernames correctly but beside the username it has the total amount of games, not just the ones where they are first. All players who have a score at all get this figure.

can anyone tell me why this is happening, thanks! :D

[fenway]

I'm quite confused -- post your table structure. I have no idea how scores, games and users are related.

[Jleagle]

--
-- Table structure for table `games`
--

CREATE TABLE `games` (
`id` mediumint(9) NOT NULL auto_increment,
`folder` varchar(25) default NULL,
`reverse` varchar(6) NOT NULL default '0',
`played` int(10) NOT NULL default '0',
`stamp` int(20) NOT NULL default '0',
`name` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=536 ;

--
-- Dumping data for table `games`
--

INSERT INTO `games` VALUES (532, 'cat_bat', '', 57, 1145956796, 'Cat Bat');
INSERT INTO `games` VALUES (531, 'cat_vac', '', 9, 1145954801, 'Cat Vcume');
INSERT INTO `games` VALUES (533, 'circles', '', 18, 1146183312, 'Circles');
INSERT INTO `games` VALUES (534, 'skidwrx', 'on', 22, 1146183367, 'Skid WRX');

-- --------------------------------------------------------

--
-- Table structure for table `scores`
--

CREATE TABLE `scores` (
`id` int(11) NOT NULL auto_increment,
`game_id` int(11) NOT NULL default '0',
`score` int(11) NOT NULL default '0',
`user_id` int(11) NOT NULL default '0',
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=138 ;

--
-- Dumping data for table `scores`
--

INSERT INTO `scores` VALUES (137, 534, 56, 29);
INSERT INTO `scores` VALUES (136, 533, 125, 29);
INSERT INTO `scores` VALUES (135, 534, 57, 27);
INSERT INTO `scores` VALUES (134, 534, 43, 1);
INSERT INTO `scores` VALUES (133, 533, 70, 1);
INSERT INTO `scores` VALUES (132, 532, 2052, 25);
INSERT INTO `scores` VALUES (131, 532, 11025, 1);
INSERT INTO `scores` VALUES (130, 531, 1931, 1);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL default '',
`password` varchar(20) NOT NULL default '',
`email` varchar(30) NOT NULL default '',
`join_stamp` varchar(30) NOT NULL default '',
`realname` varchar(30) NOT NULL default '',
`last_page` varchar(100) NOT NULL default '',
`last_stamp` varchar(20) NOT NULL default '',
`games_on_page` int(11) NOT NULL default '30',
`games_page_sort` varchar(10) NOT NULL default 'played',
`games_page_order` varchar(10) NOT NULL default 'ASC',
`pm_popup` varchar(5) NOT NULL default 'yes',
`pm_popped` varchar(5) NOT NULL default 'no',
`pm_popped_reply` varchar(5) NOT NULL default 'no',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=32 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1, 'Jim', '*****', 'jimeagle@gmail.com', '1121040876', 'James Eagle', 'Playing Skid WRX', '1146601012', 20, 'played', 'ASC', 'yes', 'yes', 'yes');

[fenway]

The best way is to handle this with a single statement and a row subquery:

[code]SELECT u.username, COUNT(*) AS firstPlaceCount
FROM scores AS s
LEFT JOIN users AS u ON u.id = s.user_id
WHERE ( s.game_id, s.score ) IN ( SELECT game_id, MAX(score) FROM scores GROUP BY game_id )
GROUP BY s.user_id[/code]

Of course, this requires MySQL 4.1+.