[RESOLVED] Summing Query Factory Fields

[fotofx]

I have a query result that has several results that I want to total.

queryFactoryResult Object ( 
   [is_cached] => 
   [resource] => Resource id #339 
   [cursor] => 0 
   [EOF] => 
   [fields] => Array ( 
      [products_quantity] => 1 
      [final_price] => 1700.0000 
   ) 
)

This is one object.. I want to total the [products_quantity] and [final_price] in the final one there will be about 100 in each array.

Any help is appreciated.

[dagon]

array_sum() look at the user notes for functions to sum in multid arrays.

[fotofx]

Yeah I was playing with that but can't get it quite right.. The above query result is called $beds_query

Can you start me in the right direction?

[fotofx]

I tried

$beds = (array_sum($beds_query->fields['products_quantity']));
echo $beds;

That did not work??

[NogDog]

Option 1: Depending on what exactly you are trying to accomplish, you may be better served by having the DB query do the summing for you (e.g. with the SUM() function in MySQL, possibly also with a GROUP BY clause if needed/desire).

Option 2: You could loop through the query results as you typically might to output each result row, but instead increment counters for each value. As I don't know how your application is getting to this point, I don't know exactly what to suggest, but it might be something like:

$quantity = 0;
$total = 0;
foreach($whateverContainsYourObjects as $obj) {
   $quantiy += $obj->fields->products_quantity;
   $total += $obj->fields->final_price;
}

[fotofx]

I am not opposed to doing it in the db. My current query is:

"select products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where products_id IN ('1','2')";

How would I change this to get the sum of products_quantity and final_price?

[fotofx]

OK, I used the SUM in mysql to get the totals I need.. But I am trying to echo the two fields out and can't get it to work. Here is the print_r

queryFactoryResult Object ( [is_cached] => [resource] => Resource id #339 [cursor] => 0 [EOF] => [fields] => Array ( [SUM(products_quantity)] => 7 [SUM(final_price)] => 16545.0000 ) ) Resource id #340
1

Also I am going to want the 16545.0000 trimmed to 2 decimal places..

[fotofx]

OK, I have it working using:

echo $beds_query->fields['SUM(products_quantity)'];
echo $beds_query->fields['SUM(final_price)'];

But now I need to add a bit more to my query and have screwed up the join I think. Here is my query:

SELECT
SUM(orders_products.products_quantity),
SUM(orders_products.final_price),
FROM
orders_products
JOIN orders ON orders.orders_id =orders_products.orders_id
WHERE
orders_products.products_id IN (1, 2) AND
orders.date_purchased >= 2010-01-01

This syntax is wrong according to mysql.. Can you check?

[NogDog]

First thing I see is there should not be a comma just before the FROM. Also, you might want to assign an alias for each sum, just to make it more convenient:

. . . SUM(orders_products.products_quantity) AS prod_qty, . . .

Then in the PHP code that handle the result, the array key would just be 'prod_qty' (or whatever alias you'd prefer to use) instead of 'SUM(products_quantity)'.

[fotofx]

Yup... Missed that damm comma.. That fixed it.. Thanks

I was going to use the alias but this is going thru a query factory function and I was not sure if it is supported.