Problem displaying results of count query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gavin1512
Forum Newbie
Posts: 6
Joined: Mon Mar 10, 2008 1:46 pm

Problem displaying results of count query

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Re: Problem displaying results of count query

Post 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.
gavin1512
Forum Newbie
Posts: 6
Joined: Mon Mar 10, 2008 1:46 pm

Re: Problem displaying results of count query

Post by gavin1512 »

Thanks a lot all working now :D
Post Reply