Dynamic Forms & MySQL

[Smurlos]

Hi Everyone,

I have a bit of a problem on my hands. I've been tasked with making a team rota program and will be using a web based PHP/MySQL system.

My current database set up is as follows:

==========================================

Table 1: Staff_List
Fields: StaffID, Firstname, Surname, NumShifts, ContractHours, InitialDeptID, PayRate, PIN, LastDate, InRota, BreakPaid, HolidayHours

NumShifts is number of shifts the person works per week for the manager's reference when building the rota, InitialDeptID is the department the staff member belongs to on the rota (as some team members are multiskilled and may be used in other departments), InRota for this purpose is always 1, and LastDate is if the team member has stopped working for the company so they no longer appear on the rota from this date.

Table 2: Rota_List
Fields: RotaID, MondayDate, TuesdayDate, WednesdayDate, ThursdayDate, FridayDate, SaturdayDate, SundayDate, Status, LastUpdate, Budget, Target

The dates are stored in DD/MM/YYYY format

Table 3: Hours_List
Fields: EntryID, RotaID, StaffID, DeptID, Date, StartTime, FinishTime, TotalHours, HoursDec, PayRate, ShiftCost, Requested

Table 4: Dept_List
Fields: DeptID, DeptName, DeptShort, InRota

For this purpose, InRota can be regarded as 1. DeptShort is for admin purposes.

==========================================

Now, at the moment my buildrota.php page outputs a table (see attached - sorry about the quality, couldnt get it any better as max 100kb file size!) divided into departments. Team member names are down the side of the table and the dates are across the top Monday to Sunday. Team Members are given an initial department ID to determine which department their name comes under.

Then the manager building the rota has to click the "add a shift" link next to the persons name for the date they want them to work. This pops up a page for the shift to be created.

As you can appreciate this is a long winded process. So I wanted to condense the process down by doing the following:

• Instead of saying "add shift", have an input box for start time, input box for finish time and drop down menu for department, for each day, for each team member in the table.

• Then once all the shifts have been filled in that are needed, the manager clicks the submit button and then the script would have to insert a new entry into the "hours_list" table for each day for each team member with fields completed...

I was wondering if anyone could help me in regards to this?

Many thanks!

[Smurlos]

I'm thinking I need to do a while loop for the staff.

<?php
echo("<form name='rota' method='post' action='$PHP_SELF'>");

$select_staff = mysql_query("SELECT * FROM Staff_List WHERE InRota='1' AND LastDay<=$SundayDate"); // $SundayDate would be the last date on this rota

while ($staff = mysql_fetch_array($select_staff)) {

echo("<tr>
<td>$staff[FirstName] $staff[Lastname]</td>
<td>$staff[NumShifts]</td>");

// Monday
echo("<td><input type='text' name='$staff[StaffID]_$MondayDate_Start'> to <input type='text' name='$staff[StaffID]_$MondayDate_End'><br><br>");

// DROP DOWN MENU HERE FOR DEPARTMENT named as $staff[StaffID]_$MondayDate_Dept

// REPEAT ABOVE FOR TUESDAY TO SUNDAY

}

echo("<input name='submit_rota' value='Finalise Rota'></form>");

This is pretty much the only way I can think to display the rota, but then I need help on how to process the form... Thanks 🙂

[anoopmail]

u r correct..Just confirm your html source generated that correct form input field names are generated. I doubt

name='$staff[StaffID]_$MondayDate_Start'>

works

[Smurlos]

Hi anioopmail, sorry I don't understand what you mean...?

Basically as there will be a lot of input fields (1 for each day, for each staff member, for the start time of shift and then 1 for each staff member, for each day, for the end time of shift), i need to come up with a format for naming each input box, so I'd assume it'd be along the lines of staffid_date_start and then staffid_date_end sort of thing?

The only thing I'd then struggle with is how to process the form...

[anoopmail]

I just tried to say to confirm the field names are formed correctly in the generated HTML rendered by PHP. do a print_r($_POST) and verify you are getting all expecting fields