[RESOLVED] Multiple field count

jefterwil

I am trying get the total count from multiple fields in the same table. The count total for each field will be different.

$tmp = mysql_query("SELECT cat1, cat2, cat3, cat4, cat5, cat6 FROM pro_store");

$row = mysql_fetch_array($tmp);

$art = count ($row['cat1']);
$auto = count ($row['cat2']);
$bling = count ($row['cat3']);
$clothing = count ($row['cat4']);
$clubs = count ($row['cat5']);
$computers = count ($row['cat6']);

And it returns

Art: 2
Auto: 2
Bling: 2 ... and so on.

There may not be anything in the Art (cat1) field, but it returns the total row count, not an individual category count.
I need to count fields individually that contain a variable, not a total row count.

sneakyimp

i'm not 100% certain...and some SQL master may prove me wrong, but i think you have 2 options:

option 1: six queries...something like this for each query:

SELECT count() FROM pro_store WHERE cat1>0
or
SELECT count() FROM pro_store WHERE cat IS NOT NULL

option 2: loop through all the returned records and add up the counts yourself.

I think that is what you mean anyway. When i read your post again, it occurred to me that you might be wanting to add up the totals in each category...not the number of rows with info for a particular category. if you want to add up cat1 for all records, you would need something like this:

SELECT SUM(cat1) as total1, SUM(cat2) as total2 FROM pro_store

jefterwil

Thank YOU!!!!!!

The SUM sql was exactly what I needed! For you other beginners out there, here is the PHP I used to get my totals:

$tmp = mysql_query("SELECT SUM(cat1) as total1, SUM(cat2) as total2, SUM(cat3) as total3, SUM(cat4) as total4, SUM(cat5) as total5, SUM(cat6) as total6 FROM pro_store");

$rows = mysql_fetch_array($tmp);

$art = $rows['total1'];
$auto = $rows['total2'];
$bling = $rows['total3'];
$clothing = $rows['total4'];
$clubs = $rows['total5'];
$computers = $rows['total6'];
$misc = $rows['total7'];

I'm sure a PHP wizard could figure out a shorter way to do it, but this works for me.

THANK YOU sneakyimp!!!!!!!!!!!!