[SOLVED] hard to explain

[justinh]

Let me see if I can attempt to explain my question.

 

First off here's the code in question

 

 
<?php

   $query = "SELECT * FROM hrhuntcart"; 
   $getgame = mysql_query($query) or die(mysql_error()); 
   $number = mysql_num_rows($getgame); 
   
   if($getgame == 0){ 
       
   
     echo  "No game reserved."; 

    
}else{ 

echo "<table width=\"400\" cellpadding=\"20\"><tr><td>Game</td><td>Qty</td></tr>";

while($game = mysql_fetch_array($getgame)){
         $itemid = $game['itemid']; 
         $qty = $game['qty'];
                    
                       if(isset($_SESSION[$itemid])){ 

		     $_SESSION[$itemid] = $_SESSION[$itemid] + $qty; 

               } else { 

		     $_SESSION[$itemid] = $qty; 

               }

		 }

		 while($game = mysql_fetch_array($getgame)){ 


		  echo "</table>";
		  
		  } ?> 

 

bahh.. this is hard to explain, basically I have a table full of purchased items.. I want to display the total number of purchases for each item. Heres how my table is set up

 

id  itemid huntid qty

20 12 28037 4

19 12 28037 4

18 12 28037 4

17 12 28037 3

16 12 28037 3

15 12 28037 3

14 12 28037 3

13 12 28037 3

12 12 28037 3

21 19 22490 5

22 12 22490 20

23 13 24635 54

24 21 24635 5

 

I'm sorry if this isn't clear enough, =/

[premiso]

$query = "SELECT `itemid`, `huntid`, SUM(qty) as sum_qty FROM hrhuntcart GROUP BY `item_id`, `huntid`"; 

 

Should do it.

[justinh]

Would this work?

 

<?php
while($game = mysql_fetch_array($getgame)){ 
   
         $itemid = $game['itemid']; 
		 $qty = $game['qty'];


		 if(isset($_SESSION[items][$itemid])){ 

		 $_SESSION[items][$itemid] = $_SESSION[items][$itemid] + $qty; 

		 } else { 

		 $_SESSION[items][$itemid] = $qty; 

		 }

		 }

   ?>

 

if so could you explain how I would echo this

 

[.josh]

use the query premiso provided. It does all the totaling for you, so all you have to do is echo it out.  You should always keep your data filtering on the query level, because your database is optimized to do that sort of thing.

[justinh]

Sweet worked like a charm

 

Just one problem

 

 
   <?php 
  $query = "SELECT `itemid`, `huntid`, SUM(qty) as sum_qty FROM hrhuntcart GROUP BY `itemid`, `huntid`"; 
  $runquery = mysql_query($query) or die(mysql_error()); 
  $number = mysql_num_rows($runquery); 
   
            if($number == 0){ 
            echo  "No game reserved."; 

    
     }else{ 

   echo "<table width=\"400\" cellpadding=\"20\"><tr><td>Game</td><td>Qty</td></tr>"; 


   while($display = mysql_fetch_array($runquery)){ 

      $getname = "SELECT * FROM hrhuntitems WHERE id = $display[itemid]"; 
      $doquery = mysql_query($getname) or die(mysql_error());
        while($getitemname = mysql_fetch_array($doquery)){ 
        $itemname = $getitemname['item_name'];
         } 


      echo "<tr><td>".$itemname. " </td><td> " . $display['sum_qty']. "</td></tr>"; 

} 

echo "</table>";

}
?>

 

Here's the tables 

 

hrhuntcart

id   itemid 	huntid 	qty
20 	12 	28037 	4
19 	12 	28037 	4
18 	12 	28037 	4
17 	12 	28037 	3
16 	12 	28037 	3
15 	12 	28037 	3
14 	12 	28037 	3
13 	12 	28037 	3
12 	12 	28037 	3
11 	12 	28037 	3
21 	19 	22490 	5
22 	12 	22490 	20
23 	13 	24635 	54
24 	21 	24635 	5
25 	33 	24635 	15
26 	22 	9595 	          1
27 	22 	9595 	          1
28 	29 	9595 	          1
29 	22 	9595          1
30 	29 	9595 	        1
31 	22 	9595 	        1
32 	29 	9595 	        1
33 	28 	28281 	4
34 	28 	28281 	4
35 	28 	28281 	4
36 	28 	28281 	4
37 	28 	28281 	4
38 	28 	28281 	4

 

 

This is the output of the code:

Game	Qty
Whitetail Doe 	20
Whitetail Doe 	33
Whitetail Cull Buck 1 	54
Whitetail Trophy Buck(130-139) 	5
Whitetail Trophy Buck (150-159) 	5
Whitetail Trophy Buck(160-169) 	4
Axis Doe 	24
Axis Buck( less than 32) 	3
Axis Buck (36) 	15

 

Whitetail deer is displaying twice (itemid 12). Why would it display twice? Thanks guys

 

 

 

[.josh]

because you have whitetail doe in your db with two different huntid's and you are grouping by both itemid and huntid.  If you take out the huntid in the group by it will list the total for just itemid (whitetail doe - 53)

[justinh]

sweet thanks CV