SUM and GROUP BY php/MySQL problem

BGamlin · January 12, 2011

Hi,

I'm very new to MySQL and could really do with some help!

I am creating a statistics system which should take information from 5 columns on a table and calculate their totals individually. This area of the code seems to be working. Although thousand formatting isn't working. So help with that too would be great!

My main problem is that I need the output to group by a field named 'type'. For some reason the code isn't picking up the data in the type column (Refer to screenshot for further information).

Also, here is the code I am using:

<?php

$inclTerror = 0;

$liability = 0;

//$liability2 = 0;

$legal = 0;

$result = mysql_query("

SELECT SUM(mdInclTerror) as incl_Terror, SUM(legalExpenses) as legal_exp, SUM(el + pl + pol ) as liability

FROM tblMaster

WHERE type='HHC' AND insurer='F3'

GROUP BY type");

if ($result && mysql_num_rows($result) > 0) {

$query_data=mysql_fetch_array($result);

$inclTerror= (float) $query_data["incl_Terror"];

$legal= (float) $query_data["legal_exp"];

$liability= (float) $query_data["liability"];

}

echo "<table border='1' align='center' cellpadding='10px'>";

echo "<tr>";

echo "<td align='center'>Type</td>";

echo "<td align='center'>100% MD Including</td>";

echo "<td align='center'>100% Liability</td>";

echo "<td align='center'>100% Legal Expenses</td>";

echo "</tr>";

echo "<tr>";

echo "<td align='right'>".$insType."</td>";

echo "<td align='center'>£".$inclTerror."</td>";

echo "<td align='center'>£".$liability."</td>";

echo "<td align='center'>£".$legal."</td>";

echo "</tr>";

echo "</table>";

?>

I cannot see a problem and everyone I have referred the question too can't see a problem either. Hope someone can help me!

Thanks In Advance,

Ben

[attachment deleted by admin]

Maq · January 12, 2011

I see a few issues.

- You don't select 'type' in your query.

- $insType is never defined.

(Please use

tags)

mikosiko · January 12, 2011

and where are you defining $insType ?

Edit: Maq beat me

Maq · January 12, 2011

and where are you defining $insType ?

Edit: Maq beat me

Another thing I noticed, in your query you have WHERE type='HHC', so why not just hard code the type?

BGamlin · January 12, 2011

Hi, Thanks for your help. I have edited this file so many times I must have forgot to change some things back. Have made the changes you said but still nothing.

Here is the updated Code.....

<?php

$inclTerror = 0;
$liability = 0;
//$liability2 = 0;
$legal = 0;

$result = mysql_query("
SELECT SUM(mdInclTerror) as incl_Terror, SUM(legalExpenses) as legal_exp, SUM(el + pl + pol ) as liability, type
FROM tblMaster
WHERE insurer='F3'
GROUP BY type");

if ($result && mysql_num_rows($result) > 0) {
  		$query_data=mysql_fetch_array($result);
  		$inclTerror= (float) $query_data["incl_Terror"];
	$legal= (float) $query_data["legal_exp"];
	$liability= (float) $query_data["liability"];
}

echo "<table align='center' cellpadding='10px'>";
echo "<tr>";
echo "<td align='center'>Type</td>";
echo "<td align='center'>100% MD Including</td>";
echo "<td align='center'>100% Liability</td>";
echo "<td align='center'>100% Legal Expenses</td>";
echo "</tr>";
echo "<tr>";
echo "<td align='right'>".$type."</td>";
echo "<td align='center'>£".$inclTerror."</td>";
echo "<td align='center'>£".$liability."</td>";
echo "<td align='center'>£".$legal."</td>";
echo "</tr>";
echo "</table>";

?>

Sorry about lack of code tags first time around. I wasn't aware of there being any...

Maq · January 12, 2011

'$type' still isn't being defined.

You need to add something like:

$type=  $query_data["type"];

BGamlin · January 12, 2011

Ok, I didn't realise you needed to do that! I only started learning php about 4 days ago...

That has got one of the types but still isn't grouping. I have attached an updated screenshot of the outcome. It sums everything but doesn't group on the type...

[attachment deleted by admin]

mikosiko · January 12, 2011

in your code you are getting only the first record... here:

	if ($result && mysql_num_rows($result) > 0) {
  		$query_data=mysql_fetch_array($result);
  		$inclTerror= (float) $query_data["incl_Terror"];
	$legal= (float) $query_data["legal_exp"];
	$liability= (float) $query_data["liability"];
}

you need:

1) Select the type field in your select

2) Loop the resultset with a WHILE and display your table in the loop

BGamlin · January 12, 2011

I presume it is only getting data from one record because of the >0?????

The loop bit I don't understand i'm afraid, could you give me an example that I could adapt please?

mikosiko · January 12, 2011

I presume it is only getting data from one record because of the >0?????

no.... here is a quick example (shooting from my hip) ... untested... but you will get the idea:

    $sql = "SELECT type, 
                   SUM(mdInclTerror) as incl_Terror, 
                   SUM(legalExpenses) as legal_exp, 
                   SUM(el + pl + pol ) as liability
      FROM tblMaster
      WHERE insurer='F3'
      GROUP BY type";
               
   	$result = mysql_query($sql) or die("Query Error: " . mysql_error());

