fields are int.
i have table printing mysql db. and caluclating in the header.
i cannot get two db columns to SUM.
i need to make a new table at the bottom to calcualte ‘allocation’ (from all rows on db), and column ‘togoal'(with all rows) to SUM up everything.
here’s my code – first table works perfect. just need to caluclate those two columns and print it to a table at the bottom
[code=php]
<style type=”text/css”>
<!–
body {
background-color: #FFF;
}
–>
</style>
<link href=”tm.css” rel=”stylesheet” type=”text/css” />
<style type=”text/css”>
<!–
body,td,th {
color: #000;
}
–>
</style><div><div align=”center”>
<table id=”Table_01″ width=”1200″ height=”3200″ border=”0″ cellpadding=”0″ cellspacing=”0″>
<tr>
<td colspan=”3″><img src=”images/layout3_01.png” width=”1200″ height=”345″ alt=”” /></td>
</tr>
<tr>
<td><img src=”images/layout3_02.png” width=”221″ height=”2855″ alt=”” />
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p></td>
<td><div align=”center”>
<iframe frameborder=”0″ width=”685″ height=”205″ src=”http://wwwxxxxxp;boxtag=g640rj&sec=main” marginheight=”2″ marginwidth=”2″ scrolling=”Auto” allowtransparency=”yes” name=”cboxmain” style=”border:#DBE2ED 1px solid;” id=”cboxmain”></iframe>
<br />
<iframe frameborder=”0″ width=”685″ height=”75″ src=”http://wwwxxxxxp;boxtag=g640rj&sec=form” marginheight=”2″ marginwidth=”2″ scrolling=”No” allowtransparency=”yes” name=”cboxform” style=”border:#DBE2ED 1px solid;border-top:0px” id=”cboxform”></iframe>
</div>
<div align=”top” class=”tbody”>
<p>
<?
$hostname=’xxxx’; //// specify host, i.e. ‘localhost’
$user=’xx’; //// specify username
$pass=’xxx’; //// specify password
$dbase=’xxxx’; //// specify database name
$connection = mysql_connect(“$hostname” , “$user” , “$pass”)
or die (“Can’t connect to MySQL”);
$db = mysql_select_db($dbase , $connection) or die (“Can’t select database.”); // I’m still not sure what the allocation – total is, is total a field?
$sQry = “SELECT cid,tier,source,school,startdate,enddate,status,enrgoal,enrrate, allocation, (day1 + day2 + day3 + day4 + day5 + day6 + day7 + day8 + day9 + day10 + day11 + day12 + day13 +
day14 + day15 + day16 + day17 + day18 + day19 + day20 + day21 + day22 + day23 + day24 + day25 +
day26 + day27 + day28 + day29 + day30 + day31) AS generated, allocation -(day1 + day2 + day3 + day4 + day5 + day6 + day7 + day8 + day9 + day10 + day11 + day12 + day13 +
day14 + day15 + day16 + day17 + day18 + day19 + day20 + day21 + day22 + day23 + day24 + day25 +
day26 + day27 + day28 + day29 + day30 + day31) AS togoal
FROM schoolinfo
ORDER BY tier, cid”;
$rs = mysql_query($sQry) or die(‘Unable to execute query: ‘ . $sQry . ‘; ‘ . mysql_error());
//initialize $previousTier to something
$previousTier = “”;
//loop through the result set, assigning each new row to $row
while ( $row = mysql_fetch_assoc($rs) ) {
//if the tier of the current row is different than the tier we have already been on…
if ( $row[‘tier’] != $previousTier ) {
//if previousTier is not empty, close the previous table
if ( $previousTier != “” ) echo “</table>”;
//set $previousTier to the current tier
$previousTier = $row[‘tier’];
//echo the current tier, and print the opening tags for a new table.
echo “<h3>{$row[‘tier’]}</h3>”;
echo “<table border=1 cellpadding=0>”;
//print a header row
echo “<tr>”;
// Consider an unset($row[‘tier’]); at this point so it won’t show in results
//print the headers, using the KEYS of $row. Read the PHP manual page on arrays, and the manual page for array_keys
foreach ( array_keys($row) as $header ) {
echo “<th>” . ucwords($header) . “</th>”;
}
// These are no longer necessary
//we have two additional fields, total and allocation – total. Print the headers:
//echo “<th>Generated</th><th>Allocation – Total</th>”;
//close the header row
echo “</tr>”;
}
echo “<tr>”;
foreach ( $row as $field ) {
echo “<td>{$field}</td>”;
}
}
//close the last table.
echo “</table>”;
?>
</p>
</p>
<table width=”200″ height=”65″ border=”0″>
<tr>
<td height=”26″></td>
<td></td>
</tr>
<tr>
<td><?
$query = “SELECT SUM(allocation) as atotal, SUM(togoal) as gtotal
FROM schoolinfo”;
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row[‘allocation’]. ” – “. $row[‘togoal’];
?>
</td>
<td></td>
</tr>
</table></td>
<td><img src=”images/layout3_04.png” width=”226″ height=”2855″ alt=”” />
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p></td>
</tr> </table>