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:
//Create the day pull-down menu.
$days = range (1, 31);
echo "<Select Name=day Value=''><Option>Day</option>";
foreach ($days as $value) {
echo '<option value="'.$value.'">'.$value.'</option>\n';
}
echo "</Select>";
echo "</td><td>";
//Create the month pull-down menu
echo "<Select Name=month Value=''><Option>Month</option>";
echo "<option value='01'>Jan</option>";
echo "<option value='02'>Feb</option>";
echo "<option value='03'>Mar</option>";
echo "<option value='04'>Apr</option>";
echo "<option value='05'>May</option>";
echo "<option value='06'>Jun</option>";
echo "<option value='07'>Jul</option>";
echo "<option value='08'>Aug</option>";
echo "<option value='09'>Sep</option>";
echo "<option value='10'>Oct</option>";
echo "<option value='11'>Nov</option>";
echo "<option value='12'>Dec</option>";
echo "</select>";
echo "</td><td>";
//Create the year pull-down menu
$currentYear = date ("Y");
$years = range ($currentYear, 2020);
echo "<Select Name=year Value=''><Option>Year</option>";
foreach ($years as $value) {
echo '<option value="'.$value.'">'.$value.'</option>\n';
}
echo "</Select>";
echo "</td></tr></table>";
echo "</td><td>";
//echo "<img src='../ETMSimages/etms_staff.png'</img></td><td>";
//construct the available staff list
$staffsql = "SELECT StaffId, LastName, FirstName
FROM Staff
order by StaffId";
$staffResult = mysql_query($staffsql);
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>";
