Topic: SUM() not working in this array

[phpstuck]

I solved a bigger problem and thought I was on the way to finishing this particular page. I am pulling from two tables. I am trying to SUM() the quantity from the group. If I pull from only one table it works like a dream...

Code: [Select]

<?php
include_once 'db.php';

$query = "SELECT groccat, SUM(quant) FROM inven GROUP BY groccat"; 
 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['groccat']. " = ". $row['SUM(quant)'];
echo "<br />";
}
?>

That works like a charm to get a total of all similar items.

However when pulling from two tables I get this errror:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\xampp\htdocs\inventory\shoppinglist.php on line 10

Warning: Invalid argument supplied for foreach() in D:\xampp\htdocs\inventory\shoppinglist.php on line 17

Here is the code I am trying to get the sum to work in.

Code: [Select]

<?php

include_once 'db.php';


echo "<hr>";

$deflist=mysql_query(        
"SELECT l.groccat, l.SUM(quant), b.grocname, b.min, b.tobuy FROM inven l, groc b WHERE l.groccat = b.grocname GROUP BY 

grocname");
while ($all = mysql_fetch_array($deflist)) {
   $results[$all['grocname']][] = array ('quant' => $all['SUM(quant)'], 'min' => $all['min'], 'tobuy' => $all['tobuy']);
}




foreach ($results as $catName => $catData)

{

   print('<center><TABLE id=AutoNumber20 style="BORDER-COLLAPSE: collapse" borderColor=#000000 
      bgcolor=blue height=12 cellSpacing=3 cellPadding=3 width=600 border=1>
      <TBODY>
      <TR><TD>
<b><font face=arial size=2 color=white>'.stripslashes($catName).'</b><br/></font></td></tr></table></center>'."\n");

foreach ($catData as $itemNum => $itemData)
   
   {

      
    print('<center><TABLE id=AutoNumber21 style="BORDER-COLLAPSE: collapse" borderColor=#000000 
      height=12 cellSpacing=3 cellPadding=3 width=600 border=0>
      <TBODY>
      <TR><TD><font face=arial size=2>On Hand: ' .$itemData['quant'].',<b> Minimum: '.$itemData['min'].'</b> To Buy: '. $itemData

['tobuy'].
'<br/></td></tr></table></center></font>'."\n"); 
      
      
   }
}
echo '</td></tr></table></center>';


 

?>

If I take out the two references to SUM() it prints the following results in the browser. I want to total all ON HAND things under the single group heading. Instead of it printing a new line for each brand under the heading.

Baking Soda
On Hand: 1, Minimum: 4 To Buy: 2
Mollasses Standard Size
On Hand: 2, Minimum: 3 To Buy: 2
Cake Mix
On Hand: 2, Minimum: 12 To Buy: 3
On Hand: 2, Minimum: 12 To Buy: 3
On Hand: 1, Minimum: 12 To Buy: 3
Muffin Mix
On Hand: 1, Minimum: 4 To Buy: 4
On Hand: 3, Minimum: 4 To Buy: 4
Canned Corn 10 - 12 oz.
On Hand: 10, Minimum: 36 To Buy: 10
Mens Deodorant
On Hand: 2, Minimum: 12 To Buy: 5
On Hand: 1, Minimum: 12 To Buy: 5
On Hand: 1, Minimum: 12 To Buy: 5
On Hand: 1, Minimum: 12 To Buy: 5
On Hand: 1, Minimum: 12 To Buy: 5
On Hand: 0, Minimum: 12 To Buy: 5


See if there is more than one brand it shows a total for each brand, whereas I want it to just add up the on hands for each category and show a single total on hand.  Like this:

Baking Soda
On Hand: 1, Minimum: 4 To Buy: 2
Mollasses Standard Size
On Hand: 2, Minimum: 3 To Buy: 2
Cake Mix
On Hand: 5, Minimum: 12 To Buy: 3

ANY HELP WOULD BE GREATLY APPRECIATED :-)

[PFMaBiSmAd]

To start with (I don't know if this is all the errors in the query), this -

Code: [Select]

l.SUM(quant)
Should be something like -

Code: [Select]

SUM(l.quant)
And if you echo mysql_error() when your query fails, mysql/php will help you find where and why the query is failing.

[phpstuck]

To start with (I don't know if this is all the errors in the query), this -

Code: [Select]

l.SUM(quant)
Should be something like -

Code: [Select]

SUM(l.quant)
And if you echo mysql_error() when your query fails, mysql/php will help you find where and why the query is failing.

If I do that the only thing that prints to the browser is:   (and no errors on the mysql_error() )

Baking Soda
On Hand: , Minimum: 4 To Buy: 2


It echos those two lines and nothing else follows