[thelist] PHP & Crosstabs [long] MORE CLEARLY MAYBE...
Jay Blanchard
jay.blanchard at niicommunications.com
Tue May 7 13:35:20 CDT 2002
[snip]
the second query is created on the fly, because I may not know all of the
RecordID's;
//query for crosstab
$row_num = 0;
$qx = "SELECT RecordDate, ";
while($rowx = mysql_fetch_object($dbrid)){
$row_num++;
if($row_num <> mysql_num_rows($dbrid)){
$qx .= "SUM(IF(RecordID = '$rowx->RecordID', 1, 0)) AS r$rowx->RecordID,
";
}
else {
$qx .= "SUM(IF(RecordID = '$rowx->RecordID', 1, 0)) AS r$rowx->RecordID ";
}
}
$qx .= "FROM tblCrosstab ";
$qx .= "GROUP BY RecordDate ";
$dbx = mysql_query($qx, $dbconnect)
The query returned by the code above is;
SELECT RecordDate
SUM(IF(RecordID = '100101', 1, 0)) AS r100101,
SUM(IF(RecordID = '100118', 1, 0)) AS r100118,
SUM(IF(RecordID = '100119', 1, 0)) AS r100119,
SUM(IF(RecordID = '100120', 1, 0)) AS r100120
FROM tblCrosstab
GROUP BY RecordDate
...so far, so good. Here is the problem...I need to create a table row for
each RecordDate. The table should come out looking like this; (following is
poor example of HTML table)
+------------+--------+--------+--------+--------+
| | 100101 | 100118 | 100119 | 100120 |
+------------+--------+--------+--------+--------+
| 2002-03-01 | 12 | 18 327 | 2 |
+------------+--------+--------+--------+--------+
| 2002-03-02 | 8 | 18 245 | 2 |
+------------+--------+--------+--------+--------+
| 2002-03-03 | 16 | 18 244 | 2 |
+------------+--------+--------+--------+--------+
| 2002-03-04 | 11 | 24 912 | 2 |
+------------+--------+--------+--------+--------+
Of course the query does exactly this in DB, I just have to do it with PHP
and HTML where I do not know how many rows (RecordDates) there might be. I
solved not knowing how many RecordID's, just not sure at this bit? This one
is sure to stretch some imaginations.
[/snip]
The above query returns the number of columns needed in each row with a name
for each column. If I knew the number of columns (and their names) I would
write (hard code) something like;
<?
while($dbrow = mysql_fetch_object($dbx)){
print("<tr>\n");
print("<td>");
print($dbrow->RecordDate);
print("</td><td align=\"right\">");
print($dbrow->r100101);
print("</td><td align=\"right\">");
print($dbrow->r100118);
print("</td><td align=\"right\">");
print($dbrow->r100119);
print("</td><td align=\"right\">");
print($dbrow->r100120);
print("</td>\n");
print("</tr>\n");
}
?>
Any thoughts?
TIA!
Jay
More information about the thelist
mailing list