[thelist] PHP & Crosstabs [long]

Jay Blanchard jay.blanchard at niicommunications.com
Tue May 7 13:52:24 CDT 2002


[snip]
> 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.
missing a comma after RecordDate, but don't stick it in just yet...

you have a messy php if statement there, which appears to check to see
whether the record is the last in the series, and if so, don't include the
trailing comma

well, you can simplify the code and avoid the if statement if you simply
put the comma in front of each term -- and don't put one after RecordDate!
[/snip]

Excellent idea rudy, I will make that change (it really wasn't missing, just
a typo from yours truly). Messy php indeed!

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");
	}
?>

But I don't. So I need to find a way to say, "hey, we got this many columns
this time, so write out that many <td> sets to correspond."

That's where I am stuck. One thing is for sure, I will know a lot more about
crosstabs in MySQL, loops in PHP, and my own personal character when I get
through this project. :)

Thanks!

Jay

Jay





More information about the thelist mailing list