php mysql if sum not working

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(my开发者_如何学JAVAsql_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?

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;
}

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

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?