Getting data from a table based on the results from another?

[hatrickpatrick]

This is the second part of my problems with a top ten list:

I'm making a site for guitar effects. I'm trying to make a list of the top ten effects pedals, based on how many effects settings (patches) people have submitted for each pedal. There are two tables, pedals and patches:

I want it to select patches and find out which "pedal_id" is most common, then select that pedal ID from pedals, echo some tuff, then do the query again, except search for the SECOND most popular value, echo it, then the third, echo it, etc etc etc

 

Anyone know if this is possible?

[cooldude832]

Probably an easier way but this will work
<?php
$id = "The Field you are counting";
$q = "Select ".$id." From `Table` Order by ID Desc";
$r = mysql_query($q) or die(mysql_error());
$temp  = "";
$counts = array();
while($row = mysql_fetch_array($r)){
if($row[$id] != $temp){$counts[$row[$id]]++;}
$temp = $row[$id];
}
print_r($counts);
?>

see what the $counts has for you.

[hatrickpatrick]

Hang on a sec, what exactly does that code do? Doesn't look like it's what I need, since logically my list has to have 2 querys (one to find out the most popular product_id, then the other to search for that product_id in the other table...)