query count help
am 21.12.2005 18:05:28 von MattMika
On my orders page I have this code to generate a simple menu of orders
statuses so users can browse orders by status.
$query = mysql_query("SELECT * FROM orders_status");
while ($r = mysql_fetch_array($query))
{
$status_name = $r["status"];
$status_id = $r["status_id"];
echo "
href=\"orders.php?status=$status_id&statusname=$status_name\ ">$status_name
- $num | ";
}
?>
|
What I want to do is join my orders table so that I can echo the
number($num) of orders matching each status.
Something like;
Pending-4, Processing-3, Confirmed-2, Cancelled-1
I'll have to join the orders table, but Im not sure how to go about getting
the count.
My tables look like this:
TABLE_ORDERS
------------
order_id order_status user_id
1 2 1
TABLE_ORDERS_STATUS
-------------------
status_id status
1 Pending
2 Processing
3 Confirmed
4 Cancelled
TIA for any suggestions or pointers.
--
MattMika
Re: query count help
am 22.12.2005 01:34:17 von IndyTim
MattMika wrote:
> Got it, thnx anyway...
>
>
>
>
> $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 "
> href=\"orders.php?status=$status_id&statusname=$status_name\ ">$status_name
> - $num | ";
> }
> ?>
>
> |
>
>
>
> MattMika wrote:
>
>
>>On my orders page I have this code to generate a simple menu of orders
>>statuses so users can browse orders by status.
>>
>>
>>
>>
>>$query = mysql_query("SELECT * FROM orders_status");
>>while ($r = mysql_fetch_array($query))
>>{
>>$status_name = $r["status"];
>>$status_id = $r["status_id"];
>>echo "
>>href=\"orders.php?status=$status_id&statusname=$status_nam e\">$status_name
>>- $num | ";
>>}
>>?>
>>
>> |
>>
>>What I want to do is join my orders table so that I can echo the
>>number($num) of orders matching each status.
>>
>>Something like;
>>Pending-4, Processing-3, Confirmed-2, Cancelled-1
>>
>>I'll have to join the orders table, but Im not sure how to go about
>>getting the count.
>>
>>My tables look like this:
>>
>>TABLE_ORDERS
>>------------
>>order_id order_status user_id
>>1 2 1
>>
>>TABLE_ORDERS_STATUS
>>-------------------
>>status_id status
>>1 Pending
>>2 Processing
>>3 Confirmed
>>4 Cancelled
>>
>>TIA for any suggestions or pointers.
>>
>
>
Matt,
Another approach if all you are interested in is the number of records
returned from the query is to use the function mysql_num_rows($query).
With this function, you wouldn't have to dive into the actual extraction
of data via the mysql_fetch_array(...).
FWIW,
IndyTim
Re: query count help
am 22.12.2005 16:35:51 von Hilarion
> Got it, thnx anyway...
>
>
>
>
> $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 "
> href=\"orders.php?status=$status_id&statusname=$status_name\ ">$status_name
> - $num | ";
> }
> ?>
>
> |
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
Re: query count help
am 04.01.2006 00:28:34 von MattMika
Hilarion wrote:
> 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").
You are correct. After two weeks away from this I realized after adding many
orders for testing that this is exactly the case.
>
> 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
Thanx!