simplify multiple query?

[Oziam]

Hello,

 

I have a table with x amount of rows and each row holds data for a pic, it has various coulmns including

a category coulmn, what I want to do is get the number of rows(listings) for each category,

there are only seven categories(at the moment) and I have code that queries each row by cat name

to get the result, I am doing this for each category name but there must be a more efficient solution!

 

my current code is below;

 

$q1 = "SELECT id FROM pics WHERE cat='category1'"; 
$r1 = mysql_query($q1) or die(mysql_error());
$cnt1 = mysql_num_rows($r1);

$q2 = "SELECT id FROM pics WHERE cat='category2'"; 
$r2 = mysql_query($q2) or die(mysql_error());
$cnt2 = mysql_num_rows($r2);

$q3 = "SELECT id FROM pics WHERE cat='category3'"; 
$r3 = mysql_query($q3) or die(mysql_error());
$cnt3 = mysql_num_rows($r3);
// etc....

 

any suggestions would be appreciated!

 

Thanks!!

[chrisshennan]

Hi,

 

How about something like:-

 

$q1 = "SELECT cat, COUNT(*) as number_per_category FROM pics GROUP BY cat";

$r1 = mysql_query($q1) or die(mysql_error());

 

while ($row = mysql_fetch_assoc($r1)) {

  echo $row['cat'] . ' has ' . $row['number_per_category '] . ' pictures<br>';

}

 

 

This will get you back all the categories and the number of pictures per category with a single query and you can then loop round the results to display them.  This also has the benefit of including the additional categories that get added later on without having to add another section of code as per your example.

 

[Oziam]

Yep that does the trick nicely,

 

Thanks for your help, this is a great solution, as you stated it

has the benefit of being able to add more categories also.

 

Cheers!