[thelist] PHP & Crosstabs [long]
Jay Blanchard
jay.blanchard at niicommunications.com
Tue May 7 12:34:01 CDT 2002
I have the following code in page to query a 12,000,000 record DB for a
crosstab;
//query for RecordID list
$qrid = "SELECT DISTINCT RecordID ";
$qrid .= "FROM tblCrosstab ";
$qrid .= "ORDER BY RecordID ";
$dbrid = mysql_query($qrid, $dbconnect)
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.
I do not want to do a third query (hell, I'd prefer to do only one, but I
can't for the life of me figure out a way to do it and get the crosstab too)
for RecordDates.
Thanks!
Jay
More information about the thelist
mailing list