BGamlin Posted January 12, 2011 Share Posted 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] Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/ Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted January 12, 2011 I see a few issues. - You don't select 'type' in your query. - $insType is never defined. (Please use tags) Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158414 Share on other sites More sharing options...
mikosiko Posted January 12, 2011 Share Posted January 12, 2011 and where are you defining $insType ? Edit: Maq beat me Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158415 Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158418 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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... Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158422 Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted January 12, 2011 '$type' still isn't being defined. You need to add something like: $type= $query_data["type"]; Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158425 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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] Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158428 Share on other sites More sharing options...
mikosiko Posted January 12, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158431 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158433 Share on other sites More sharing options...
mikosiko Posted January 12, 2011 Share Posted 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>"; Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158441 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158452 Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted January 12, 2011 What's the error? Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158455 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158457 Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted 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); Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158459 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158463 Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158465 Share on other sites More sharing options...
fenway Posted January 12, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158483 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158519 Share on other sites More sharing options...
mikosiko Posted January 12, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158527 Share on other sites More sharing options...
Maq Posted January 12, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158529 Share on other sites More sharing options...
BGamlin Posted January 12, 2011 Author Share Posted 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] Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158601 Share on other sites More sharing options...
mikosiko Posted January 13, 2011 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/224192-sum-and-group-by-phpmysql-problem/#findComment-1158657 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.