I have a database with a category field.
There are 5 categories.
I use an include file that contains a text array; If the DB category field has a 1, my php file uses script to echo the appropriate text, 1 = Autos, 2 = DVDs, 3 = Furniture, and so on.
I would like to use one SQL statement to total each category so that the totals for each category are listed on the web page:
Autos (3)
DVDs (10)
Furniture (7)
etc...
I know how to grab the total count for each item:
$sql1 = "SELECT count(id) as total FROM pro_store WHERE category = '1' ";
$query1 = mysql_query($sql1) or die(mysql_error());
$rows1 = mysql_fetch_array($tquery1);
$count1 = $rows1['total'];
$sql2 = "SELECT count(id) as total FROM pro_store WHERE category = '2' ";
$query2 = mysql_query($sql2) or die(mysql_error());
$rows2 = mysql_fetch_array($query2);
$count2 = $rows2['total'];
Is there a way that I can use one SQL statement in a loop?
I've tried this:
for ($p=1;$p <= 5;$p++){
$sql."".$p = "SELECT count(mid) as total FROM pro_store WHERE category = '".$p."'";
$query."".$p = mysql_query($sql."".$p) or die(mysql_error());
$rows."".$p = mysql_fetch_array($query."".$p);
$count."".$p = $rows['total'];
}
but it returned this message:
mysql_fetch_array(): supplied argument is not a valid MySQL.....
Please, someone give me a nudge in the right direction
