Page 1 of 1

Problem displaying results of count query

Posted: Wed Mar 12, 2008 8:11 am
by gavin1512
Good Afternoon,

I am having a slight issue displaying the results of a count query. I have tested it in the SQL section of PHPmyAdmin and the query runs fine and gets 5 results and the appropriate counts.

Here is my SQL Query~:

Code: Select all

$query="select COUNT(orderid),employee.username from  orders, employee where employee.username = orders.username AND `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "' Group BY orders.username";
                $result=@mysql_query($query,$connection)
                or die("Unable to perform query<br>$query");
I am displaying the results with:

Code: Select all

    <?php
    while($row= mysql_fetch_array($result))
{
?>
    <tr>
      <td>&nbsp;</td>
      <td><?php echo $row['employee.username']?></td>
      <td><?php echo $row['COUNT(orderid)'] ?></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
      <?php
}
?>
I have tried removing the date function but that does not seem to be the problem.

Any assistance would be much appreciated

Re: Problem displaying results of count query

Posted: Wed Mar 12, 2008 8:49 am
by CoderGoblin
The problem you are having is simply the fieldname isn't COUNT(orderid). Not a mysql person but the fieldname is probably returned as simply count.

Normally I use the expression

Code: Select all

$query="select COUNT(orderid) as num_orders,employee.username from  orders, employee where employee.username = orders.username AND `date` BETWEEN '" . $date1 . "' AND '" . $date2 . "' Group BY orders.username";
You then use num_orders as the field name so...

Code: Select all

<?php echo $row['num_orders'] ?>
This becomes especially important when you have several counts in the same select.

Re: Problem displaying results of count query

Posted: Wed Mar 12, 2008 9:06 am
by gavin1512
Thanks a lot all working now :D