Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sum of all rows in table using PHP

Posted on 2011-09-07
7
Medium Priority
?
508 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 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