php mysql if sum not working

Question

trans_id  trans_product  trans_user  trans_date  trans_date2  trans_description  trans_inventory
     13         6              1     1278604284   1278547200                     -1000
      9         5              1     1278601462   1278547200    New Arrival        200
     11         7              1     1278601743   1278547200                        50
     12         6              1     1278601756   1278547200                      5000

$sql = mysql_query("SELECT *, SUM(IF(trans_inventory>0,trans_inventory,0)) as pos "
                  +"FROM site_trans GROUP BY trans_product") or die(mysql_error());
while($row = mysql_fetch_array($sql)) {
    $amt_p = $row['pos'];
    $sql2 = mysql_query("SELECT *, SUM(IF(trans_inventory<0,trans_inventory,0)) as neg "
                      + "FROM site_trans GROUP BY trans_product") or die(mysql_error());
    while($row2 = mysql_fetch_array($sql2)) {
        $amt_n = $row2['neg'];
    }

    echo $amt_p; //working
    echo $amt_n; // not working
}

First sql query is working ($amt_p) however second one is for negative isn't working($amt_n) any idea what im doing wrong?

I think your while loops are wrong, do you really want the second sql query inside the first while loop? — DeveloperChris, Jul 09 '10 at 05:37

score 2 · Accepted Answer · answered Jul 09 '10 at 05:35

2

You're overwriting $amt_n for each product. I'd suggest combining the queries into something like this:

$sql = mysql_query("SELECT *, "
                  + "SUM(IF(trans_inventory>0,trans_inventory,0)) as pos, "
                  + "SUM(IF(trans_inventory<0,trans_inventory,0)) as neg "
                  + "FROM site_trans GROUP BY trans_product") or die(mysql_error());
while($row = mysql_fetch_array($sql)) {
    $amt_p = $row['pos'];
    $amt_n = $row['neg'];

    echo $amt_p;
    echo $amt_n;
}

answered Jul 09 '10 at 05:35

Mark Elliot

75,278
22
140
160

i cannot upvote the answer because not enough reputation. Anyhow, good job Mark. Its working perfectly. – damien Jul 09 '10 at 05:40
@DeveloperChris, what? the intent from the original query seemed to be "positive" and "negative" values grouped by trans_product, that's what this produces. (not to mention the OP is saying this is yielding correct behavior) – Mark Elliot Jul 09 '10 at 05:52
@DeveloperChris: furthermore, see the [OP's other post](http://stackoverflow.com/questions/3205513/mysql-fetch-sum-php) which confirms this is correct. – Mark Elliot Jul 09 '10 at 05:55
yeah my bad, The OP actually changed his question partway through, the original actually summed the positive and negative values for ALL records, while your answer displays a different result for each grouping. When I responded I was referring to his original source code he provided. sorry for the inference, your answer works fine for his reworked question. (which still doesn't make much sense). – DeveloperChris Jul 12 '10 at 00:54

score 1 · Answer 2 · answered Jul 09 '10 at 05:34

1

Add $amt_n = 0 to initialise it
change $amt_n = $row2['neg']; to $amt_n += $row2['neg'];

the problem is you are zeroing the value when iterating over it

You need to do this for both pos and neg

DC

answered Jul 09 '10 at 05:34

DeveloperChris

3,412
2
24
39

score 0 · Answer 3 · answered Jul 09 '10 at 05:28

0

Not sure why it's not working, but it looks to me that you just want all the positive and negative trans_inventory rows. How about you add "where trans_inventory>0" to your first query and get rid of that conditional IF hoohah?

SELECT *, SUM(trans_inventory) as pos 
FROM site_trans
WHERE trans_inventory>0
GROUP BY trans_product

And what exactly do you mean by "not working"? What values are you getting, and what were you expecting?

answered Jul 09 '10 at 05:28

Andy Lester

91,102
13
100
152

i getting 0 and its should be -1000 on product_id 6 – damien Jul 09 '10 at 05:32

php mysql if sum not working

3 Answers3