Posted by larry on 11/22/06 04:00

Here is a no-brainer table creation I just worked up - you specify the
columns in your MySQL query and the PHP script takes it from there
(creates the heading row, displays the data, then sums the rows.
(dbconnect and dbquery are just functions that do the normal mysql
connect and query). I left in my query as it is also a good example of
grouping)

- build your query in the preferred column output order of your table,
use the AS option to make the header names, '_ 's are replaced with <BR
/> tags in the heading row.
- caution - make sure you have safe data, it does utilize "eval"

Larry

<?php
dbconnect();

$reptdate = 20061012;

$result = dbquery("SELECT
`facility_loccity` as `city`,
SUM(IF(`facility_type`='sfcch',1,0)) AS `small_homes`,
SUM(IF(`facility_type`='sfcch',`facility_fcchcap`,0)) AS `sm_home_cap`,
SUM(IF(`facility_type`='lfcch',1,0)) AS `large_homes`,
SUM(IF(`facility_type`='lfcch',`facility_fcchcap`,0)) AS `lg_home_cap`,
SUM(IF((`facility_type`='lcent' OR facility_type='excent'),1,0)) AS
`centers`,
SUM(`facility_infcap`) AS `infant_cap`,
SUM(`facility_precap`) AS `presch_cap`,
SUM(`facility_sacap`) AS `schage_cap`,
SUM(`facility_infptvac`) AS `inf_pt_vac`,
SUM(`facility_infftvac`) AS `inf_ft_vac`,
SUM(`facility_preptvac`) AS `presch_pt_vac`,
SUM(`facility_preftvac`) AS `presch_ft_vac`,
SUM(`facility_saptvac`) AS `schage_pt_vac`,
SUM(`facility_saftvac`) AS `schage_ft_vac`,
SUM(`facility_infptenrol`) AS `inf_pt_enrol`,
SUM(`facility_infftenrol`) AS `inf_ft_enrol`,
SUM(`facility_preptenrol`) AS `pre_pt_enrol`,
SUM(`facility_preftenrol`) AS `pre_ft_enrol`,
SUM(`facility_saptenrol`) AS `schage_pt_enrol`,
SUM(`facility_saftenrol`) AS `schage_ft_enrol`
FROM facility
WHERE `facility_begin` <= $reptdate
AND `facility_end` >= $reptdate
AND `facility_type` != 'exhome'
GROUP BY UPPER(`facility_loccity`)
ORDER BY UPPER(`facility_loccity`)");

$first = true;

$line1 = $line3 = 'echo "<TR align=\"center\">';

echo '<TABLE align="center" border="1"><TR align="center">';

while( $item = mysql_fetch_array($result) ){

if($first){
foreach( $item as $name => $value) {
if(!is_numeric($name)){
echo "<TD>".strtoupper(str_replace("_","<br />",$name))."</TD>";
$line1 .= '<TD>".$item[\''.$name.'\']."</TD>';
$line2 .= '$t'.$name.' += $item[\''.$name.'\'];';
$line3 .= '<TD>".$t'.$name.'."</TD>';
}
}
$line1 .= '</TR>\r";';
$line3 .= '</TR>\r";';
echo "</TR>\r";

}

$first = false;
eval($line1);
eval($line2);
}
eval($line3);
echo '</TABLE>';
?>

[Back to original message]