Cross tabulation / pivot table in php

[davesmith437]

Hi All, here's the query....

$risk_section=$row_risk_category['risksectid'];
        mysql_select_db($database_auditing, $auditing);
   $qry_selfsites = sprintf("SELECT
   tblself.siteid AS selfsite, 
   tblsite.sitename AS sitename,
    tblsite.address AS address, 
     tblpct.pctname AS pctname,
      tblresultsnew.total,
       tblresultsnew.auditid AS auditid,
        tblriskcategories.risksectid AS risksectid,
         tblriskcategories.risksection AS risksection
   FROM tblself 
     LEFT JOIN tblresultsnew ON tblself.auditid = tblresultsnew.auditid 
      LEFT JOIN tblsite ON tblself.siteid = tblsite.siteid 
       LEFT JOIN tblpct ON tblsite.pctid = tblpct.pctid
        LEFT JOIN tblriskcategories ON tblresultsnew.risksectid=tblriskcategories.risksectid 
   WHERE tblsite.pctid IN (SELECT pctid FROM tblreportpcts WHERE pctreportid='$pctreportid') 
     AND tblsite.sitetypeid IN (SELECT sitetypeid FROM tblreportsites WHERE pctreportid='$pctreportid')
      AND tblself.year = %s 
       ORDER BY tblsite.pctid,tblsite.sitename", 
     GetSQLValueString($yearofrpt, "int"));
   $selfsites = mysql_query($qry_selfsites, $auditing) or die(mysql_error());
    $totalRows_selfsites = mysql_num_rows($selfsites);

What I'm trying to do is populate the column headers dynamically from the query tblriskcategories.risksection AS risksection.

Then underneath, populate the rest of the table with the rows showing the sitename and the corresponding column's results - the table below is an idea of what I'm trying to get only it doesn't work the way I've set it out and I don't know how to get it to go

<table>
<tr>
<th>Sitename</th>
<?php while($row_selfsites=mysql_fetch_assoc($selfsites){  
 //loop through the section headers pulled from the DB (tblriskcategories)  
<th><?php echo $row_selfsites['risksection'];//show the section headers?></th>
<?php }   
//end header loop then start another loop to show a row for each site pulled
 //out by the query and show the relevant results in the correct column  
while($row_selfsites=mysql_fetch_assoc($selfsites)) {  
 //do the vertical drop matching the header rows with the sitenames from tblsite 
 //and the results from tblresultsnew 
?>  
<tr>  
<td><?php echo $row_selfsites['sitename'];?></td>  
<td><?php echo $row_selfsites['total'];  
//these need to grow to fit the headers and each site?></td>  
<tr>  
<?php } //end displayed data loop?>  
</table>  

The relevant tables from which all the data comes are structured as below:
tblresultsnew resultsid,auditid,risksectid,total
tblriskcategories risksectid, risksection
tblself selfauditid,siteid,auditid
tblsite siteid,sitename

So tblself holds the list of sites we need the data for and the relevant auditid,
tblresultsnew holds the results - the total column - for each risksectid and each auditid eg, one auditid can have approx 8 risksectid's each with corresponding total
tblriskcategories holds the column headings
tblsite holds the site data to make it mean something

I think I need to turn the query into an array and then loop through the relevant bits to build the table but can't get it to work.

Any ideas greatfully received.

Many thanks.
Dave

[pritaeas]

Can you show a sample of the data the query is returning? Perhaps that will clear it up.

[davesmith437]

Thanks for the reply,

The data should appear something like

     Site Name | Section A Result | Section B Result | Section C Result |
      Site A   |       15         |        9         |         12       |
      Site B   |       11         |       13         |          8       |
      Site C   |       12         |        1         |         19       |

At the moment all I can get the query to do is return data like this

Site A   |  15 | Section A
Site A   |  9  | Section B
Site A   |  12 | Section C
Site B   |  11 | Section A
Site B   |  13 | Section B
Site B   |   8 | Section C
Site C   |  12 | Section A
Site C   |   1 | Section B
Site C   |  19 | Section C

So as you can see I'm trying to get this to display in a much more readable manner (there could be up to 500 sites to show the results for so doing 7 - 10 rows each just isn't reasonable!!) with the sections as the column headers and the rows showing the corresponding results.

The data is purely a score which is linked to the section id and the site name

Thanks Again

[pritaeas]

Are there always three sections, or a fixed number, or is that variable?

[davesmith437]

No the sections are not fixed, they are a variable (could be anywhere between 3 and 10 column headers and likely to change over time).

The headers are stored in their own table - tblriskcategories

[pritaeas]

Well, if you loop your results then you should start a new tr when the site name changes. If it is the same, you can add the values to an additional td in the same row.