Nested query

Maybe.
Also I saw no mention of userAttended table earlier in this thread, so I can now see where you are storing component learned.

This one?

the fire safety component is stored in this one:

IN post#3 above you use userAttended.[componentTitle] so I assume you have a userAttended table… right?

If so what fields does it have? id, user_id, componentTitle???

Yes you are correct.

in post #3 is my attempt to join the two queries that I succesfully retrieved the data that I am after while using Microst Access. Which failed in the script that I have written here.

the name of the user is $userid. I pickup the user id from the table through $_SESSION

the exercise is three folds:

first: (and first page)

they completed the report. Once this report has been completed I store the ID of this [ tbl firesafety]record in $_session and tag it to the second step as a hidden input tag.

second step:(redirected to another page for attendances)
I get the user to tic all the users who attended the fire drill and then when they save the form, the script pickup the ID of the firesafety record at the same time from the hidden input tag and save into [tbl fsattendances]

last step (redirected to last page fire safety components)
once again I hide the firesafety id in a hidden tag and ask the user to tic which component that was instructed during the drill and save to [tbl fstraining]

I have no choice to use this method given the audience I deal with. I tried various ways in the past and this is the only one that seems to work with everyone. I am trying to transfer this application that was built in microsft Access into web format

here is the script if it can help?

       <section id="CompletedComponents" class="sectionLeft">
        <h3 class="pow">Completed Fire Safety Training Components</h3>
        	<?php 
    		$userid = $user->data()->id;
    		$rs = DB::getInstance()->get('fsattendances LEFT JOIN firesafety ON fsattendances.fireSafetyId = firesafety.id LEFT JOIN fstraining LEFT JOIN fscomponents ON fstraining.FSComponentId = fscomponents.id ON firesafety.id = fstraining.fireSafetyId', array( 'fsattendances.attendee', '=', $userid));
    		if($rs->count()){
    			echo '<table>';
    		
    				echo '<th class="th">Fire Safety Component</th><th class="th">Completed On</th>';
    					echo '<tr>';
    					  foreach($rs->results() as $list){
    						  echo	'<td class="tdLeft"> <a  href="firesafety.php?id='.escape($list->id).'">'.escape($list->componentTitle).'</a></td>';
    						  echo	'<td class="tdLeft">'.date ("F d, Y",strtotime($list->FSDate)).'</td>';
    					echo '</tr>';	
    						}	
    		}
    		else
    		  {echo 'You have not completed any Fire Safety Component Training';}
    		echo'</table>';
    		?>
        </section>
      
      
          <section class="sectionLeft">
        <h3 class="pow">Fire Safety Training Components that have NOT been completed yet</h3>
        	<?php 
    		$userid = $user->data()->id;
