Solved

Sub Total, From Dynamic PHP table total

Posted on 2011-03-22
9
453 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP populating an array. 4 25
paypal ipn url 5 58
How can I implement a "Select All" with this configuration...? 6 42
website maintenance mode 1 17
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.
This article discusses how to create an extensible mechanism for linked drop downs.
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 dynamically set the form action using jQuery.

809 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