How to SUM Quantity from database with GROUP BY function using php

frecia18
Posts: 1

How to SUM Quantity from database with GROUP BY function using php

Code: Select all

<?php     mysql_connect('localhost', 'root');     mysql_select_db('inventory'); if(isset($_GET['from_date']) && isset($_GET['to_date']) && $_GET['from_date']!='' && $_GET['to_date']!='') {     error_reporting (E_ALL ^ E_NOTICE);             $selected_date=$_GET['from_date'];               $selected_date=strtotime( $selected_date );             $mysqldate = date( 'Y-m-d H:i:s', $selected_date ); $fromdate=$mysqldate;             $selected_date=$_GET['to_date'];               $selected_date=strtotime( $selected_date );             $mysqldate = date( 'Y-m-d H:i:s', $selected_date ); $todate=$mysqldate; ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Inventory Report</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <style type="text/css" media="print"> .hide{display:none} </style> <script type="text/javascript"> function printpage() { document.getElementById('printButton').style.visibility="hidden"; window.print(); document.getElementById('printButton').style.visibility="visible";   } </script> <body> <input name="print" type="button" value="Print" id="printButton" onClick="printpage()"> <table width="100%" border="0" cellspacing="0" cellpadding="0">   <tr>     <td align="center">           <img src="images/logo.jpg" width="80px" align="left" height="80px">           <div align="center">             <h1 style="font-family: Arial; font-size: 25px; margin-top: 2px;">LM Cereal Corporation (LMCC)</h1>             <p style="font-family: Arial; font-size: 12px; margin-top: 2px;">First Tondo Complex 2150 Velasquez St., Brgy. 94 Zone 08 Dist. I Tondo Manila<br>             Reg. TIN: 004-516-647-000<br>             Tel: 255-2225 &nbsp; Fax: 255-6565</p>            </div>         <tr>         <?php                  $selecteddate = mysql_query("SELECT YEAR(  `cr_date` ) AS YEAR FROM orders WHERE  `cr_date` BETWEEN  '$fromdate' AND  '$todate' GROUP BY YEAR");                  $row = mysql_fetch_array($selecteddate);         ?>           <td align="center"><strong><?php echo $row['YEAR'] ?> Inventory Report</strong></td>         </tr>         <table border="1" align="center" width="100%" cellspacing="0" cellpadding="0">         <thead align="center" style="font-weight: bold;">         <th>Items</th>         <th>Total in</th>         <?php         $query = "SELECT DISTINCT DATE_FORMAT( cr_date,  '%b' ) AS MONTH FROM orders WHERE cr_date BETWEEN  '$fromdate' AND  '$todate' ";         $queryMonth = mysql_query($query) or die(mysql_error());         ?>         <?php while ($rowD = mysql_fetch_array($queryMonth )) { ?>         <th><?php echo $rowD['MONTH']; ?></th><?php } ?>         <th>Total out</th>         <th>Balance</th>         </thead>         <tbody style="font-family: Verdana; font-size:11px;">         <?php         $viewProd = "SELECT prod_mixcode FROM products";                  $queryProd = mysql_query($viewProd) or die(mysql_error());         ?>         <?php while ($rowP = mysql_fetch_array($queryProd )) { ?>         <tr>         <td><?php echo $rowP['prod_mixcode']; ?></td>                  <?php         $products=$rowP['prod_mixcode'];         $viewTqty = "SELECT SUM(prod_qty) as totalQty FROM dr_manila WHERE prod_mixcode='$products' AND `dr_date` BETWEEN  '$fromdate' AND  '$todate'";                  $queryTqty = mysql_query($viewTqty) or die(mysql_error());         ?>                  <?php while ($rows = mysql_fetch_array($queryTqty )) { ?>                  <td><?php echo $rows['totalQty']; ?></td><?php } ?>                  <?php         $sql_qtypermonth ="SELECT SUM(  `prod_qty` ) AS totalQtybymonth, MONTHNAME(  `dr_date` ) AS  MONTH FROM  `dr_manila` WHERE `dr_date` BETWEEN  '$fromdate' AND  '$todate' GROUP BY MONTH ";         $queryqtypermonth = mysql_query($sql_qtypermonth) or die(mysql_error());                 ?>         <?php while ($row = mysql_fetch_array($queryqtypermonth )) { ?>         <td><?php echo $row['totalQtybymonth']; ?></td>         <td><?php echo $row['totalQtybymonth']-$row['totalQtybymonth'];  ?></td>         <?php } ?><?php } ?>         <?php           /** start get the sum **/                 $sql_order = mysql_query("SELECT prod_mixcode, sum(prod_qty), MONTHNAME(  `cr_date` ) AS MONTH FROM orders  WHERE cr_date >= '$fromdate' AND cr_date <= '$todate'");                      $data_order = mysql_fetch_array($sql_order);                 echo $data_order[0];                 echo $data_order[1];                 echo $data_order[2];         /** end get the sum **/         ?>                   <?php function getSumQtybyDateFromOrders($startDate,$endDate){     $sql_qtybydate = mysql_query("SELECT MONTHNAME(  `cr_date` ) AS MONTH FROM  `orders` WHERE `cr_date` BETWEEN  '$startDate' AND  '$endDate'");           $data_qtybydate = mysql_fetch_array($sql_qtybydate);          echo $data_qtybydate['MONTH']; } getSumQtybyDateFromOrders($fromdate,$todate);      ?>         </tr>         <tbody>         </table>     </td>  </tr>          </table> </body> <footer> <p align="center"> Developed By: <strong>A2P2 Systems</strong> </br> Email:systemsa2p2@gmail.com</p> </footer> </html> <?php } else{ echo "Please set from and to date to process inventory report"; } ?>
HELP ME Please :) Monthly and Yearly inventory report..

Similar Topics