// this is the one that doesn't work/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    		$rs = DB::getInstance()->query(" 
    			SELECT fsattendances.attendee, fscomponents.componentTitle
    FROM fsattendances LEFT JOIN (fstraining LEFT JOIN fscomponents ON fstraining.FSComponentId=fscomponents.ID) ON fsattendances.fireSafetyId=fstraining.fireSafetyId
    WHERE (((fsattendances.attendee)!='$userid')");
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////    
    		
    
    		if($rs->count()){
    			echo '<table>';
    		
    				echo '<th class="th">Fire Safety Component</th>';
    					echo '<tr>';
    					  foreach($rs->results() as $list){
    						
    						  echo	'<td class="tdLeft"> <a  href="firesafety.php?id='.escape($list->id).'">'.escape($list->componentTitle).'</a></td>';
    						  Session::flash('home', '<<<----Click Fire Safety menu link to view all Fire Safety Components----->>>.');
    					echo '</tr>';	
    		
    					  }	
    		}else{echo 'There is no existing Fire Training Component at the monent';}
    		echo'</table>';
    		?>
        </section>

So, for a given UserID, Find the status of all components.
Assuming that someone who attended a firesafety attended ALL components covered by that firesafety:

SELECT fscomponents.componentName, a.attendee FROM (SELECT fscomponents.componentName,fsattendances.attendee FROM fscomponents LEFT JOIN fstraining ON fscomponents.id = fstraining.componentId LEFT JOIN fsattendances ON fstraining.fireSafetyId = fsattendances.fireSafetyId WHERE fsattendances.attendee = 1) AS a RIGHT JOIN fscomponents ON a.componentName = fscomponents.componentName

(Only partially tested)
Should return a list of all component names, and either NULL or the userid. If NULL, the user has not attended the component yet.

1 Like

Had to change componentName to componentTitle to work with fields I have but results were as expected. Well done! :wink:

I’m not sure about that unless when you say “All component attended” you mean all components that was related to the particular fire safety drill was attended? Otherwise we cannot assume that someone will attend every single fire safety Drill in the future.

I tried the new sql and unfortunately I am not getting the result that I am looking for; basically returning attended components.

What I am looking for is to show a list of components that exists and have still not been attended by the user.

how did you get that to work Drummin? have you built tables with data to see the results?

Try with this data. at the end your result would need to be ->>>>component 5 title<<<<–

tbl firesafety
ID SFDate
1 2015-06-01
2 2015-06-02
3 2015-06-03
4 2015-06-04
5 2015-06-05

tbl fsattendances
ID attendee fireSafetyId
1 1 1
2 1 2
3 1 3

tbl fscomponents
ID componentTitle
1 component 1 title
2 component 2 title
3 component 3 title
4 component 4 title
5 component 5 title

tbl fstraining
ID fireSafetyId FSComponentId
1 1 1
2 1 2
3 2 3
4 3 4
5 4 1
6 4 2
7 5 5

Component1
1
Component2
1
Component3
1
Component4
1
Component5
NULL

Which is the correct result.

Yes Jaady, I see what you mean but you should be able to use the null id in the result set to display any component that this user has not attended.

I have one question:

the last part of the query you show:

AS a RIGHT JOIN fscomponents ON a.componentTitle = fscomponents.componentTitle");

Where do you get the " a.componentTitle " ? I do not have anything relating to fire safety component in my attendance table? I only have 3 fields: id, fireSafetyId, attendee

copy of the print_r()

DB Object
(
[_pdo:DB:private] => PDO Object
(
)

[_query:DB:private] => PDOStatement Object
    (
        [queryString] =>  
		SELECT fscomponents.componentTitle, a.attendee FROM (SELECT fscomponents.componentTitle,fsattendances.attendee 
		FROM fscomponents 
		LEFT JOIN fstraining 
		ON fscomponents.id = fstraining.componentId 
		LEFT JOIN fsattendances 
		ON fstraining.fireSafetyId = fsattendances.fireSafetyId 
		WHERE fsattendances.attendee = 1) AS a RIGHT JOIN fscomponents ON a.componentTitle = fscomponents.componentTitle
    )

[_error:DB:private] => 1

Note the parenthesis. I do an entire subquery, which i use as a temporary table in my outer query, defined as “a”. (Not the most inventive temporary table name i’ve ever used, but it works).

If you notice in the inner select, i select fscomponents.componentTitle and fsattendances.attendee as my returns. That means that “a” is a table with two columns - componentTitle and attendee (I could have renamed them, but chose not to).

I see, that is very clever to do what you did. I see now that it should work since it is a properly formatted query. I will go back to my page and check out why I am getting an error on the query. thanks a million.

one more quick question… if I may impose?

is the “1” standing for as a boolean or as a string?
(bare with me I am still pretty green to the whole web programing scene)

ok Cool, I got it working…
I changed the “1” in the WHERE clause to “$userid” and voila!!! works like a charm after I inserted an if staement to filter the NULL values.

here it is for those who might be interested or were following:

<?php 
		$userid = $user->data()->id;
		$rss = DB::getInstance()->query(" SELECT fscomponents.componentTitle, fscomponents.id, a.attendee FROM (SELECT fscomponents.componentTitle,fsattendances.attendee 
			FROM fscomponents LEFT JOIN fstraining 	ON fscomponents.id = fstraining.FSComponentId 
			LEFT JOIN fsattendances ON fstraining.fireSafetyId = fsattendances.fireSafetyId 
			WHERE fsattendances.attendee = $userid) AS a RIGHT JOIN fscomponents ON a.componentTitle = fscomponents.componentTitle");
		

		if($rss->count()){
			
			echo '<table>';
				echo '<th class="th">Fire Safety Component</th>';
					echo '<tr>';
					  foreach($rss->results() as $lists){
						  if($lists->attendee == NULL){
						  echo	'<td class="tdLeft"> <a  href="firesafety.php?id='.escape($lists->id).'">'.escape($lists->componentTitle).'</a></td>';
						  }
					echo '</tr>';	
					  }	
		}else{echo 'There is no existing Fire Training Component at the monent';}
		echo'</table>';
		?>
    </section>

Mucha Gracias Amigo StarLion

2 Likes

Sorry yes, forgot to replace that when i did the copy/paste out of my test run. the “1” was the user ID.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.