Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Sub Total, From Dynamic PHP table total

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
NeoAshura
Asked:
NeoAshura
  • 4
  • 3
  • 2
2 Solutions
 
NeoAshuraAuthor Commented:
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
 
Chris HarteThaumaturgeCommented:
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
 
m_walkerCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NeoAshuraAuthor Commented:
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
 
m_walkerCommented:
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
 
NeoAshuraAuthor Commented:
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
 
Chris HarteThaumaturgeCommented:
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
 
NeoAshuraAuthor Commented:
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
 
m_walkerCommented:
Thanks. Glad I could help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now