Page 1 of 1

DB array within array

Posted: Wed Oct 10, 2007 3:18 pm
by php_wannabe
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am trying to create a total from an array using information from an array retrieved via a form post but am having trouble getting the last piece to work. I have searched the forums and do not see a solution so I apologize if this is a repeat. Below is the info concerning the code. Any suggestions would be appreciated and thanks in advance.

PHP code from page capturing form post(Works great):

Code: Select all

<?php

//declare variables
$id = $_POST['id'];


for ($i=0; $i<count($id); $i++){


//Create movement total
$total = MSSQL_QUERY("select sum(price_movement.quantity)
                          from item_price
                          join price_movement on item_price.item_price_id = price_movement.item_price_id
                          where ip_start_date >= '$startf'
                          and ip_end_date <= '$end'
                          and pt_type = '$pt'
                          and item_id = '$id[$i]'

                         ");
$tq = mssql_fetch_array($total);

echo $tq[0]."<br>";

}

?>
The problem I am running into is I need to take the totals from each id in the array as it may change and get a total. I am unable to take the totals for each id, break them out of the array created from the query and then add them together for an overall total.

Example;
id = 14629, 28070, and 28077
generate a total value for each of 413, 522, and 618 respectively.

I can get them to show up with the id information specific to each id number but when i try and get the total, which in this case should be 1553, I get no love. Any thoughts on this from anyone. Let me know if additional info is needed.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Oct 10, 2007 5:44 pm
by Christopher
I am not clear from you description what you are trying to do. You are getting the column total currently. If you use GROUP BY you can get sub totals. Or you can "select price_movement.quantity" and use PHP to do calculate the totals while looping through all the records.

Posted: Wed Oct 10, 2007 5:44 pm
by neophyte
Not sure I understand the problem completely. But if you want an array of individuals you'll need to use a while loop and change your query from an aggregate sum to individual fields.

Posted: Wed Oct 10, 2007 7:12 pm
by php_wannabe
Sorry for not being clear on this. I am getting the totals for each individual id number ok and displaying them fine. It is when I take the totals from the loop through each id and try to add them together for an overall total that I have a problem. In my current example I have 3 different id numbers that generate individual totals. This works great and I can display the value along with the item information fine. The problem I have is when I try and take the individual quantity total from the $total query and add them together for an overall total. Example 3 items 3 different quantities for individual item movement. Add them together for overall total for all items. The problem I am experiencing is getting an aggregate sum no matter what function or method I use. I have tried array_sum, foreach loop, if statement to name some. Let me know if more explanation or code is needed or desired. The ultimate goal is to show the overall movement, get a percentage of total movement for each item, and show the individual items total movement.