Posted by Hilarion on 12/22/05 17:35

> Got it, thnx anyway...
>
> <tr><td>
> <table><tr>
> <?
> $query = mysql_query("SELECT * FROM orders_status
> LEFT JOIN orders ON orders.order_status=orders_status.status_id
> ");
> while ($r = mysql_fetch_array($query))
> {
> $status_name = $r["status"];
> $status_id = $r["status_id"];
> $num = count($r["order_id"]);
> echo "<td class=\"box\"><a class=\"alert\"
> href=\"orders.php?status=$status_id&statusname=$status_name\">$status_name
> - $num</a></td>";
> }
> ?>
> </tr></table>
> </td></tr>


I'm not sure if your solution really works OK. As far as I can see
it'll return as many rows for each order status as there are orders
and this will make your script output something like:

Pending - 1, Pending - 1, Pending - 1, Pending - 1, Processing - 1,
Processing - 1, Processing - 1, Confirmed - 1, Confirmed - 1,
Cancelled - 1

So it'll work OK only for those statuses which have only one order
(or have no orders, when it'll output "status name-0").


I'd suggest using your script whith the query replaced by this one:

SELECT
os.status_id,
os.status,
COUNT( o.order_id ) AS num
FROM
orders_status AS os LEFT OUTER JOIN
orders AS o ON o.order_status = os.status_id
GROUP BY
os.status_id,
os.status
ORDER BY
os.status_id

and line:
$num = count($r["order_id"]);
replaced with:
$num = intval( $r['num'] );



Hilarion

[Back to original message]