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)'>";
}
