Right, I shall try to explain my situation as best I can.
I have 5 tables in my DB. 3 of which are connected with this problem.
Table 1 is called engineers and holds a list of engineers with auto allocated numbers set to each one.
Table 2 is called job_cat and contains a list of job catagories each with an auto allocated number.
Table 3 is called eng_job_cat. This table contains 2 colums. One colum called job_cat (job catagories)and the other called eng_no (engineer number). Both of these fields are primary keys.
When an engineer is added to the database, the user is shown a list of check boxes labelled with each of the catagories from the job_cat table. The user selects each catagory that the engineer will cover by ticking the required boxes and clicks Submit. The number assigned to the engineer and the numbers assigned to the catagories selected are then added to the eng_job_cat table.
Now, what I'm trying to do is to give the user the option to edit an engineer's details. This is how I have it working so far:
Page 1
User selects the engineer that they want to edit from a drop down list which is populated from the engineers table
Page 2
This page contains 4 text boxes which are populated with the details of the selected engineer. Here, the user can make changes to the username, location, etc.
Page 3
This page updates the information submitted on the previous page and then (and this is where I'm stuck) displays a list of all of the catagories with tick boxes next to them. The catagories already associated to the engineer SHOULD be ticked but I just can't get it to work.
Here is the code I have so far.
//Get the engineer number from the engineers table that matches the submitted username//
$get_engineer_no_query = "SELECT eng_no FROM engineers WHERE username='".$_POST['username']."'";
//Assign the results of $get_engineer_no_query to $eng_no_result //
$eng_no_result = mysql_query($get_engineer_no_query);
//Assign the engineer number to the variable $eng_no//
while ( $row = mysql_fetch_array($eng_no_result) )
{
$eng_no=$row['eng_no'];
}
//Get all information about all job catagories//
$job_cat_query = "SELECT * FROM job_cat";
//Assign the results of $job_cat_query to $job_cat_result//
$job_cat_result = mysql_query($job_cat_query) OR die(mysql_error());
//Get the job catagory numbers that this engineer is assigned to//
$assigned_job_cat_nos_query = "SELECT job_cat_no FROM eng_job_cat WHERE eng_no='".$eng_no."'";
//Assign the results of $assigned_job_cat_nos_query to $assigned_job_cat_nos_result //
$assigned_job_cat_nos_result = mysql_query($assigned_job_cat_nos_query);
//Show a list of check boxes with the job catagories already assigned pre-checked//
echo "<form name='assigned_job_cat' method='post' action='add-engineer-confirmed.php'>
<table border ='2'>
<tr>";
while( $row = mysql_fetch_array($job_cat_result) )
{
echo "<tr><td><input type='checkbox' name='job_cat[]' value='".$row['job_cat_no']."'";
while( $val = mysql_fetch_array($assigned_job_cat_nos_result) )
{
if ($val['job_cat_no'] == $row['job_cat_no'])
{
echo "checked></tr></td>";
}
}
}
echo "</form>";
Well, if you're still reading!, the above code outputs the correct number of check boxes but none are labelled and only the first one is ticked. I've been playing around with this part of the program for about 5 hours and still no luck. I'm obviously doing something wrong so if someone could point it out, I'd be very grateful.
