title: Tick checkboxes from a join table
body: <p>I need a way to compare a join table from a loop I have showing a list of categories from another table and tick them if the category id matches the doc id.</p>
  
  <p>Here is my current function which I am struggling with:</p>
  
  <pre><code>&lt;?php
  
  function build_cat_checkboxes(){
  
      global $dbc; // database connection
      global $id; // id from the url
  
  
      $q = "SELECT * FROM doc_cat_join LEFT OUTER JOIN cats ON cats.cat_id = doc_cat_join.cat_id;";
      $r = mysqli_query ($dbc, $q); // Run the query.
  
      var_dump($r);
  
      $currentDoc = $id;
  
      while ($row = mysqli_fetch_array($r)) {
  
      $checked = (($row['cat_id'] == $currentDoc) ? 'checked="checked"' : '');
  
      echo '&lt;ul&gt;
            &lt;li&gt;&lt;label&gt;
              &lt;input type="checkbox" name="cat_id[]" value="' . $row['cat_id'] . '" '.$checked.'"&gt; ' . $row["cat_name"] . '&lt;/label&gt;&lt;/li&gt;
          &lt;/ul&gt;';
      }
  
      }
  
  ?&gt;
  </code></pre>
  
  <p>So I have three tables:</p>
  
  <pre><code>docs
    doc_id
  
  cats
    cat_id
  
  doc_cat_join
    id
    doc_id
    cat_id
  </code></pre>
  
  <p>My code above lists out the rows from the cats table and creates checkboxes. When I am in a doc editing it I need a way in my code above to take the doc id form the url (which I can do) and then perform a comparison on the join table? and tick the checkboxes if a rows exists of the doc_id and the cat_id here is a visual of my doc_cat_join table:</p>
  
  <p><img src="https://i.stack.imgur.com/EzdAF.png" alt="enter image description here"></p>
  
  <p>I believe my JOIN is the part that is causing me the issue but not sure on how to cure this issue?</p>
  
  <p>If I do a var_dump on $r inside of the loop I get this..</p>
  
  <pre><code>object(mysqli_result)#3 (5) { ["current_field"]=&gt; int(0) ["field_count"]=&gt; int(7) ["lengths"]=&gt; array(7) { [0]=&gt; int(3) [1]=&gt; int(2) [2]=&gt; int(2) [3]=&gt; int(2) [4]=&gt; int(7) [5]=&gt; int(7) [6]=&gt; int(51) } ["num_rows"]=&gt; int(1) ["type"]=&gt; int(0) }
  </code></pre>
  
  <p><strong>UPDATED</strong></p>
  
  <p>this update reflects the suggest answer below but I am still having some problems:</p>
  
  <pre><code>&lt;?php
  
  function build_cat_checkboxes(){
  
      global $dbc; // database connection
      global $id; // id from the url
  
  
      $q = "SELECT * FROM cats AS c LEFT JOIN doc_cat_join AS dcj ON c.cat_id = dcj.cat_id AND dcj.doc_id = $id";
      $r = mysqli_query ($dbc, $q); // Run the query.
  
      $currentDoc = $id;
  
      while ($row = mysqli_fetch_array($r)) {
  
      $checked = $row['id'];
  
      echo '&lt;ul&gt;
            &lt;li&gt;&lt;label&gt;
              &lt;input type="checkbox" name="cat_id[]" value="' . $row['cat_id'] . '" '.$checked.'"&gt; ' . $row["cat_name"] . '&lt;/label&gt;&lt;/li&gt;
          &lt;/ul&gt;';
      }
  
      }
  
  ?&gt;
  </code></pre>
  
  <p>Here is the query that deletes and inserts the boxes that are ticked:</p>
  
  <pre><code>$q = "SELECT doc_id FROM docs WHERE doc_name='$dn' AND doc_id != $id";
  
          $r = mysqli_query($dbc, $q);
  
          if (mysqli_num_rows($r) == 0) {
  
              // Make the query:
              $q = "UPDATE docs SET doc_name='$dn', doc_content='$dc', doc_owner='$do' WHERE doc_id=$id LIMIT 1";
  
  
              $r = mysqli_query ($dbc, $q);
  
              if ($r) {
  
              // CODE TO DELETE ROW FROM DOC_CAT_JOIN    
              $q = "DELETE from doc_cat_join WHERE doc_id = $id";
              $r = mysqli_query ($dbc, $q);
  
              //AND THEN...    
  
              // CODE TO INSERT BACK INTO DOC_CAT_JOIN    
              $query = "INSERT INTO doc_cat_join (cat_id,doc_id) VALUES "; 
              $cat_ids = $_POST['cat_id']; 
              $length = count($cat_ids); 
              for ($i = 0; $i &lt; count($cat_ids); $i++) { 
              $query.='(' . $cat_ids[$i] . ',' . $id . ')'; 
  
              if ($i &lt; $length - 1) 
              $query.=','; 
              } 
  
          mysqli_query($dbc, $query);    
  </code></pre>