Solved

Sum of all rows in table using PHP

Posted on 2011-09-07
7
501 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 82

Accepted Solution

by:
hielo earned 500 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 109

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed with Powershell  XML to MySQL 5 41
error log using ftp 7 41
two ways encryption with php 3 28
Cookie not unsetting 7 19
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
The viewer will learn how to count occurrences of each item in an array.

773 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