Solved

Sum of all rows in table using PHP

Posted on 2011-09-07
7
504 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
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 110

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

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 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