title: PDO JOIN while binding IN on two tables with related data
body: <p>This might be less complicated than I'm making it, but I'm stuck. I have two tables with columns that have related data that I'd like to join when the user searches for one or two terms. Example tables would look like this, using placeholder examples:</p>
  <pre><code>table: FoodCategories
  +---------+--------------+
  | ID (INT)| food (VCHAR) |
  +---------+--------------+
  | 40      | Seafood      | 
  | 41      | Pasta        | 
  | 42      | Salad        | 
  | 43      | Bread        |
  +------------------------+
  </code></pre>
  <p>And</p>
  <pre><code>table: FoodItems
  +---------+--------------+
  | ID (INT)| item (VCHAR) |
  +---------+--------------+
  | 40      | Seabass      | 
  | 40      | Shrimp       | 
  | 40      | Lobster      | 
  | 41      | Spaghetti    | 
  | 41      | Lasagne      | 
  | 41      | Linguine     |
  | 42      | Cesar        | 
  | 42      | Tossed       | 
  | 43      | Baguette     | 
  | 43      | Rolls        | 
  | 43      | Breadsticks  |
  +------------------------+
  </code></pre>
  <p>What I am trying to do is output the data from both tables together, but leave it open to search either table if the term is not found in the second when the user searches one more more words, dealing with comma separated words using IN (which I've learned to bindparam in PDO, and I assume this is what is causing the issue, but I'm not sure). I can make it work with ONE word search and with two or more words from the SECOND table, but if the word happens to be from the FIRST table, I get null.</p>
  <p>So, for example, if we search the word &quot;Shrimp&quot;, I want to get</p>
  <pre><code>&quot;ID: 40, food: Seafood, item: Shrimp&quot;
  </code></pre>
  <p>If we search &quot;Cesar, Breadsticks&quot; I want to get</p>
  <pre><code>&quot;ID: 42, food: Salad, item: Cesar&quot;
  &quot;ID: 43, food: Bread, item: Breadsticks&quot;
  </code></pre>
  <p>Now, if I want the user also to be able to get results from the first table, this is where I can't seem to get it to work. For example, searching &quot;Seafood, Rolls&quot; I want to get</p>
  <pre><code>&quot;ID: 40, food: Seafood&quot;
  &quot;ID: 43, food: Bread, item: Rolls&quot;
  </code></pre>
  <p>or if I search &quot;Pasta, Salad&quot; I want to get simply</p>
  <pre><code>&quot;ID: 41, food: Pasta&quot;
  &quot;ID: 42, food: Salad&quot;
  </code></pre>
  <p>This is the query which actually works to some extent. Every other attempt fails.</p>
  <pre><code>$questionmarks = &quot;&quot;;
  $query =&quot;SELECT DISTINCT FoodCategories.ID,FoodCategories.food FROM FoodCategories
  INNER JOIN 
  FoodItems ON FoodCategories.ID = FoodItems.ID
  WHERE FoodItems.items IN (&quot;;
  for($i=0;$i&lt;count($searchterms);$i++)
  {
      $questionmarks .= &quot;?,&quot;;
  }
  $query .= trim($questionmarks,&quot;,&quot;);
  $query .= &quot;)
  ;&quot;;
  </code></pre>
  <p>So, 1. what kind of JOIN should I use if INNER JOIN is wrong, 2. how to I get the data for <code>FoodItems.item</code> in my SELECT (I've tried subqueries but don't know if that's the right approach) and 3. How do I perform an AND/OR situation to pull the data from either or both tables (items AND/OR food) while still being able to bind my IN statement?</p>