PHP Postgres Using Special Input Values With Intervals

Question

I'm trying to select a date within the time period of one day. A simple query like this will do the trick:

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

My problem is I am using prepared statements with pg_execute/PDO(the problem is the same in both). For example:

$query = "SELECT * 
FROM table_name
WHERE table_name.the_date > $1";

    $params = array('DATE_SUB(CURDATE(), INTERVAL 1 DAY)')

    $results = pg_prepare($dbconn, '' , $query );

    $results = pg_execute($dbconn, '', $params);

The result is that it always errors out with:

pg_execute(): Query failed: ERROR: invalid input syntax for type timestamp:

Now I now there is special postgres functions that can be used as indicated in this link: http://www.postgresql.org/docs/9.0/static/datatype-datetime.html#AEN5582 . Now I've replaced NOW() with the special input 'now' , but there is still an error with the 'INTERVAL 1 DAY' .

How can I get around this?

Mureinik Mureinik · Accepted Answer · 2013-09-29T15:13:36

I don't get why you'd want to use bind variables here.

You can simply do:

$query = "SELECT * 
          FROM table_name
          WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)";

$params = array();
$results = pg_prepare($dbconn, '' , $query);
$results = pg_execute($dbconn, '', $params);

If you want the ability to change the interval, you must still hard-code any syntax elements, although you should be able to dynamically configure the size of the interval.

$query = "SELECT * 
          FROM table_name
          WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL $1 DAY)";

$params = array('1');
$results = pg_prepare($dbconn, '' , $query);
$results = pg_execute($dbconn, '', $params);

PHP Postgres Using Special Input Values With Intervals

1 Answers