title: Writing a PDO search query from a PHP array
body: <p>I'm building an application using PHP 7 and a PDO connection to a MySQL database.</p>
  
  <p>One part of the application contains a search form which allows a user to search for a training course by 3 different fields: the course category, the course name, and a date.</p>
  
  <p>The types of elements on the form are:</p>
  
  <ol>
  <li>Course category - dropdown, with numerical (int) ID's.</li>
  <li>Course name - text input</li>
  <li>Date - date picker (using HTML 5 <code>type="date"</code> parameter to get a calendar in the browser).</li>
  </ol>
  
  <p>These fields can be used in conjunction, or on their own. This means a user could search, for example, just by (1), or (2 &amp; 3), or all (1 &amp; 2 &amp; 3).</p>
  
  <p>I've written the PHP to get the POST data and it's now in an array - for example:</p>
  
  <pre><code>$search_data = [
      'category' =&gt; 3,
      'name' =&gt; 'Hazard training',
      'date' =&gt; ''
  ]
  </code></pre>
  
  <p>I want to use this within a PDO query but I don't know what the best way to write it is because (1) and (3) would be an <code>=</code> query condition, whereas (2) is a <code>LIKE</code>. My solution was going to be looping through the search terms and then trying to construct a query, e.g.</p>
  
  <pre><code>$sql = ' WHERE ';
  foreach ($search_data as $key =&gt; $value) {
      if ($key == 'category') {
          $sql .= ' category = ' . $value;
      }
      if ($key == 'course_name') {
          $sql .= ' course_name LIKE % ' . $value ' % ';
      }
      if ($key == 'date') {
          $sql .= ' date = ' . $value;
      }
  }
  </code></pre>
  
  <p>The trouble with this is it doesn't work because of having to bind the parameters in PDO. It also doesn't work because I can't find a way to get the <code>AND</code> between each query (if there is a preceding statement).</p>
  
  <p>I'm lost with this now and unsure what the best way to write this is.</p>
  
  <p>Any help would be appreciated.</p>
  
  <p><b>Edit:</b> I realise that hardcoding the names, e.g. <code>($key == 'course_name')</code> isn't ideal, but this is only being done because of the different query conditions (<code>LIKE</code> vs <code>=</code>). I assume that one could make <code>$search_data</code> multi-dimensional to say which type of query it was, but this is beyond my initial problem and probably another post.</p>