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...

 

<?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.

 

<?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 -

l.SUM(quant)

 

Should be something like -

 

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 -

l.SUM(quant)

 

Should be something like -

 

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