I've been trying to do this for like more than two weeks but I couldn't succeed. It's a page quite similar to a forum memberlist page. I'll first show the codes.
CREATE TABLE `articles` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`votes` int(10) unsigned NOT NULL default '0',
`user` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `articles` VALUES (1, 'Article 1', '2005-06-23', '50', 1);
INSERT INTO `articles` VALUES (2, 'Article 2', '2005-06-23', '0', 3);
INSERT INTO `articles` VALUES (3, 'Article 3', '2005-06-23', '1', 3);
INSERT INTO `articles` VALUES (4, 'Article 4', '2005-06-23', '35', 2);
INSERT INTO `articles` VALUES (5, 'Article 5', '2005-06-23', '5', 1);
INSERT INTO `articles` VALUES (6, 'Article 6', '2005-06-23', '100', 3);
INSERT INTO `articles` VALUES (7, 'Article 7', '2005-06-23', '0', 2);
INSERT INTO `articles` VALUES (8, 'Article 8', '2005-06-23', '5000', 1);
INSERT INTO `articles` VALUES (9, 'Article 9', '2005-06-23', '0', 2);
INSERT INTO `articles` VALUES (10, 'Article 10', '2005-06-23', '2', 1);
CREATE TABLE `users` (
`userid` int(10) unsigned NOT NULL auto_increment,
`username` varchar(12) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
PRIMARY KEY (`userid`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;
INSERT INTO `users` VALUES (1, 'Mark', 'mark');
INSERT INTO `users` VALUES (2, 'Johnny', 'johnny');
INSERT INTO `users` VALUES (3, 'Anderson', 'anderson');
<?php
$res = "SELECT users.username, articles.user, articles.votes FROM users LEFT JOIN articles ON userid = user ORDER BY votes DESC LIMIT 10";
$query = mysql_query($res);
while($row = mysql_fetch_array($query) ) {
$username = $row["username"];
$points = $row["votes"];
echo "<tr><td>$username</td><td align=right>$points</td></tr>";
}
?>
The Page Now Shows Top 10 Articles and their poster's name and order them by the no. of votes. Looks like this:
USERNAME POINTS
Mark 5000
Anderson 100
Mark 50
Johnny 35
Mark 5
Mark 2
Anderson 1
Anderson 0
Johnny 0
Johnny 0
Now, I want the page display top 5 usernames and their points. So in this case it should show something like this:
USERNAME POINTS
Mark 5057
Anderson 101
Johnny 35
But I don't know yet how it can be done. Can anyone help me with this?
