[SOLVED] SQL help

[play_]

I have a table that looks similar to this:

 

|userID | fname | lname | school |

| 1      | John  | Doe  |  UMD  |

--------|-------|------|--------|

| 2      | Janet  | Smith| UCLA  |

--------|-------|------|--------|

 

Just exmaple entries. It's going to get alot larger than that.

 

User selects school from a drop-down menu.

What i'd like to do is, retrieve all schools and list by popularity. How can I do that?

[PHP_PhREEEk]

Tell us how you intend on 'managing' popularity, and we'll be able to tell you how to pull lists based on it.

 

PhREEEk

[play_]

That's what I'm asking.

 

I want to display something like:

 

UMD: 500

UCLA: 470

VT:  389

 

etc.

 

Was thinking there could be an SQL command that would compliment SORT BY

[GingerRobot]

Where does that data (e.g. the number) come from?

[play_]

@ Gingerroot

Where does that data (e.g. the number) come from?

 

The number of times it is in the database.

 

So say I have 10 users who signed up.

 

2 of them go to UMD.

3 of them go toVT

4 of them go to UCLA

1 goes to WVU

 

I'd then like to list the results from highest, to lowest. Like this:

UCLA: 4

VA  : 3

UMD : 2

WVU : 1

[GingerRobot]

Ah, im with you. Try:

 

<?php
$sql = "SELECT COUNT(*) as `number`,`school` FROM `yourtable` GROUP BY `school` ORDER BY `number` DESC";
$result = mysql_query($sql) or die(mysql_error());
while(list($number,$school) = mysql_fetch_row($result)){
echo $school.' : '.$number.'<br />';
}
?>

 

Edit: Ideally you should be storing a school ID in this table, and have a separate table of school names.

[play_]

Thank you