MYSQL inserting INTERVAL DAY number dynamically?

Question

I understand that you can use CASE to select the value of another column to set the interval :

SELECT CASE some_column
       WHEN "SOME_VALUE" THEN date_add(date, INTERVAL value DAY)
       WHEN "SOME_OTHER_VALUE" THEN date_add(date, INTERVAL value DAY)
       END
       AS newDate
FROM table

But how can I pass that number in from my script (and not a value in the database).

For example, I have a constant my PHP script which grabs it's value from an .env file :

define("DOWNLOAD_DAYS", getenv("DOWNLOAD_DAYS")); // say this is 3 for example

And my query currently looks like this :

$sql = 'SELECT * FROM orders WHERE user_id = :user_id 
        AND status = :status
        AND date_added > DATE_SUB(NOW(), INTERVAL 7 DAY)
        ORDER BY date_added DESC';

How could I use the DOWNLOAD_DAYS constant to replace the 7 in this query?

I've tried ...

AND date_added > DATE_SUB(NOW(), INTERVAL DOWNLOAD_DAYS DAY)

... Which obviously doesn't work as INTERVAL expects a keyword, not a string or a variable.

And also tried binding the entire command with PDO...

$range = 'DATE_SUB(NOW(), INTERVAL ' . DOWNLOAD_DAYS . ' DAY)';
...
AND date_added > :range
...
$stmt->bindParam(':range', $range, PDO::PARAM_STR);

.. But it's having none of that.

Any suggestions?

score 1 · Accepted Answer · answered Nov 09 '19 at 01:31

You can just build the entire query using the constant:

$sql = 'SELECT * FROM orders WHERE user_id = :user_id 
        AND status = :status
        AND date_added > DATE_SUB(NOW(), INTERVAL ' . DOWNLOAD_DAYS . ' DAY)
        ORDER BY date_added DESC';

or you can bind to just the interval value

$sql = 'SELECT * FROM orders WHERE user_id = :user_id 
        AND status = :status
        AND date_added > DATE_SUB(NOW(), INTERVAL :interval DAY)
        ORDER BY date_added DESC';
$stmt->bindValue(':interval', DOWNLOAD_DAYS, PDO::PARAM_INT);

MYSQL inserting INTERVAL DAY number dynamically?

1 Answers1