?
Solved

Sum of all rows in table using PHP

Posted on 2011-09-07
7
Medium Priority
?
507 Views
Last Modified: 2012-05-12
I have a table whose data is coming from a MySQL table. here is my query. and I am using DREAMWEAVER CS5 .

$query_Recordset2 = "SELECT internetitems.SKU, internetitems.`DESCRIPTION`, internetitems.`UNIT COST`, internetitems.`UNIT RETAIL`, SUM(allcatalogs.`NET UNITS`) as QtyTotal FROM internetitems, allcatalogs WHERE internetitems.SKU = allcatalogs.SKU GROUP BY internetitems.SKU";

Open in new window


and I am calcuating the Total Cost and Totat Retail using a If clause with the help of PHP like below

<?php $WtotalQuantity = 0; $TotalCost = 0; $TotalRetail = 0; ?> 
<table cellpadding="0" cellspacing="0">
  <tr>
    <th style="width:120px;">Sku</th>
    <th style="width:360px;">Description</th>
    <th style="width:60px;">Unit Cost</th>
    <th style="width:60px;">Unit Retail</th>
	<th style="width:60px;">Rerun Start OH</th>
    <th style="width:80px;">Qty Sold as of <?php echo date("m/d/y"); ?></th>
    <th style="width:120px;">Total Cost</th>
    <th style="width:120px;">Total Retail</th>
  </tr>
   <?php do { ?>
     <tr>
       <td style="width:120px; text-align:left;"><?php echo $row_Recordset1['SKU']; ?></td>
       <td style="width:360px; text-align:left;"><?php echo $row_Recordset1['DESCRIPTION']; ?></td>
       <td style="width:60px; text-align:right;">$<?php echo $row_Recordset1['UNIT COST']; ?></td>
       <td style="width:60px; text-align:right;">$<?php echo $row_Recordset1['UNIT RETAIL']; ?></td>
	   <td style="width:60px; text-align:right;"><?php echo $row_Recordset1['RERUN START OH']; ?></td>
       <td style="width:60px; text-align:right;"><?php echo $row_Recordset1['QtyTotal']; ?></td>
       <td style="width:120px; text-align:right;">
       <?php
		  if($row_Recordset1['QtyTotal'] < $row_Recordset1['RERUN START OH'])
		    {
			   echo $row_Recordset1['QtyTotal'] * $row_Recordset1['UNIT COST']; 
			}
		  else
		    {
				echo $row_Recordset1['RERUN START OH'] * $row_Recordset1['UNIT COST']; 
			}
				
						  
		 ?>
       </td>
       <td style="width:120px; text-align:right;">$
       <?php
		  if($row_Recordset1['QtyTotal'] < $row_Recordset1['RERUN START OH'])
		    {
			   echo $row_Recordset1['QtyTotal'] * $row_Recordset1['UNIT RETAIL'];
			}
		  else
		    {
				echo $row_Recordset1['RERUN START OH'] * $row_Recordset1['UNIT RETAIL']; 
			}
				
	  
		 ?>
       
       </td>
    </tr>
     <tr>
    <?php $WtotalQuantity += $row_Recordset1['QtyTotal']; ?> 
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
     <td style="width:120px;">&nbsp;</td>
     <td style="width:360px;">&nbsp;</td>
     <td style="width:60px;">&nbsp;</td>
     <td style="width:60px;">&nbsp;</td>
	 <td style="width:60px;">&nbsp;</td>
      <td style="width:60px; text-align:right;"><b><?php echo $WtotalQuantity; ?>  </b></td>
      <td style="width:120px; text-align:right;"><b>$<?php echo $TotalCost; ?></b></td>
      <td style="width:120px; text-align:right;"><b>$<?php echo $TotalRetail; ?> </b></td>
  </tr>
</table>

Open in new window


Now I am trying to display the SUM of Total Cost from the first condition and Sum of Total Retail

Thanks and appreciate it
0
Comment
Question by:niceoneishere
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 36495853
At lines 273, 274, 275 you have attribute value set two times for element input.
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 36495865
I'm sorry, too fast to post an answer: I said stupid thing :-(
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 36495868
lol its ok I was like whaaaaaaaaat lol
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 82

Accepted Solution

by:
hielo earned 2000 total points
ID: 36497220
try:
<?php $WtotalQuantity = 0; $TotalCost = 0; $TotalRetail = 0; ?> 
<table cellpadding="0" cellspacing="0">
  <tr>
    <th style="width:120px;">Sku</th>
    <th style="width:360px;">Description</th>
    <th style="width:60px;">Unit Cost</th>
    <th style="width:60px;">Unit Retail</th>
	<th style="width:60px;">Rerun Start OH</th>
    <th style="width:80px;">Qty Sold as of <?php echo date("m/d/y"); ?></th>
    <th style="width:120px;">Total Cost</th>
    <th style="width:120px;">Total Retail</th>
  </tr>
   <?php do { ?>
     <tr>
       <td style="width:120px; text-align:left;"><?php echo $row_Recordset1['SKU']; ?></td>
       <td style="width:360px; text-align:left;"><?php echo $row_Recordset1['DESCRIPTION']; ?></td>
       <td style="width:60px; text-align:right;">$<?php echo $row_Recordset1['UNIT COST']; ?></td>
       <td style="width:60px; text-align:right;">$<?php echo $row_Recordset1['UNIT RETAIL']; ?></td>
	   <td style="width:60px; text-align:right;"><?php echo $row_Recordset1['RERUN START OH']; ?></td>
       <td style="width:60px; text-align:right;"><?php echo $row_Recordset1['QtyTotal']; ?></td>
       <td style="width:120px; text-align:right;">
       <?php
		  if($row_Recordset1['QtyTotal'] < $row_Recordset1['RERUN START OH'])
		    {
				$TotalCost+=$row_Recordset1['QtyTotal'] * $row_Recordset1['UNIT COST']
			   echo $row_Recordset1['QtyTotal'] * $row_Recordset1['UNIT COST']; 
			}
		  else
		    {
				$TotalCost+=$row_Recordset1['RERUN START OH'] * $row_Recordset1['UNIT COST']; 
				echo $row_Recordset1['RERUN START OH'] * $row_Recordset1['UNIT COST']; 
			}
				
						  
		 ?>
       </td>
       <td style="width:120px; text-align:right;">$
       <?php
		  if($row_Recordset1['QtyTotal'] < $row_Recordset1['RERUN START OH'])
		    {
				$TotalRetail +=$row_Recordset1['QtyTotal'] * $row_Recordset1['UNIT RETAIL'];
			   echo $row_Recordset1['QtyTotal'] * $row_Recordset1['UNIT RETAIL'];
			}
		  else
		    {
				$TotalRetail +=$row_Recordset1['RERUN START OH'] * $row_Recordset1['UNIT RETAIL']; 
				echo $row_Recordset1['RERUN START OH'] * $row_Recordset1['UNIT RETAIL']; 
			}
				
	  
		 ?>
       
       </td>
    </tr>
     <tr>
    <?php $WtotalQuantity += $row_Recordset1['QtyTotal']; ?> 
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
     <td style="width:120px;">&nbsp;</td>
     <td style="width:360px;">&nbsp;</td>
     <td style="width:60px;">&nbsp;</td>
     <td style="width:60px;">&nbsp;</td>
	 <td style="width:60px;">&nbsp;</td>
      <td style="width:60px; text-align:right;"><b><?php echo $WtotalQuantity; ?>  </b></td>
      <td style="width:120px; text-align:right;"><b>$<?php echo $TotalCost; ?></b></td>
      <td style="width:120px; text-align:right;"><b>$<?php echo $TotalRetail; ?> </b></td>
  </tr>
</table>

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36497734
Here's your problem: I am using DREAMWEAVER CS5

I am not saying that to be flip - Dreamweaver is a terrible place to learn PHP.  It has some of the worst code I have ever seen, and was never meant to be a teaching example.  So when you start by following Dreamweaver's model you put yourself at a disadvantage right away.

This little book will help you learn the PHP and MySQL part of things correctly.  As you come to understand how those parts work together (instead of just trying to model your programming on Dreamweaver) you will be way ahead in your programming, I promise.
http://www.sitepoint.com/books/phpmysql4/

It's a great investment - a little money, a little time, and a lot of knowledge.  Best of luck with your project. ~Ray
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 36498100
Brilliant it  worked. I can't believe i missed it.

Thanks and appreciate it.
0
 
LVL 1

Author Closing Comment

by:niceoneishere
ID: 36498114
Thanks
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question