Solved

Sub Total, From Dynamic PHP table total

Posted on 2011-03-22
9
451 Views
Last Modified: 2012-05-11
Hi Experts,

What i need is a sub total at the end of every month.

Currently i have companies totals for the number of pounds spent in every month in total for every phone. The company may own 3 phones spend £30 a month in which case the total shown would be £90. However the sub total i want is if more companies where listed and there was a company that had spent £40 for january then the total would be £130. What im after is a way of adding up the dynamicly created totals which are totaled by adding together what exsists in the bill table.

I have attached a copy of my table,

I have also attached a copy of what the output currently looks like.

I have also attached a copy of my current code.

I have also attached a copy of what i would like it to look like.

Is what i have asked for possible??

Many thanks
<?php 
error_reporting(E_ALL);
include 'myphp.php';

  $query = "SELECT customer_name, SUM(Jan09), SUM(Feb09),SUM(Mar09),SUM(Apr09),SUM(May09),SUM(Jun09),SUM(Jul09),SUM(Aug09),SUM(Sep09),SUM(Oct09),SUM(Nov09),SUM(Dec09) FROM bill_detail2009 GROUP BY customer_name"; 
	 
$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Company</th> <th>Jan09</th> <th>Feb09</th> <th>Mar09</th> <th>Apr09</th> <th>May09</th> <th>Jun09</th> <th>Jul09</th> <th>Aug09</th> <th>Sep09</th> <th>Oct09</th> <th>Nov09</th> <th>Dec09</th> </tr>";
// Print out result
while($row = mysql_fetch_array($result)){
	//echo "<tr><td>";
	//echo "Total ". $row['company']. " = £". $row['SUM(Jan09)'] ."    ".$row['SUM(Feb09)'];
	//echo "<br />";
	
	// Print out the contents of each row into a table
echo "<tr><td>"; 
	echo $row['customer_name'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Jan09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Feb09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Mar09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Apr09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(May09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Jun09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Jul09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Aug09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Sep09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Oct09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Nov09)'];
	echo "</td><td>"; 
	echo "£".$row['SUM(Dec09)'];
	echo "</td><td>"; 
	 
} 

echo "</table>";

   ?>

Open in new window

pivture1.png
pivture2.jpg
0
Comment
Question by:NeoAshura
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
I should metion there is more than one mobile number per company so the database contains more than one mobile  bill for one company.
0
 
LVL 16

Assisted Solution

by:Chris Harte
Chris Harte earned 100 total points
Comment Utility
Additions work in php. Your commented out line works with the addition of a plus sign

echo "Total ". $row['company']. " = £". $row['SUM(Jan09)'] ."+".$row['SUM(Feb09)'];

I'm not sure which totals you want adding up so I have not done the code.

0
 
LVL 4

Accepted Solution

by:
m_walker earned 400 total points
Comment Utility
Are you saying that after you show the total for each month for each company, that you want a total to all companies for Jan, then Feb... DEC ?

You could do this two ways.
a) Run a 2nd query and group by month and show the results
or
b) Keep a running total.
eg:
$JanTotal = 0;
$FebTotal = 0;

than in you db loop
echo "£".$row['SUM(Jan09)'];
$JanTotal += $row['SUM(Jan09)'];
      echo "</td><td>";
      echo "£".$row['SUM(Feb09)'];
$FebTotal += $row['SUM(Feb09)'];

then add the results to the table when the db loop ends.

BTW: I like to use the AS and set name for functions in sql.

SELECT customer_name, SUM(Jan09), SUM(Feb09)
would look like
SELECT customer_name, SUM(Jan09) AS jan, SUM(Feb09) as feb .....

then
$row['SUM(Jan09)'];
would look like
$row['jan'];

this means you can change the Jan09 toJan10 and not change the rest of the code....
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
Hi m walker, That is exaclty what i need is a total at the end of every month for the companies in total in a list.. At the end.. you hit the nail on the head.

Would you be able to show me how this would be done with my current table structure and queries?

It would be greatly greatly appreciated.

@ Mutter man, It was the jan box total, feb box total, Etc.. However if i did want to do it for say every 3 months would that be possible too?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 4

Expert Comment

by:m_walker
Comment Utility
This was a quick edit and not tested (so I hope not too many typos..)
Key changes
... Added the as <mon> to the sql for re-usable code
... Added the Month Total Variables
... Added the Month Total Add to the loop
... Added the Month total output after the loop

<?php
error_reporting(E_ALL);
include 'myphp.php';

  $query = "SELECT customer_name, SUM(Jan09) as jan, SUM(Feb09) as feb, SUM(Mar09) as mar,SUM(Apr09) as apr,SUM(May09) as may,SUM(Jun09) as jun,SUM(Jul09) as jul,SUM(Aug09) as aug,SUM(Sep09) as sep,SUM(Oct09) as oct,SUM(Nov09) as nov,SUM(Dec09) as dec FROM bill_detail2009 GROUP BY customer_name";
      
