working with the values of multiple tables

[cyberangel]

 

hi everybody

 

simply love this forum because i always get the question perfectly answered.

i am here this time with a rather complicated question:

i am creating a simple duty plan for different departments for exeample department #1 is "Tech" and department #2 is  "Service" different employees working in these departments change their dutys and will are sent to another department or section or go on holidays. this is why i have at least  4 different mysql tables to store the data:-

 

personel(storing the personal information of the workers)

dutyplan(storing the week's working daysy of the workers)

departments(storing the starting and ending dates of the department change)

vacation(stores the start and ending dates of the vacations)

 

in all the tables the common id is the pid which is issued unique to every worker.

 

i am still able to work only with the 1st two tables. i can edit and create new plans for the weeks for employees

working in the departments with the following php code(submitting only to edit the plan)

if i add a date range for example Monday the 24th of January to Sunday the 30th of January for an employee working in the Tech department to work a few days in service department, it will show the employee in the week on the plan of both departments. if both department heads edit the plan without communicating to each other, and knowing on which date the worker goes out and in to the department, the 1st one will plan him for the whole week on his dutyplan and the second department head will overwrite this plan(if edited) or if creating a new plan(create the duty of the worker also one more time). i want to avoid this confusion and manual work.

the same is with the vacation or sickness tables, if the employee has vacation, the program should check and return the selected value in the pulldown menu with the value stored in the vacation or sickness tables appropriate to the date in the plan table.

 

 

 

************************************************************************

form.php:

 

  <?

require "config.php";

 

$result=mysql_query("select * from personel, dutyplan, department, vacation WHERE personel.pid = dutyplan.pid and

 

personel.pid = vacation.pid and dp.pid = departments.pid and departments.Section = 'Service' order by dp.id asc");

?>

 

              <?

while($row=mysql_fetch_assoc($result)){

?>

 

 

// after that i create table, displaying all the days of the week from monday to sunday and use this code

              <tr>

                <td><? echo $row['FirstName'] . " " . $row['LastName']; ?>: </td>

                <td>

                  <select name="monday_<? echo $row['id']; ?>" id="select1">

                    <option><? echo $row['Mo']; ?></option>

                    <option>Duty</option>

                    <option>Free</option>

                    <option>Vacation</option>

 

//the same way down to sunday, for every day the different options to be selected. it displays the records

 

perfectly, as long as there is no change of department planed and the vacation must also be selected manually.

 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

update.php

 

    <?

 

if($_POST['Submit']){

 

require "includes/config.php";

 

 

$result=mysql_query("select * from dutyplan, departments, vacation order by dutyplan.id asc");

 

 

while($row=mysql_fetch_assoc($result)){

 

 

$mo=$_POST["monday_".$row[id]];

$tu=$_POST["tuesday_".$row[id]];

$we=$_POST["wednesday_".$row[id]];

$th=$_POST["thursday_".$row[id]];

$fr=$_POST["friday_".$row[id]];

$sa=$_POST["saturday_".$row[id]];

$su=$_POST["sunday_".$row[id]];

 

 

 

mysql_query("update dp set Mo='$mo', Tu='$tu', We='$we', Th='$th', Fr='$fr', Sa='$sa', Su='$su' where

 

id='$row[id]'");

}

echo "Records updated";

}

?>

 

****************************************************

 

how would you gueys solve this problem?

many thanks in advance