Page 1 of 1

going through each record

Posted: Mon Jul 12, 2010 2:31 pm
by gazzieh
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:

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';
This in turn generates the following SQL:

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
I run this in SQL and get exactly what I want:

Image

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?

Re: going through each record

Posted: Mon Jul 12, 2010 3:19 pm
by websitesca
I would probably split this into 2 different queries. One for the sums and the other for all your rows. I'm not quite sure I understand your problem, but I think that is what you want for your website. You could also design the code such that it sums all the stuff in your loop (the loop that prints all the rows).

Hope that helps!
Georges,
http://www.websites.ca

Re: going through each record

Posted: Mon Jul 12, 2010 3:38 pm
by gazzieh
Yep, I can see how I have created confusion.

Basically I want my web page to create a table similar in structure to the one I show on the image above.

All the information already exists and I already have the top row of the table (with all the column headings in place).

The issue is that my second query contains all the numerical sums that I need to display but to show this I would need to do something like:

echo '<td>'.$txtDetails['FIELD NAME'].'</td>';

and have this replicated for each of the potential field headings given in the row header. However, I cannot hardwire these in code since I will not know what the row headers will be until the query is run.

I assume I am going to need to re-run the query I have used to create the row headings and put this in place of the string reference but then I begin to get my head in a spin and worry I am running a complex query wrapped in a simple SELECT query; making the process seem far more complex than it may need to be.

My head hurts! :)