$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Company</th> <th>Jan09</th> <th>Feb09</th> <th>Mar09</th> <th>Apr09</th> <th>May09</th> <th>Jun09</th> <th>Jul09</th> <th>Aug09</th> <th>Sep09</th> <th>Oct09</th> <th>Nov09</th> <th>Dec09</th> </tr>";
// Print out result
// Init Month totals
$JanTotal = 0;
$FebTotal = 0;
$MarTotal = 0;
$AprTotal = 0;
$MayTotal = 0;
$JunTotal = 0;
$JulTotal = 0;
$AugTotal = 0;
$SepTotal = 0;
$OctTotal = 0;
$NovTotal = 0;
$DecTotal = 0;

while($row = mysql_fetch_array($result)){
      //echo "<tr><td>";
      //echo "Total ". $row['company']. " = £". $row['SUM(Jan09)'] ."    ".$row['SUM(Feb09)'];
      //echo "<br />";
      // Add totals
      $JanTotal += $row['jan'];
      $FebTotal += $row['feb'];
      $MarTotal += $row['mar'];
      $AprTotal += $row['apr'];
      $MayTotal += $row['may'];
      $JunTotal += $row['jun'];
      $JulTotal += $row['jul'];
      $AugTotal += $row['aug'];
      $SepTotal += $row['sep'];
      $OctTotal += $row['oct'];
      $NovTotal += $row['nov'];
      $DecTotal += $row['dec'];

      // Print out the contents of each row into a table
      echo "<tr><td>";
      echo $row['customer_name'];
      echo "</td><td>";
      echo "£".$row['jan'];
      echo "</td><td>";
      echo "£".$row['feb'];
      echo "</td><td>";
      echo "£".$row['mar'];
      echo "</td><td>";
      echo "£".$row['apr'];
      echo "</td><td>";
      echo "£".$row['may'];
      echo "</td><td>";
      echo "£".$row['jun'];
      echo "</td><td>";
      echo "£".$row['jul'];
      echo "</td><td>";
      echo "£".$row['aug'];
      echo "</td><td>";
      echo "£".$row['sep'];
      echo "</td><td>";
      echo "£".$row['oct'];
      echo "</td><td>";
      echo "£".$row['nov'];
      echo "</td><td>";
      echo "£".$row['dec'];
      echo "</td><td>";
            
      
}

// Add totals
      echo "<tr><td>sub total</td><td>";
      echo "£".$JanTotal;
      echo "</td><td>";
      echo "£".$FebTotal;
      echo "</td><td>";
      echo "£".$MarTotal;
      echo "</td><td>";
      echo "£".$AprTotal;
      echo "</td><td>";
      echo "£".$MayTotal;
      echo "</td><td>";
      echo "£".$JunTotal;
      echo "</td><td>";
      echo "£".$JulTotal;
      echo "</td><td>";
      echo "£".$AugTotal;
      echo "</td><td>";
      echo "£".$SepTotal;
      echo "</td><td>";
      echo "£".$OctTotal;
      echo "</td><td>";
      echo "£".$NovTotal;
      echo "</td><td>";
      echo "£".$DecTotal;
      echo "</td><td>";
      
echo "</table>";

   ?>
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
Good news sorted it myself i thank you both for your input couldnt of done it without you. i used the jan total = 0 thing and kept a running total and then made a new table to show the totals for each month.

i thank you both couldnt of done it without you.
0
 
LVL 16

Expert Comment

by:Chris Harte
Comment Utility
The totals you have for the months are numbers, they are called $row[], but they are still numbers

echo "Total ". $row['company'].
        " = £".
        $row['SUM(Jan09)'] .
        " +  ".
        $row['SUM(Feb09)'].
        " + ".
        $row['SUM(Mar09)'];
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
all i added was this basiclly what u did m walker.

echo "<table>";
      echo "<table border='1'>";
echo "<tr><th>Total</th> <th>Jan09</th> <th>Feb09</th> <th>Mar09</th> <th>Apr09</th> <th>May09</th> <th>Jun09</th> <th>Jul09</th> <th>Aug09</th> <th>Sep09</th> <th>Oct09</th> <th>Nov09</th> <th>Dec09</th> </tr>";
echo "<tr><td width=125>";
echo 'Total';
      echo "</td><td>";
      echo "£".$row['SUM(Jan09)'];
      $JanTotal = 0;
$JanTotal += $row['SUM(Jan09)'];
      echo "</td><td>";
0
 
LVL 4

Expert Comment

by:m_walker
Comment Utility
Thanks. Glad I could help
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now