[thelist] PHP & Crosstabs [long]

[Jay Blanchard]

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