Help with query

[graham23s]

Hi Guys,

 

hers a snap shot of my database:

 

http://img261.imageshack.us/my.php?image=dbeq4.jpg

 

i'm trying to make a basic statistics page, where the product and how many of eanch product has been sold!

 

<?php
  case "products-statistics":
  
    // select all the products sold
    $q_sold = "SELECT DISTINCT(`PRODUCT_ID`) FROM `fcp_orders_master_log` ORDER BY `QUANTITY` DESC";
    $r_sold = mysql_query($q_sold);
    
    if (mysql_num_rows($r_sold) < 1)
    {
    
      print("<span class=\"msg_nothing_here\">No product statistics to display.</span>");      
      include("inc/inc-footer.php");
      exit;  
    
    } else {
    
      // display the products
      print("<table width='95%' border='0' cellpadding='5' cellspacing='1' class='tbl_login' />\n");
      print("<tr>\n");
      print("<td colspan='3' align='left' class='c3'><b>Products & quantity sold to date.</b></td>\n");
      print("</tr>\n"); 
      print("<tr>\n");
      print("<td align=\"center\" class='c3'><b>Product Image</b></td><td align=\"center\" class='c3'><b>Product Description</b></td><td align=\"center\" class='c3'><b>Quantity Sold</b></td>\n");
      print("</tr>\n");
      print("<tr class=\"c5\">\n");
      print("<td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td><td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td><td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td>\n");
      print("</tr>\n");
      
      while ($p_row = mysql_fetch_array($r_sold))
      {
      
       // vars
       $pid = $p_row['PRODUCT_ID'];
       $pqy = $p_row['QUANTITY'];
       
       // find out what the products were
       $qP = "SELECT * FROM `fcp_products` WHERE `ID`='$pid'";
       $rP = mysql_query($qP);
       $aP = mysql_fetch_array($rP);
       
       // more vars
       $p_th = $aP['product_thumbnail'];
       $p_nm = $aP['product_name'];
       
       // print rows...
       print("<tr><td align=\"center\"><img src=\"products/thumbnails/$p_th\"></td><td align=\"left\"><a class=\"smart_links\" href=\"product-information.php?productid=$pid\">$p_nm</a></td><td align=\"center\"><span class=\"cart_price\">$pqy</span></td></tr>");
      
      } 
      
      print("</table>");
    
    }
?>

 

this query:

 

<?php
    $q_sold = "SELECT DISTINCT(`PRODUCT_ID`) FROM `fcp_orders_master_log` ORDER BY `QUANTITY` DESC";
?>

 

so we have sold 10 of product 243 it shsows up in the table! right now it shows individually!

 

thanks for any help guys

 

Graham

[Barand]

SELECT `PRODUCT_ID`, SUM(quantity) as qty

FROM `fcp_orders_master_log`

GROUP BY PRODUCT_ID

ORDER BY qty DESC

[graham23s]

thanks a ton barry

 

Graham