PHP MySQL getting top five highest results. ?

Question

have a poll table which has 10 categories and I want to view the top 5 results from highest to lowest limit into 5 categories. How can I do this in php and mysql?

here are example tables

categories

• id
• category_name

Votes

• id
• category_id
• user_id

Users

• id
• username

here is my sample query:

$q = mysql_query("SELECT * from categories");
while($data = mysql_fetch_array($q){

  $votes = mysql_query("SELECT * from votes where category_id=".$data['id']."");
  $data_vote = mysql_nuw_rows($votes);
  echo $data['category_name']."has".$data_vote."Votes";
}

I want my output to be like this from the highest votes in a categories

category1 has 30 votes
category3 has 25 votes
category5 has 23 votes
category2 has 15 votes
category4 has 10 votes

Answer 1

Use:

$q = mysql_query("SELECT c.category_name,
                         COALESCE(COUNT(v.category_id), 0) AS cnt
                    FROM CATEGORIES c
               LEFT JOIN VOTES v ON v.category_id = c.id 
                GROUP BY c.category_name
                ORDER BY cnt DESC
                   LIMIT 5 ");
while($data = mysql_fetch_array($q) {
  echo $data['category_name'] ." has ". $data['cnt'] ." votes"
}

Answer 2

That query should do it:

select c.category_name, count(v.id) as number_of_votes 
  from CATEGORIES c
left join VOTES v on v.category_id = c.id
    group by c.id
    order by number_of_votes desc
    limit 5

(assuming your VOTES table primary key is "id"