counting items in a database

robives

I'm using the following to select from a table

$query = "SELECT item_name, mc_gross, completed, COUNT(*) FROM ipn_tblsaleshistory GROUP BY item_name";
$result = mysql_query($query)
or die("couldn't execute query");

...then...

while ($row = mysql_fetch_array($result)){
extract($row);
echo "<tr>";
echo"<td> $item_name </td>";
echo"<td> £ $mc_gross </td>";
echo" <td> $COUNT </td>";
echo"<td> $completed </td>";
echo "</tr>";
}
to display the results -
I can't seem to get the value of $COUNT
to display - what am I doing wrong?
TIA
best
- Rob

mrhappiness

COUNT is not the complete name
use $row['COUNT(*)'] instead of $COUNT

or...

SELECT 
  item_name, mc_gross, completed, 
  COUNT(*) the_count
FROM ipn_tblsaleshistory 
GROUP BY item_name

extract($row);
echo $the_count;

hth

robives

Perfect :-)
Thanks!

best
- Rob

Jason_Batten

How is count different from mysql_num_rows?

Weedpacket

Originally posted by robives
Perfect :-)
Thanks!

best
- Rob

Or alternatively, alias the count to something else

"SELECT .... COUNT(*) as item_count ..."
and then read it as $row['item_count'].

Originally posted by NetNed85
How is count different from mysql_num_rows?

mysql_num_rows will return the number of rows in the result set, which in this case will be the number of distinct (and non-null IIRC) 'item_name's. The COUNT will be the number of rows in the table for each item_name.