working with more then two tables...HELP!!!

[cyberangel]

holla everybody

 

so back again with another crazy question 

 

am working on a small database with 3 to 4 tables. need at least 3 tables. the problem is that if i work with two of them, it works perfectly, as soon as i add the 3rd one, and try to edit a record, it edits only the 1st row and not all the rows required 

the first table is called "perso" containing the personal data of the employees

the second table is called "center" this contains the information of the starting and ending dates of an employee on a center.

the third table is called "dp" it contains the workplan of the employee

all the 3 tables have a common fiield "pid" which is identical

 

now i want the form to display alle the records suppose in the department IT, the workers have their plan of starting their work on a certian center on a certian date to a certian date. usually 2 weeks to a month. they should therefor appear on the plan of the specified center.

it is simple if i am using the dp and center tables, but i want the perso table too so that i can display the names according to the pid. and maybe some other tables containing information of vacation etc. but as i said, as soon as i add the 3rd table to the query, it breaks down.

 

please check the code:

 

// the form code

 

                                  <?

require "db_credentials.php";

 

// here is the problem, in the following query, if i add "from dp, perso, center where dp.pid = center pid it wont work. but as long as it looks like below, it works find...

 

$result=mysql_query("select * from dp, perso where dp.pid = perso.pid order by dp.id asc");

 

// Fetch record rows in $result by while loop and put them into $row.

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

?>

// then the table with cells filled with such code:

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

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

                      <option>Select</option>

                      <option style="background-color:#FF0000 ">FDLS</option>

                      <option style="background-color:#FF0000 ">FDFD</option>

//and so on till sunday again

              <? } // End while loop. ?>

 

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

 

<?

 

if($_POST['Submit']){

 

// Connect database.

$host="mysql";

$db_user="name";

$db_password="password";

$database="my_db";

mysql_connect($host,$db_user,$db_password);

mysql_select_db($database);

 

 

$result=mysql_query("select id from dp order by id asc");

 

 

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

 

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

 

mysql_query("update dp set Mo='$mo'' where id='$row[id]'");

}

echo "--- DB Updated---";

}

?>

 

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

 

all help will be very much appriciated

 

thanks in advance

[o3d]

Maybe try this:

 

select * from dp, perso, center where dp.pid = perso.pid and center.pid = dp.pid order by dp.id asc

 

Just remember that the same pid row data need to exist in all 3 tables else it won't work.

 

Ps. try to use the sql keywords JOIN, etc when querying from multiple tables. It will save you a lot of debugging when you start working on larger queries: eg.

 

select *
from dp 
join perso on perso.pid = dp.pid
join center on center.pid = dp.pid
order by dp.id asc

[cyberangel]

thanks o3d for the quick reply

 

"where dp.pid = perso.pid and center.pid = dp.pid order by dp.id asc" is what i meant by adding the 3rd table. this is what i tried but as soon as i do this, it only updates the 1st row the right way and removes all date from the rest of the rows and put them on blank

 

will try it with the join method, thanks for the tip. hope it works