Show Pirticular Number of Units (Not Total Units)

justlukeyou

Hi,

I am trying to display a number of pirticular units in a database. I have the following which shows the total number of units, but not the pirticular units.

For example, the code below will show: There are 11 Widgets.

What I am trying to show is:

There are 4 Red Widgets.
There are 2 Blue Widgets.
There are 5 Green Widgets.

Does anyone know how to do this please?

There are <?php

$result = mysql_query("SELECT * FROM organiserdbase", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows \n";

?> Widgets

Weedpacket

SELECT particularunit,COUNT(*) FROM organiserdbase GROUP BY particularunit

Where particularunit is whatever field in the table that distinguishes one particular unit from another.

justlukeyou

Hi,

I must be misinterupting how you coded it. I have the following:

$result = mysql_query ("SELECT event manager, COUNT(*) FROM organiserdbase GROUP BY event manager");
$num_rows = mysql_num_rows($result);



echo "$num_rows \n";

I have 3 "event manager" into the database in the 'category' row.

I have also tried:

$result = mysql_query ("SELECT category, COUNT(*) FROM organiserdbase GROUP BY event manager");
$num_rows = mysql_num_rows($result);



echo "$num_rows \n";

Weedpacket

Oh, I dropped the last sentence from my reply.

What you'll get back is a record set with one record for each distinct value of the "event manager" field. Each record will contain that value (so that you can know which value each count relates to), and the number of rows in the table with that value.

justlukeyou

Thanks,

So should I be doing something like this? I tried this but it creates the following error: Warning: mysql_fetch_array()

$result = mysql_query ("SELECT category, COUNT(event manager) FROM organiserdbase GROUP BY category");
while($row = mysql_fetch_array($result)){
	echo $row['category']. " - ". $row['COUNT(event manager)'];
	echo "<br />";
}
echo "$num_rows \n";

Does anyone have any suggestions what I can try next?

bradgrafelman

You could try retrieving the actual error message from the MySQL server (e.g. via [man]mysql_error/man) and attempting to fix your apparently broken query.

Ashley_Sheridan

You appear to be counting a field called "event manager" which contains a space, and is most probably what MySQL is complaining about. Does that field actually contain a space in the name? If it does, you could probably try putting backticks (`) around it so that MySQL knows the whole thing is a single field name, because it's attempting to look for two fields individually called "event" and "manager" right now.

The backticks are also very useful when dealing with fields that are named after one of the reserved words in MySQL (e.g. 'count', 'name', etc)