Check checkboxes if value is present in a database
<p>I have a form that allows the user to add or remove users from a job by using checkboxes.  At the moment the engineer's names etc are stored in a table called 'users' and they are identified by their user type, engineers.  The checkboxes on the form are created by using a while loop to display a mysql query.  This is done so that new users can be added to the database via a webpage at a later date and their name will automatically be added to the list of available engineers.  To add an engineer to a job their name is simply ticked and the form submitted.</p>

<p>This works well but I would like the user to be able to add or remove users by checking and unchecking boxes on another form but I can't think for the life of me how to do this.</p>

<p>The users assigned to a job are stored in a many-to-many table called calls_engineers which has 3 fields</p>

<p>id_ce (unique id)
call_ce (id of job, a foreign key)
engineer_ce (id of engineer, a foreign key)</p>

<p>The engineer_ce is the users primary key in the 'users' table which is 'id'.</p>

<p>The code I have so far is...</p>

<pre><code>&lt;?  $sql = "SELECT * FROM calls_engineers WHERE call_ce = '$diary_id'";
      $result = mysql_query($sql) or die(mysql_error());
      $row2 = mysql_fetch_array($result);
      $sql = "SELECT * FROM users WHERE type = 'engineer' ORDER BY first_name";
      $result = mysql_query($sql) or die(mysql_error());
      while ($row = mysql_fetch_array($result)){ ?&gt;
          &lt;div style="width:70px; float:left"&gt;
              &lt;? 
                  if($row2['engineer_ce'] == $row['id']){
              ?&gt;
              &lt;input type="checkbox" name="engineer[]" checked="checked" value="&lt;? echo $row['id']; ?&gt;" /&gt;
              &lt;? echo '   '.$row['first_name']; 
              } else { ?&gt;
                  &lt;input type="checkbox" name="engineer[]" value="&lt;? echo $row['id']; ?&gt;" /&gt;
              &lt;? echo '   '.$row['first_name'];
              }?&gt;
          &lt;/div&gt;
  &lt;?
      }

  ?&gt;
</code></pre>

<p>All this does is create checkboxes for each user which is an engineer but it doesn't check them if the user is assigned to the job.</p>

<p>Any help will be greatly appreciated</p>
