Sorting Database Results

[tcollie]

Okay, I'm working on a small project for my daughter's class at school. They have a fundraiser running right now and they want to post their fund raiser data on the their school website. What they want is this, they want a page listing each student and the amount that they've managed to raise so far along with a ranking.

Something like this:

Teresa H. $85 #1
Jana L. $84 #2

etc...etc...

This i've already accomplished. But they also want the ability to list the students alphabetically, which I can do also, but they each name to be clickable with a link to a page about that student containing information about their particular fundraising efforts. I guess a blog of sorts. And I have this written as well. The only thing I can't figure out is how to add each individual ranking to the individual pages.

To get the ranking above, I just select all the data from the db and use mysql_numrows($result); with a loop to get the "ranking" but I can't seem to figure this out when I have to use a select statement that requires that the userid be a match. Example:

[code]
$query = mysql_query("SELECT * FROM students where uid =$uid")
  or die(mysql_error()); 
[/code]
Now how do I re-write my select statement to get my overall ranking for each student on their individual page?

Here's the basic db structure also:

uid, student_name, total_raised, homeroom

"total_raised" is the field that I'm using to get my Ranking.

Any help would be great b/c I'm stumped on this.

[ultrus]

Hello tcollie,
An easy way to get this done might be to include the rank in the individual's page link as a get variable. There might be a bit of an issue when jumping from individual page to individual page, but might do the trick if clicking from the page with everyone's rankings. Like this:

[code]<a href="individual.php?uid=5&rank=1">Teresa H.</a><br />
<a href="individual.php?uid=22&rank=2">Jana L.</a><br />[/code]
on the next page...
[code]$rank=$_GET['rank'];[/code]

Another alternative would be a table with rankings that updates itself every time you add a dollar amount. I'm still somewhat newbish, so take my advise with a grain of salt. Hope this helps spark something.  :)

[mattd8752]

If you want to jump from users to users page, would be to make a simplified output page and parse it into a readable by php textfile and have php scan through the file and update the list 1ce an hour.

[Barand]

You could quickly get the rank with
[code]
<?php
function getRank ($student) {
    $sql = "SELECT COUNT(*) as rank FROM tablename
        WHERE total_raised > (SELECT total_raised FROM tablename WHERE uid = '$student')";
    $res = mysql_query($sql);
    return mysql_result($res, 0) + 1;
}
?>
[/code]

[tcollie]

That did it Barand.  Thanks for everyone's input on this one.