Solved

Sub Total, From Dynamic PHP table total

Posted on 2011-03-22
9
457 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
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Author Comment

by:NeoAshura
ID: 35188655
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 17

Assisted Solution

by:Chris Harte
Chris Harte earned 100 total points
ID: 35188936
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
ID: 35188948
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Comment

by:NeoAshura
ID: 35189057
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
 
LVL 4

Expert Comment

by:m_walker
ID: 35189176
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
ID: 35189194
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 17

Expert Comment

by:Chris Harte
ID: 35189207
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
ID: 35189234
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
ID: 35189288
Thanks. Glad I could help
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

617 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