I am designing a job rota planner for a company and need help displaying a
dynamic table containing the staff details. I have the following tables in MySQL database: Staff, Event, and Job. The staff table holds staff details (staffed, name, address...etc), the Event table (eventide, eventName, Fromdate, Todate...etc) and the Job table holds (Jobid, Jobdate, Eventid(fk), Staffid (fk)).
I need to dynamically display the available staff list from the staff table when the user selects the EVENT and the DATE (3 drop downs: date, month, and year) from a PHP form.
I need to display staff members that have not been assigned work on the selected date by checking the Jobdate in the Job table.
I have been at this for all day and can't get around it. I am still learning PHP and would surely appreciate any help I can get.
My current code displays all staff members when an event is selected:
if(isset($_POST['submit']))
{
$eventId = $_POST['eventradio'];
}
$timePeriod = $_POST['timeperiod'];
$Day = $_POST['day'];
$Month = $_POST['month'];
$Year = $_POST['year'];
$dateValue = $Year."-".$Month."-".$Day;
$selectedDate = date("Y-m-d", strtotime($dateValue));
//construct the available staff list
if ($selectedDate)
{
$staffsql = "SELECT s.StaffId, s.LastName, s.FirstName
FROM Staff s
WHERE s.StaffId NOT IN
(SELECT J.StaffId
FROM Job J
WHERE J.JobDate != ".$selectedDate.")";
$staffResult = mysql_query($staffsql) or die (mysql_error());
}
if ($staffResult){
echo "<p><table cellspacing='1' cellpadding='3'>";
echo "<th colspan=6>List of Available Staff</th>";
echo "</tr><tr><th> Select</th><th>Id</th><th></th><th>Last Name
</th><th></th><th>First Name </th></tr>";
while ($staffarray = mysql_fetch_array($staffResult))
{
echo "<tr onMouseOver= this.bgColor = 'red' onMouseOut =this.bgColor = 'white' bgcolor=
'#FFFFFF'> <td align=center><input type='checkbox' name='selectbox[]'
id='selectbox[]' value=".$staffarray['StaffId'].">
</td><td align=left>".$staffarray['StaffId']."
</td><td>  </td><td align=center>".$staffarray['LastName']."
</td><td>  </td><td align=center>".$staffarray['FirstName']."
</td></tr>";
}
echo "</table>";
}
else {
echo "<br> The Staff list can not be displayed!";
}
echo "</td></tr>";
echo "<tr><td></td>";
echo "<td align=center><input type='submit' name='Submit' value='Assign Staff'>  ";
echo "<input type='reset' value='Start Over'>";
echo "</td></tr>";
echo "</table>";
