ok I understand what you are saying. I use “ID” as a primary key for all my tables. Where there are Foreign keys pointing at the primary key I always used the whole name.
Example: tbl FireSafety primary key is ID. anywhere else this primary key is used as a foreign key I use the full name :fireSafetyId
relationship flow like this:
the driving force behind this exercise is the fire drill report. ->firesafety table. the ID of this record is used as a foreign key in the [fstraining] table along with another foreign key for the safety component that was instructed. The last table is the attendance which has a foreign key of the user and of the [fireSafety] record.
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
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
<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.
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.
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.
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>