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?

PDO bind for date perhaps:.... https://stackoverflow.com/questions/2374631/pdoparam-for-dates — Richard Housham, Apr 30 '19 at 09:30
@DirkScholten good spot! Corrected in code + Q. Still didn't fix it unfortunately — treyBake, Apr 30 '19 at 09:34
@RichardHousham tried via binding - adding `PDO::PARAM_STR` to the `:dateTs` bind. Again nothing :/ — treyBake, Apr 30 '19 at 09:37
@treyBake Can you put together a DB fiddle with your tables and their values, so we can play around and help debug it? — Qirel, Apr 30 '19 at 10:00
@Qirel just answered! haha but where would I go (in future) for a good DB fiddle? — treyBake, Apr 30 '19 at 10:03
https://www.db-fiddle.com/ or http://sqlfiddle.com/ is what I've been using before. When asking SQL questions, it's always helpful to create a fiddle with your table/data and current query. Makes troubleshooting easier for those of us trying to answer :-) — Qirel, Apr 30 '19 at 10:04

score 1 · Accepted Answer · answered Apr 30 '19 at 10:02

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.

PHP/MySQL Compare against a date

1 Answers1