going through each record
Posted: Mon Jul 12, 2010 2:31 pm
I have finally solved my pivot table issue and now find myself burnt out and out of ideas. Maybe someone here could help.
I have written the following code:
This in turn generates the following SQL:
I run this in SQL and get exactly what I want:

But how would I now get this to display each record, one field at a time?
I want to take the First Name and Last Name (easy, know this part because I know the field names at development) to appear in the left most cell in a table.
I then want each of the summed values to appear in the relevant cell; given the table header as created at the top of the first code. Yet I will not know these until the SQL has been executed since it depends upon the data in the table.
Anyone able or willing to help?
I have written the following code:
Code: Select all
$getDetails = $connector->query('SELECT action FROM '.$settings['tbl_actions'].' GROUP BY action');
$txtDetails = 'SELECT txtFirstName, txtSurname, txtSchool';
while ($allDetails = $connector->fetchArray($getDetails))
{
if ($allDetails['action'] != "")
{
$txtDetails = $txtDetails.', SUM(IF(action = "'.$allDetails['action'].'" ,1 ,0 )) AS "'.$allDetails['action'].'"';
echo '<th>'.$allDetails['action'].'</th>';
}
}
echo '</tr><tr>';
$txtDetails = $txtDetails.' FROM '.$settings['tbl_actions'].', '.$settings['tbl_users'].' WHERE '.$settings['tbl_users'].'.ID = '.$settings['tbl_actions'].'.user_id GROUP BY txtSurname';
Code: Select all
SELECT txtFirstName, txtSurname, txtSchool, SUM(IF(action = "Add User" ,1 ,0 )) AS "Add User", SUM(IF(action = "Germany 1919-1941" ,1 ,0 )) AS "Germany 1919-1941", SUM(IF(action = "Home Page" ,1 ,0 )) AS "Home Page", SUM(IF(action = "Logged In" ,1 ,0 )) AS "Logged In", SUM(IF(action = "The Control Room" ,1 ,0 )) AS "The Control Room", SUM(IF(action = "View All Users" ,1 ,0 )) AS "View All Users", SUM(IF(action = "View User" ,1 ,0 )) AS "View User", SUM(IF(action = "View User Details" ,1 ,0 )) AS "View User Details" FROM tbl2, tbl1 WHERE tbl1.ID = tbl2.user_id GROUP BY txtSurname

But how would I now get this to display each record, one field at a time?
I want to take the First Name and Last Name (easy, know this part because I know the field names at development) to appear in the left most cell in a table.
I then want each of the summed values to appear in the relevant cell; given the table header as created at the top of the first code. Yet I will not know these until the SQL has been executed since it depends upon the data in the table.
Anyone able or willing to help?