PHP/MySQL Compare against a date

Question

I'm running into a little issue concerning a datetime picker and MySQL.

I have a PHP script that should get records before (and including) the selected day. However, the dataset returns empty.

Here's the SQL part of the script:

$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") <= :dateTs;';

I then use PDO to bind the params:

array(
    ':flags' => implode(',', $flags), # this outputs 1,2,3
    ':dateTs' => $date # this outputs 2019-04-30
)

I've also tried changing <= to >= to no avail (not that it should work, but thought I'd try).

I've come across a good few SO posts but nothing has actually got me there. Here is the description of the flag_history table (where ts is stored)

MariaDB [mastern]> 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              |                |
+---------+-------------+------+-----+-------------------+----------------+

And some example data:

MariaDB [mastern]> 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 |
+-------+----------+---------------------+---------+---------+

Then, using PART-001 to make sure the flag_id is actually set:

MariaDB [mastern]> select `pro_flag_id` from `products` where `pro_part_number` = "PART-001";
+-------------+
| pro_flag_id |
+-------------+
|           3 |
+-------------+

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 INNER JOIN's to LEFT JOIN's but again, didn't work.

What am I doing wrong?

Answer 1

I managed to solve it. As I'm really reading the flag_history table, I changed the main SELECT focus to flag_history, revised SQL:

$sql = 'SELECT `old_val`, `new_val`, `ts`, `flags`.`name` AS flag_name, `pro_part_number`, `pro_title`
        FROM `flag_history`
        INNER JOIN `products` ON `pro_part_number` = `part`
        INNER JOIN `flags` ON `pro_flag_id` = `flags`.`id`
        WHERE `ts` <= :dateTs;';

I slowly added in the INNER JOIN's and managed to get it working when using flag_history. I think the issue actually may have been down to the id field. Being an ambiguous field (flags and flag_history have the id column). Weirdly, I wasn't getting that error though (maybe as I wasn't selecting). Either way managed to resolve it by being more specific with my select and going from the read-table rather than joining it.

On a further note, removing the flags segment from the JOIN shows the constraint error. So my guess is that it didn't show first time as I was reading from a different table.