Sorting Database Results

Expand Post »

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:

PHP Syntax (Toggle Plain Text)
$query = mysql_query("SELECT * FROM students where uid =$uid") 
  or die(mysql_error());
$query = mysql_query("SELECT * FROM students where uid =$uid") 
  or die(mysql_error());

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.

Similar Threads

How to call a PHP function from Javascript and return the results back into Javascrip in PHP
PHP results in Blank Screen in PHP
Creating a Basic String Database in C++
Tutorial: Search a Database in ASP

c0rkscrew

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jan 2007

Permalink

Jan 14th, 2007

Re: Sorting Database Results

You cannot determine the position of a record by selecting it alone.

The best idea, I think, is to save the ranking to the database as well. For example, upon the payment you can recalculate them and save to he database.

php_daemon

Reputation Points: 13

Solved Threads: 2

Junior Poster

Offline

138 posts
since Aug 2006

Permalink

Jan 14th, 2007

Re: Sorting Database Results

How about something like:

PHP Syntax (Toggle Plain Text)
select count(*)
 from students
where total_raised > (select total_raised from students where uid=$uid)
select count(*)
 from students
where total_raised > (select total_raised from students where uid=$uid)

This should tell you how many students have total_raised greater than the student you're looking at on your individual page... which should be equal to the ranking.

It should also handles ties just fine.

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: PHP and scheduled tasks

Next Thread in PHP Forum Timeline: Feature Posts on Index ?

DaniWeb Message

Cancel Changes

User Name
Password