I'm designing a staff rota planner....have three tables Staff (Staff details), Event (Event details), and
Job (JobId, JobDate, EventId (fk), StaffId (fk)). I need to display the last inserted
job detail with the staff name. I've been at it for couple of hours and getting nowhere. Thanks for the help in advance. My code is the following:
$eventId = $_POST['eventid'];
$selectBox = $_POST['selectbox'];
$timePeriod = $_POST['time'];
$selectedDate = $_POST['date'];
$count = count($selectBox);
//constructing the staff selection
if (empty($selectBox))
{
echo "<p>You didn't select any member of staff to be assigned.";
echo "<p><input type='button' value='Go Back' onClick='history.go(-1)'>";
} else
{
echo "<p> You selected ".$count. " staff for this show.";
for ($i=0;$i<$count;$i++)
{
$selectId = $selectBox[$i];
//insert the details into the
Job table in the database
$insertJob = "INSERT INTO
Job (JobDate, TimePeriod, EventId, StaffId)
VALUES ('".$selectedDate."', '".$timePeriod."', ".$eventId.",
".$selectId.")";
$exeinsertJob = mysql_query($insertJob) or die (mysql_error());
}
}
//display the inserted
job details
$insertedlist = "SELECT
Job.JobId, Staff.LastName, Staff.FirstName,
Job.JobDate,
Job.TimePeriod
FROM Staff,
Job
WHERE
Job.StaffId = Staff.StaffId
AND
Job.EventId = $eventId
AND
Job.JobDate = ".$selectedDate;
$exeinsertlist = mysql_query($insertedlist) or die (mysql_error());
if ($exeinsertlist) {
echo "<p><table cellspacing='1' cellpadding='3'>";
echo "<tr><th colspan=5> ".$eventname."</th></tr>";
echo "<tr><th>Job Id</th><th>Last Name</th>
<th>First Name </th><th>Date</th><th>Hours</th></tr>";
while ($joblistarray = mysql_fetch_array($exeinsertlist))
{
echo "<tr><td align=center>".$joblistarray['JobId']."
</td><td align=center>".$joblistarray['LastName']."</td><td
align=center>".$joblistarray['FirstName']."
</td><td align=center>".$joblistarray['JobDate']."
</td><td align=center>".$joblistarray['TimePeriod']."</td></tr>";
}
echo "</table>";
echo "<h3><a href=AssignStaff.php>Add More Staff?</a></h3>";
} else {
echo "The
Job list can not be displayed at this time. Try again.";
echo "<p><input type='button' value='Go Back' onClick='history.go(-1)'>";
}
