[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