Sorting and counting

(This will get easier over time, won't it?)

[riverstyx]

I have a table of widgets with fields 'ID','name','shape','color' and I want to pull the data into a report:

Total number of widgets
Number of red widgets (X% of total)
Number of square widgets (X% of total)
Number of square blue widgets (X% of total) etc.

If I do this:

$result = mysql_query("SELECT * FROM widget_table");

// This gives total number of widgets:

$num_rows = mysql_num_rows($result);

echo ("There are " . $num_rows . " total widgets");

That works. But how do I sort out all the square blue widgets, for example? I got this far and now I'm not sure where to take it:

$result = mysql_query("SELECT * FROM widget_table");

if ($num_rows){

while ($widgets = mysql_fetch_row($result)) {

//I know this is wrong:

if( $widgets["color"] = "blue");

Should I have assigned color to a variable first? Any input appreciated.

RS

[Sarah Atkinson]

YOu could use a WHERE

something like

$result = mysql_query("SELECT * FROM widget_table WHERE color='blue' AND shape = 'square'");
then just do a num_rows on that

[riverstyx]

I was trying to make just one call to the dbase (SELECT *) and then sort the data. Otherwise I would have to make several separate queries (blue widgets, blue square widgets, red widgets etc.) Isn't it more efficient to make just one call?

RS

[arran]

Hi riverstyx, here's a solution which uses simple SQL:

Total number of widgets

select count(*) as 'total' from widget_table

Number of red widgets (X% of total)

select count(*) as 'total_red' from widget_table where color = 'red'

Number of square widgets (X% of total)

select count(*) as 'total_square' from widget_table where shape = 'square'

Number of square blue widgets (X% of total) etc.

select count(*) as 'total_blue_square' from widget_table where shape = 'square' and color = 'blue'

Then use these values to work out your percentages.

You could also work out the percentages using entirely SQL.

[riverstyx]

Thank you... I'll give it a go.

RS