Simple Count

[wmguk]

Hey guys,

 

I have an order_details table and all the items which have been ordered go into it.

 

I'm trying to find out how many items are postid =1  so ive used the code below...

 

only issue is some of the items are qty of 10, so for example sake, say:

 

ITEM1 x 5 (postid 1)

ITEM2 x 10 (postid 1)

ITEM3 x 8 (postid 3)

 

I need to know that there are 15 items postid 1 and 8 postid 3 - but the script only says 2 items postid 1 and 1 item postid 3... because there is only 1 record... how can i get it to work it out using the qty field too?

$countsql = "SELECT * , count(`postid`) as Counter FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`";
echo $countsql;
$r_counts = mysql_query($countsql);
while($coun = mysql_fetch_array($r_counts)) 
{
	$postid = $coun['postid'];
	$counter = $coun['Counter'];
	echo $counter;
}

[Yesideez]

Remove the GROUP BY on the end - should work if I read your post right.

[wmguk]

Hi,

 

I tried that but dont get any result now...

 

what i need to do is get the count to also check qty in the DB and add the qty...

[steelaz]

Try adding to your query:

 

SUM(`post_id`) as Sum

 

 

[revraz]

The GROUP BY clause is required in this type of query.

 

See if this works.

 

"SELECT postid, count(*) as Counter FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`";

[wmguk]

The GROUP BY clause is required in this type of query.

 

See if this works.

 

"SELECT postid, count(*) as Counter FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`";

 

Ok, I tried this one, and I got

 

2

1

 

so it knows that the order is 2 x postid 1 and 1 x postid 3

 

however it needs to work out the qty too... so postid 1 should be 15.....

 

ITEM1 x 5 (postid 1)

ITEM2 x 10 (postid 1)

ITEM3 x 8 (postid 3)

[wmguk]

Try adding to your query:

 

SUM(`post_id`) as Sum

 

 

 

tried this but didnt get any output, again, i dont think this will take account of the qty

 

man this is making my brain ache... must be a way to do it though!

[wmguk]

currently using

$countsql = "SELECT postid, count(*) as Counter FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`";
echo $countsql;
$r_counts = mysql_query($countsql);
while($coun = mysql_fetch_array($r_counts)) 
{
	$postid = $coun['postid'];
	$counter = $coun['Counter'];
	$qty = $coun['prodqty'];

//$counter = ($counter * qty);
echo $counter;

}

but doesnt work either....

[steelaz]

This - "$qty = $coun['prodqty'];" doesn't work because you don't have "prodqty" in SELECT query.

 

Again, try:

 

SELECT postid, COUNT(*) as Counter, SUM(prodqty) as Quantity FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`

 

If it doesn't work, run it in phpMyAdmin (replace $ordid) and let us know what the outcome is.

 

 

[wmguk]

This - "$qty = $coun['prodqty'];" doesn't work because you don't have "prodqty" in SELECT query.

 

Again, try:

 

SELECT postid, COUNT(*) as Counter, SUM(prodqty) as Quantity FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`

 

If it doesn't work, run it in phpMyAdmin (replace $ordid) and let us know what the outcome is.

  

 

 

Ok, in myadmin sql...

 

it works!!!!

 

postid  Counter  Quantity 

1          2            11

2          1            1

3          1            1

 

however on my php page i get

0

0

0

 

$countsql = "SELECT postid, COUNT(*) as Counter, SUM(prodqty) as Quantity FROM `orders_details` WHERE order_id = '$ordid' GROUP BY `postid`";
echo $countsql."<br>";
$r_counts = mysql_query($countsql);
while($coun = mysql_fetch_array($r_counts)) 
{
	$postid = $coun['postid'];
	$counter = $coun['Counter'];
	$qty = $coun['Quantity'];

	$counter2 = ($counter * qty);
	echo "Count: " .$counter2."<br>";

}

[steelaz]

You forgot dollar sign before your variable name:

 

$counter2 = ($counter * $qty);