CODEWITHSUNDEEP
phpmysql
Nate
Nate

Reputation: 28384

Effective wayto sum values in MySQL table rows

I have a MySQL table that looks something like this:

+--------------------------------------+
|   id   |    product_id   |   qty     |
+--------------------------------------+
|    1   |        0        |    1      |
|    2   |        1        |    3      |
|    3   |        0        |    2      |
|    4   |        2        |    18     |
+--------------------------------------+

I want to get the total number of each product in the table. So for the above table, for instance, here is the result I would like:

0 -> 3
1 -> 3
2 -> 18

I figured the easiest way to do this would be to loop through the MySQL results and add the quantity of each product to an array, at the position in the array that corresponds to the product_id. I.E:

$qtyArray = array();

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
    $qtyArray[$row[product_id]] += $row[qty];
}

I have two questions:

  1. Would the above work OK?
  2. Is there a better way of doing this?

Thank you!

Upvotes: 1

Views: 2195

Answers (3)

AD7six
AD7six

Reputation: 66217

As mentioned - you're better off using a group query as that's a lot more efficient.

However:

$qtyArray[$row[product_id]] += $row[qty];

That's going to trigger a notice unless the variable is already set. That's beause it's equivalent to:

$qtyArray[$row[product_id]] = 
    $qtyArray[$row[product_id]] + // First time around this won't exist
    $row[qty];

If (in other circumstances) you need to do something similar - check that the array key exists if there's any doubt.

Upvotes: 1

Zefiryn
Zefiryn

Reputation: 2265

As to the first question. It will work, but Notice "Undefined offset" will be issued

Also remember that array index must be a string so 

$qtyArray[$row['product_id']] += $row['qty'];

Upvotes: 1

Dan Blows
Dan Blows

Reputation: 21174

MySQL does this for you:

SELECT product_id, sum(qty)
FROM tablename
GROUP BY product_id

Upvotes: 8

Related Questions