if ($result && mysql_num_rows($result) > 0) {
  
  // Start your table
   echo "<table align='center' cellpadding='10px'>";
   echo "<tr>";
   echo "<td align='center'>Type</td>";
   echo "<td align='center'>100% MD Including</td>";
   echo "<td align='center'>100% Liability</td>";
   echo "<td align='center'>100% Legal Expenses</td>";
   echo "</tr>";

	while($row = mysql_fetch_assoc($result)){

  		      $inclTerror= (float) $row["incl_Terror"];
	      $legal= (float) $row["legal_exp"];
	      $liability= (float) $row["liability"];

   	              echo "<tr><td align='right'>".$row['type']."</td>";
              echo "<td align='center'>£".$inclTerror."</td>";
              echo "<td align='center'>£".$liability."</td>";
              echo "<td align='center'>£".$legal."</td></tr>";

        }    
  echo "</table>";

BGamlin · January 12, 2011

Excellent, thanks that has done the job!

There seems to be an error on this line:

if ($result && mysql_num_rows($result) > 0) {

But I don't think it is needed as those fields will be constantly populated when the page opens

Thanks very much for your help! Do you know how to set the thousand marker on numbers? as the fields output are monetary values they could really do with proper formatting

Maq · January 12, 2011

What's the error?

BGamlin · January 12, 2011

It doesn't show an error. It just shows a red marker over the line in Dreamweaver.

When the page loads it just comes up with a Server Error message saying the server could be down or moved to a different location

Maq · January 12, 2011

It doesn't show an error. It just shows a red marker over the line in Dreamweaver.

When the page loads it just comes up with a Server Error message saying the server could be down or moved to a different location

I'm not familiar with DW. This doesn't seem like a PHP error but just to be sure add these 2 lines directly after your opening <?php tag:

ini_set ("display_errors", "1");
error_reporting(E_ALL);

BGamlin · January 12, 2011

Ok, I have added those two lines. But the error it displayed earlier is still the same saying the server may be down for maint etc etc

Maq · January 12, 2011

Ok, I have added those two lines. But the error it displayed earlier is still the same saying the server may be down for maint etc etc

Hmm, I'm not sure. Try running something that you know works.

fenway · January 12, 2011

Ok, I have added those two lines. But the error it displayed earlier is still the same saying the server may be down for maint etc etc

This has nothing to do with mysql.

BGamlin · January 12, 2011

Ok, well the code seems to all be working. PLUS i've sorted out the number formatting. I didn't realise it was that simple...

I have stumbled across another problem though :S. Is it possible to calculate off the calculated fields that you helped me with earlier? I thought it would be simply like was done before where you put it in the SELECT statement. but as it's not in the table it won't work like that it would seem

any ideas?

Thanks

mikosiko · January 12, 2011

:confused:

Is it possible to calculate off the calculated fields that you helped me with earlier? I thought it would be simply like was done before where you put it in the SELECT statement. but as it's not in the table it won't work like that it would seem

seems like a translation/better explanation is in-order here ... maybe you should rephrase/explain it

Maq · January 12, 2011

Is it possible to calculate off the calculated fields that you helped me with earlier? I thought it would be simply like was done before where you put it in the SELECT statement. but as it's not in the table it won't work like that it would seem

seems like a translation/better explanation is in-order here ... maybe you should rephrase/explain it

Yeah, I'm not sure exactly what you want here. Please elaborate.

BGamlin · January 12, 2011

sorry about that, I'll try to elaborate.

Basically I wanted to have a total at the bottom of the table shown earlier (I have reattached to this post). This should give a total for, for instance, 100% Legal for all type categories.

I thought the code should be something like this:

$sql = "(SELECT type, 
            SUM(mdInclTerror) as incl_Terror,
            SUM(legalExpenses) as legal_exp,
            SUM(el + pl + pol) as liability,
		SUM(mdInclTerror) as totalInclTerror
        FROM tblMaster
        WHERE insurer='F3'
        GROUP BY type)";
               
   	$result = mysql_query($sql) or die("Query Error: " . mysql_error());

//if ($result && mysql_num_rows($result) > 0) {
  
  // Start your table
   echo "<table align='center' cellpadding='10px'>";
   echo "<tr>";
   echo "<td align='center'><b></b></td>";
   echo "<td align='center'><b>100% MD Including Terrorism</b></td>";
   echo "<td align='center'><b>100% Liability</b></td>";
   echo "<td align='center'><b>100% Legal Expenses</b></td>";
   echo "</tr>";

	while($row = mysql_fetch_assoc($result))
	{
  		      $inclTerror= (float) $row["incl_Terror"];
	      $legal= (float) $row["legal_exp"];
	      $liability= (float) $row["liability"];
		  $totalInclTerror= (float) $row["totalInclTerror"];
		  
   	          echo "<tr><td align='right'><b>".$row['type']."</b></td>";
          echo "<td align='center'>£".number_format($inclTerror,2)."</td>";
          echo "<td align='center'>£".number_format($liability,2)."</td>";
          echo "<td align='center'>£".number_format($legal,2)."</td></tr>";
		  
		  echo "<tr><td></td>";
		  echo "<td align='center'>£".number_format($totalInclTerror,2)."</td>";
		  echo "<td align='center'>£</td>";
		  echo "<td align='center'>£</td>";
		  echo "</tr>";
	}

But this shows a column for each type of business so it's effectively just showing me the total for each type. I have attached a 2nd screenshot of this outcome.

Is there any way to have a total at the end of the table to calculate everything for the column under all types?

[attachment deleted by admin]

mikosiko · January 13, 2011

first... don't comment the IF clause that is necessary to control in case you query doesn't return values.

regarding the totals.... sure it is possible... just define total variables for each column ... accumulate the desire columns in those variables inside the while loop and print them at the end of the loop before to close the table

Sign In

SUM and GROUP BY php/MySQL problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information