title: PHP/MySQL Compare against a date
body: <p>I'm running into a little issue concerning a datetime picker and MySQL.</p>
  
  <p>I have a PHP script that <em>should</em> get records before (and including) the selected day. However, the dataset returns empty.</p>
  
  <p>Here's the SQL part of the script:</p>
  
  <pre><code>$sql = 'SELECT `pro_title`,
                 `pro_part_number`,
                 `name` AS flag_name,
                 `old_val`,
                 `new_val`
          FROM `products`
          INNER JOIN `flags` ON `id` = `pro_flag_id`
          INNER JOIN `flag_history` ON `pro_part_number` = `part`
          WHERE `pro_flag_id` IN(:flags)
          AND STR_TO_DATE(`ts`, "%y-%m-%d") &lt;= :dateTs;';
  </code></pre>
  
  <p>I then use PDO to bind the params:</p>
  
  <pre><code>array(
      ':flags' =&gt; implode(',', $flags), # this outputs 1,2,3
      ':dateTs' =&gt; $date # this outputs 2019-04-30
  )
  </code></pre>
  
  <p>I've also tried changing <code>&lt;=</code> to <code>&gt;=</code> to no avail (not that it should work, but thought I'd try).</p>
  
  <p>I've come across a good few SO posts but nothing has actually got me there. Here is the description of the <code>flag_history</code> table (where <code>ts</code> is stored)</p>
  
  <pre><code>MariaDB [mastern]&gt; describe `flag_history`;
  +---------+-------------+------+-----+-------------------+----------------+
  | Field   | Type        | Null | Key | Default           | Extra          |
  +---------+-------------+------+-----+-------------------+----------------+
  | id      | int(11)     | NO   | PRI | NULL              | auto_increment |
  | part    | varchar(25) | NO   |     | NULL              |                |
  | ts      | datetime    | YES  |     | CURRENT_TIMESTAMP |                |
  | old_val | int(4)      | YES  |     | NULL              |                |
  | new_val | int(4)      | YES  |     | NULL              |                |
  +---------+-------------+------+-----+-------------------+----------------+
  </code></pre>
  
  <p>And some example data:</p>
  
  <pre><code>MariaDB [mastern]&gt; select * from `flag_history` order by `id` desc limit 5;
  +-------+----------+---------------------+---------+---------+
  | id    | part     | ts                  | old_val | new_val |
  +-------+----------+---------------------+---------+---------+
  | 24026 | PART-001 | 2019-04-30 09:42:22 |       0 |       3 |
  | 24025 | PART-002 | 2019-04-30 09:42:22 |       0 |       3 |
  | 24024 | PART-003 | 2019-04-30 09:42:22 |       0 |       3 |
  | 24023 | PART-004 | 2019-04-30 09:42:22 |       0 |       3 |
  | 24022 | PART-005 | 2019-04-30 09:42:22 |       0 |       3 |
  +-------+----------+---------------------+---------+---------+
  </code></pre>
  
  <p>Then, using PART-001 to make sure the flag_id is actually set:</p>
  
  <pre><code>MariaDB [mastern]&gt; select `pro_flag_id` from `products` where `pro_part_number` = "PART-001";
  +-------------+
  | pro_flag_id |
  +-------------+
  |           3 |
  +-------------+
  </code></pre>
  
  <p>So I'm not really sure what's going wrong, the logic and everything to me (MySQL newb) looks like it should work but it's giving me empty data. I also tried changing the <code>INNER JOIN</code>'s to <code>LEFT JOIN</code>'s but again, didn't work.</p>
  
  <p>What am I doing wrong?</p>