title: Codeigniter - Join same table and search with array
body: <p>I have two input fields. Both leading to an array array of colors that will be matched on the database. 
  The arrays can look like this</p>
  
  <pre><code>Array ( [0] =&gt; red [1] =&gt; blue [2] =&gt; green ) 
  </code></pre>
  
  <p>One input is used for searching a table called ´image_topcolor´ and the other input is searching another table, ´image_bottomcolor´.
  <br>
  I'm trying to come up with a structure to search for images with these two inputs based on color selection.</p>
  
  <p>I want the user to be able to select several colors and then return all images which has the selected color in the database.
  The search works fine when using only one input field with the following code:</p>
  
  <pre><code>$this-&gt;db-&gt;select('*'); 
  $this-&gt;db-&gt;from('image');
  
  if(!empty($top_colors[0]) &amp;&amp; empty($bottom_colors[0])) {
  $this-&gt;db-&gt;join('image_topcolor', 'image_topcolor.image_id = image.id' ,'inner');
  $this-&gt;db-&gt;join('color', 'color.id = image_topcolor.color_id', 'inner');
  }
  if(!empty($bottom_colors[0]) &amp;&amp; empty($top_colors[0])) {    
  $this-&gt;db-&gt;join('image_bottomcolor', 'image_bottomcolor.image_id = image.id' ,'inner'); 
  $this-&gt;db-&gt;join('color', 'color.id = image_bottomcolor.color_id', 'inner');
  }
  
  if(!empty($bottom_colors[0])) {     
  $this-&gt;db-&gt;where_in('color', $bottom_colors); 
  }
  if(!empty($top_colors[0])) {
  $this-&gt;db-&gt;where_in('color', $top_colors); 
  }
  </code></pre>
  
  <p>This returns images to the user even if the user has selected a color that doesn't have an id matched with an image yet, which is perfect! <br>
  <br>
  I want this to work the same way when using both input fields to find all images that matches the colors from the inputs.</p>
  
  <p>I want to use something similiar to:</p>
  
  <pre><code>$this-&gt;db-&gt;select('*'); 
  $this-&gt;db-&gt;from('image');
  
  if(!empty($top_colors[0]) &amp;&amp; !empty($bottom_colors[0])) {
  $this-&gt;db-&gt;join('image_topcolor', 'image_topcolor.image_id = image.id' ,'left');
  $this-&gt;db-&gt;join('image_bottomcolor', 'image_bottomcolor.image_id = image.id' ,'left');      
  $this-&gt;db-&gt;join('color as a', 'image_topcolor.color_id = a.id','left');
  $this-&gt;db-&gt;join('color as b', 'image_bottomcolor.color_id = b.id','left');
  }
  
  if(!empty($top_colors[0]) &amp;&amp; !empty($bottom_colors[0])) {
  $this-&gt;db-&gt;where_in('a', $top_colors); 
  $this-&gt;db-&gt;where_in('b', $bottom_colors); 
  }
  </code></pre>
  
  <p>This returns with the error </p>
  
  <blockquote>
    <p>"Column 'color' in where clause is ambiguous"</p>
  </blockquote>
  
  <p>"SELECT * FROM <code>image</code> LEFT JOIN <code>image_topcolor</code> ON <code>image_topcolor</code>.<code>image_id</code> = <code>image</code>.<code>id</code> LEFT JOIN <code>image_bottomcolor</code> ON <code>image_bottomcolor</code>.<code>image_id</code> = <code>image</code>.<code>id</code> LEFT JOIN <code>color</code> as <code>a</code> ON <code>image_topcolor</code>.<code>color_id</code> = <code>a</code>.<code>id</code> LEFT JOIN <code>color</code> as <code>b</code> ON <code>image_bottomcolor</code>.<code>color_id</code> = <code>b</code>.<code>id</code> WHERE <code>color</code> IN('blue') AND <code>color</code> IN('red') AND <code>a</code>.<code>color</code> IN('red') AND <code>b</code>.<code>color</code> IN('blue')"</p>
  
  <p>I'm not sure how I can use both arrays to find all images that matches any of the selected colors.<br>
  My tables looks like this: <br><br>
  <strong>image</strong></p>
  
  <pre><code>id | color | info
  ---|-------|------
  1  | blue  | foo
  2  | red   | bar
  </code></pre>
  
  <p><strong>color</strong></p>
  
  <pre><code>id | color |
  ---|-------|
  1  | red   |
  2  | blue  | 
  3  | green |
  </code></pre>
  
  <p><strong>image_topcolor</strong></p>
  
  <pre><code>image_id | color_id |
  ---------|----------|
  1        | 1        |
  1        | 2        |
  </code></pre>
  
  <p><strong>image_bottomcolor</strong></p>
  
  <pre><code>image_id | color_id |
  ---------|----------|
  2        | 1        |
  2        | 3        |
  </code></pre>
  
  <p>Sorry for the long post.
  Question recap:
  How can I get the results of <strong>all</strong> images with matching colors when using both inputs? Just like I get when using only one input. <br> 
  Thanks!<br>
  <br>
  <strong>Update</strong><br>
  This query is now not generating any errors.<br>
  But I'm still not getting the results if the user selects a color that doesn't exist. I only get the result if the image has the same selected color in both tables.
  Example: An image has red, blue top_color and blue, green bottom color. I cannot get this result by searching for red top and green bottom. However if I search for blue top and blue bottom, I get the result. I guess this has do with the joins?</p>
  
  <pre><code>$this-&gt;db-&gt;select('*'); 
      $this-&gt;db-&gt;from('image');
      if(!empty($top_colors[0]) &amp;&amp; !empty($bottom_colors[0])) {
      $this-&gt;db-&gt;join('image_topcolor', 'image_topcolor.image_id = image.id' ,'left');
      $this-&gt;db-&gt;join('image_bottomcolor', 'image_bottomcolor.image_id = image.id' ,'left');  
  
  
  $this-&gt;db-&gt;join('color as a', 'image_topcolor.color_id = a.id','left');
  $this-&gt;db-&gt;join('color as b', 'image_bottomcolor.color_id = b.id','left');
  
  if(!empty($top_colors[0]) &amp;&amp; !empty($bottom_colors[0])) {
  $result = array_merge($top_colors, $bottom_colors);
  
  $this-&gt;db-&gt;where_in('a.color', $top_colors); 
  $this-&gt;db-&gt;where_in('b.color', $bottom_colors);
  
  }
  </code></pre>