Need help displaying a table dynamically based on a drop down date selection

Posted by Gideon on Stack Overflow See other posts from Stack Overflow or by Gideon
Published on 2010-03-28T20:42:24Z Indexed on 2010/03/28 20:43 UTC
Read the original article Hit count: 213

Filed under:
|

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>&nbsp&nbsp</td><td align=center>".$staffarray['LastName']."
            </td><td>&nbsp&nbsp</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'>&nbsp&nbsp";
    echo "<input type='reset' value='Start Over'>";
    echo "</td></tr>";
    echo "</table>";

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql