With the help of the article:
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
I'm getting real close to being able to do some crosstab queries.
First, I run a query against an ITEMS table to get field designators and put the result into a Hash (hope this is not too confusing)
Example:
while ($Row = mysql_fetch_array($result3))
{
$xHASH .= "AVG(IF( DATA. ITEMID = $Row[ITEMID], SCORE, NULL)) AS $Row[ITEM], ";
}
This produces a string that is like this:
$xHASH = "AVG(IF(DATA.ITEMID = 336, SCORE, NULL)) AS EE_1_A,
AVG(IF(DATA.ITEMID = 337, SCORE, NULL)) AS EE_1_B,
AVG(IF(DATA.ITEMID = 338, SCORE, NULL)) AS EE_1_C,
AVG(IF(DATA.ITEMID = 339, SCORE, NULL)) AS EE_1_D,
AVG(IF(DATA.ITEMID = 341, SCORE, NULL)) AS EE_2_A,
AVG(IF(DATA.ITEMID = 342, SCORE, NULL)) AS EE_2_B,
AVG(IF(DATA.ITEMID = 343, SCORE, NULL)) AS EE_2_C,
AVG(IF(DATA.ITEMID = 345, SCORE, NULL)) AS EE_3_A,
AVG(IF(DATA.ITEMID = 346, SCORE, NULL)) AS EE_3_B,
AVG(IF(DATA.ITEMID = 347, SCORE, NULL)) AS EE_3_C, ";
This goes to a subsequent query, like so (Note where/how$xHASH gets added):
$xtab1Query = "SELECT PROTOCOLS.TESTPHASE, PROTOCOLS.SCHOOLID, PROTOCOLS.GRADE, PROTOCOLS.ROOM,
".$xHASH."
COUNT(PROTOCOLS.PROTOCOLID) AS PROTOCOLS
FROM DATA INNER JOIN PROTOCOLS ON DATA.PROTOCOLID = PROTOCOLS.PROTOCOLID
WHERE (((PROTOCOLS.FORMID)='$FORMID')) GROUP BY PROTOCOLS.TESTPHASE, PROTOCOLS.SCHOOLID, PROTOCOLS.GRADE, PROTOCOLS.ROOM";
I seem to have some sort of problem with my GROUP statement, however, because I get a PROTOCOL COUNT THAT IS 10 times what it should be. (Seems to be related to the number of ITEM columns...?)
Any quick/obvious hints as to what is going wrong here?
