?
Solved

Sub Total, From Dynamic PHP table total

Posted on 2011-03-22
9
Medium Priority
?
458 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 400 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 1600 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

718 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