Crosstabs & PHP [long]
| From: | Jay Blanchard | Date: | Tue, 07 May 2002 17:23:52 +0000 |
| Subject: | Crosstabs & PHP [long] | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-96464@lists.php.net to get a copy of this message | ||
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)
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 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.
Thanks!
Jay