...

View Full Version : PHP - Getting Ranked Users from DB



djmike
06-18-2005, 05:27 PM
Hi,

I have slowly been building a site and have got a database full of user details. I have produced a script which ranks thems based on a number of things. Each users has their own rank, and rank 1 is the highest. However, I now need to produce a script that just displays the Top 10 people. However, I don't really know where to go on this. This script will be run automatically on a server and I cannot seem to find code that will work. I had this as an idea, but it didn't work:
"
$rankquery = "SELECT * FROM users WHERE rank == 1";
echo("" . $rankquery["user"] ."");
"

Any Ideas?
Thanks

Taylor_1978
06-18-2005, 05:34 PM
$rank = 1;
while ($rank < 11) {
$result = mysql_query("SELECT * FROM users ORDER BY rank ASC");
$row = mysql_fetch_array($result);
echo "$rank : $row['user']";
$rank++;
}

djmike
06-18-2005, 05:43 PM
thanks for the help. However, that script only returns the numbers 1,2 etc. up to ten. Is there any way to display the person's username instead of the number - called 'user' in the database?

SeeIT Solutions
06-18-2005, 06:08 PM
try this...


<?php
$result = mysql_query("SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
echo "$row['rank'] : $row['user']";
}
?>

djmike
06-18-2005, 06:59 PM
I had to change it to:
echo ("" . $row['rank'] . " : " . $row['user'] . "");
to get it to work - but it does.

Thanks a lot!!!! :thumbsup:

djmike
06-18-2005, 07:04 PM
How would you recommend putting this into a table then?
I got that code thanks and i can make a list, but is there any way to store each name into a different variable, or into a table so each user gets their own cell?

SeeIT Solutions
06-19-2005, 04:57 AM
If you mean display them in a table replace


<?php
$result = mysql_query("SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
echo $row['rank']." : ".$row['user'];
}
?>

with


<?php
echo "<table><tr><td>Rank</td><td>User</td></tr>";
$result = mysql_query("SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>".$row['rank']."</td><td>".$row['user']."</td></tr>";
}
echo "</table>";
?>

Nightfire
06-19-2005, 06:11 AM
As you're only getting the rank and user from the db, I'd suggest/recommend you only select that from the db too, instead of everything.

eg


$result = mysql_query("SELECT rank,user FROM `users` ORDER BY `rank` ASC LIMIT 10");

Nightfire
06-19-2005, 06:12 AM
$rank = 1;
while ($rank < 11) {
$result = mysql_query("SELECT * FROM users ORDER BY rank ASC");
$row = mysql_fetch_array($result);
echo "$rank : $row['user']";
$rank++;
}

That way might be good in theory, but you would be doing 10 queries to the db when it can be done in 1 query seen by seeit solutions :)

Taylor_1978
06-19-2005, 12:27 PM
That way might be good in theory, but you would be doing 10 queries to the db when it can be done in 1 query seen by seeit solutions :)
Yep yep.. I always forget the LIMIT.. I have no idea why.. you'd think after a few years, and considering I work on league/tournament scripts where recovering ranks etc is regular, that'd I'd remember it!! lol

djmike
06-19-2005, 01:23 PM
Thanks for all your help. This is going to sound like such a newbie question, but how is the easiest way to add HTML details to the table. For example, I want to format the table and make the borders white to show up on my black background but I get an error when I use html inside the <table> command.

Alternatively, is there any way to get each user into a variable, e.g. no1 person goes into $rank1, etc?

Taylor_1978
06-19-2005, 01:28 PM
Post what you are actually attempting to do (the code you're using), that way we can fix your error. I have a feeling I know what you may be doing.. but I'll wiat to see.

djmike
06-19-2005, 04:54 PM
Ok, I'm building a section for a website. Users register on the site and enter their details. These can then be changed on a sort of league basis. I need to make a webpage that displays all the Top ten Users in a few differnet catergories. I have this code:


echo "<table><tr><td>Rank</td><td>User</td></tr>";
$result = mysql_query("SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>".$row['rank']."</td><td>".$row['user']."</td></tr>";
}
echo "</table>";

This makes a table with no borders and formatting and although it displays the information, it is not inkeeping with the website. Therefore, I need to be able to change the border colours etc. and format the table, but I cannot include HTML tags in the php code. Therefore, it would be easier to have code that saves the users to a variable so the No.1 person (i.e. rank1) is saved to $rank1, and the number 2, $rank2. Then I can make a table entirely in html and simply include "echo(......)" to print the variables content.

djmike
06-19-2005, 04:55 PM
Ok, I'm building a section for a website. Users register on the site and enter their details. These can then be changed on a sort of league basis. I need to make a webpage that displays all the Top ten Users in a few differnet catergories. I have this code:


echo "<table><tr><td>Rank</td><td>User</td></tr>";
$result = mysql_query("SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>".$row['rank']."</td><td>".$row['user']."</td></tr>";
}
echo "</table>";

This makes a table with no borders and formatting and although it displays the information, it is not inkeeping with the website. Therefore, I need to be able to change the border colours etc. and format the table, but I cannot include HTML tags in the php code. Therefore, it would be easier to have code that saves the users to a variable so the No.1 person (i.e. rank1) is saved to $rank1, and the number 2, $rank2. Then I can make a table entirely in html and simply include "echo(......)" to print the variables content.

At the moment this code simply draws a table.

Hope this is understandable

SeeIT Solutions
06-19-2005, 05:26 PM
you can, just add the html to the echo command.

like so...


<?php
echo "<table cellspacing='0' cellpadding='0' border='1'><tr><td bgcolor='#FF0000'>Rank</td><td bgcolor='#0000FF'>User</td></tr>";
$result = mysql_query("SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10");
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td bgcolor='#00FF00'>".$row['rank']."</td><td bgcolor='#00FFFF'>".$row['user']."</td></tr>";
}
echo "</table>";
?>

Kurashu
06-19-2005, 09:25 PM
Or you could avoid bad code and style it with CSS like it is supposed to be.



<?php
echo '<table id="userranks" ><tr><td class="rank">Rank</td> <td>User</td></tr>';
$result = mysql_query('SELECT * FROM `users` ORDER BY `rank` ASC LIMIT 10');
while ($row = mysql_fetch_assoc($result)) {
echo '<tr><td class="rank">',$row['rank'], '</td> <td>', $row['user'], '</td></tr>';
}
echo '</table>';
?>


That'll speed up just a little tiny bit too.

SeeIT Solutions
06-20-2005, 05:43 AM
yeah, I was just showing him that it can be done just with different quotes around each value.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum