Goal: pull out the title name of the fire safety components that have not yet been followed by a user.
I am building a database where I can track users on going fire safety training or drills. A report is created each time a drill is performed. This report will indicate what the drill was about, what fire safety components where instructed and who attended the drill. Each user have a profile they can go to in order to review their training progress. I am echoing the fire safety components that was followed and also want to show in another area the fire safety component that currently exist but not yet followed by a user.
tables:, ,
firesafety
PKEY->id
with a bunch of other fields i dont need here
fsattendances
PKEY->id
FKEY->fireSafetyId
field ā attendee (userid)
fstraining,
PKEY->id
FKEY->fireSafetyId
FKEY->componentId
fscomponents
PKEY-> id
componentTtile
and some other field I donāt really need to pull out
query I have so far:
" SELECT componentTitle FROM fscomponents WHERE 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 WHERE fsattendances.attendee <> $userid AND fscomponents.componentTitle=NULL"
which doesnāt work. I am returning the same exact results of my query that is checking for fscomponent that have been attended to.
example:
user was present at 3 fire drills where fire safety component: fire extinguisher, Room Search and Radio Communication were instructed.
those are only 3 components from a list of lets say 10
so I would in essence need to pull the 7 components that have still to be learned by the user and echo these componentTitle(s) on their profile
It is very hard to follow the logic of your DB tables and without data to see the relations, I will offer this query.
SELECT
fsc.componentTitle
FROM fscomponents as fsc
LEFT JOIN fstraining as fst
ON fst.componentId <> fsc.id
LEFT JOIN fsattendances as fsa
ON fsa.fireSafetyId = fst.fireSafetyId
WHERE fsa.attendee = $userid
Maybe attaching sql files might shed light on things.
Thanks for giving it a shot. Unfortunatley it does not work.
here is something that does work(but it is from Microsoft Access database) I been getting a big chunk of my slq statements from Access since it is so quick to build. One thing I still have to learn is how to interpret it so mysql and php can work it.
first query is to select all the component a user has participated in. I get this by the following query and which becomes my nested query: called āuserAttendedā
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"));
then I run another an āunmatched queryā from the wizard and used the first query to compare it to.
SELECT fscomponents.componentTitle
FROM fscomponents LEFT JOIN userAttended ON fscomponents.[componentTitle] = userAttended.[componentTitle]
WHERE (((userAttended.componentTitle) Is Null));
and this works . I just donāt know how to join the two queries into one on my web page.
The logic as best as I can explain it goes like this:
I have users who attend fire drill every months. These users ID get inserted in the āfsattendanceā table along with the ID number of the particular drill they attended. This ID is generated from a report that is filled up after every fire drill and saved into a table called āfiresafetyā along with the ID of the fire safety component that was practiced. The fire safety components are a bunch of exercise like fire exthinguisher operation, room search, radio communication and so on. Every month a different component or a combination of component are instructed to workers. So the fire safety report and the fire safety compnent gets saved as a record into a table called āfstraining.ā I have to use this type of logic in order to keep my tables ānormalā
Well I do understand the logic of what you are trying to do. What isnāt so clear is the relationship of the tables. Looking at it, fireSafetyId or id from firesafety table, this seems to be a primary key used in most tables and to me this would be a general group id/name with components being a sub group, yet in your attendances table you are not logging which component they have learned just the general fireSafetyId.
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).