Jump to content


Photo

help with a small script


  • Please log in to reply
3 replies to this topic

#1 Jleagle

Jleagle
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 01 May 2006 - 11:36 PM

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

<?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>';
}
?>

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 May 2006 - 06:55 PM

I'm quite confused -- post your table structure. I have no idea how scores, games and users are related.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Jleagle

Jleagle
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 02 May 2006 - 11:08 PM

--
-- 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');


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 May 2006 - 05:32 AM

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

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

Of course, this requires MySQL 4.1+.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users