Geek Answers Handbook

How to insert things like "now () -interval" 2 minutes "into a PHP PDO request?

I have a query like this: (on Postgresql 8.4, PHP-fpm 5.3.10 (fpm-fcgi))

select * from users where now() - interval '2 minutes' < seenlast ORDER BY seenlast;

I would like to use a PHP / PDO request, therefore:

 $mymin=5; //this is a variable can be changed by $_GET $query = $db_conn->prepare("select * from users where now() - interval ':myminute minutes' < seenlast ORDER BY seenlast"); $query->bindParm(":myminute",$mymin) $query->execute;

This does not work, I can not find a way to pass the protocol correctly ( $ mymin ). If I programmed the timestuff hard, it means that the other part of the code must be correct.

I also tried:

 $temp=$mymin." minutes"; $query = $db_conn->prepare("select * from users where now() - interval :myminute < seenlast ORDER BY seenlast"); $query->bindParm(":myminute",$temp)

I already saw it, didn't help

+6
source share
4 answers

Intervals can be multiplied by numbers. So one approach to this is to prepare a statement saying interval '1 minute' * :myminutes instead, passing the parameter "myminutes" as a prime integer.

+11
source

I don’t know much PDO or PHP, but I think I know what is wrong here.

When you say this:

 interval '3 minutes'

Are you really doing the translation operation in the same way as:

 '3 minutes'::interval cast('3 minutes' as interval)

So what you do is the TEXT value for INTERVAL. This means that you need to create something similar to the string '3 minutes' . You can insert fragments of a string using string concatenation:

 # Use cast to make the precedence cleaner. $query = $db_conn->prepare("select * from users where now() - cast(:myminute || ' minutes' as interval) < seenlast ORDER BY seenlast"); $query->bindParm(":myminute", $mymin)

Or you should be able to do iterating over strings in PHP:

 $query = $db_conn->prepare("select * from users where now() - interval :myminute < seenlast ORDER BY seenlast"); $query->bindParm(":myminute", $mymin . ' minutes')
+5
source

I struggled with Phalcon and its internal PDO model parser for hours with the same problem.

I found this solution:

 public static function getTimedoutRequests($secsThreshold) { return self::find( array( // PDO is buggy here, can't use INTERVAL "DATE_PART('epoch', create_time) < DATE_PART('epoch', NOW()) - ?0", "bind" => array( $secsThreshold ) ) ); }
0
source 
 ... - INTERVAL :myminute MINUTES ...

without quotes is the correct method. If this helps, think of placeholders as the equivalent of using old-school query-building methods

 ... - INTERVAL $myminute MINUTES

except that placeholders take care of injection vulnerabilities that variables do not. Just because you use placeholders doesn't mean you can change the syntax of SQL, so

 ... - INTERVAL '2 minutes' ... - INTERVAL ':myminute minute'

invalid SQL.

followup for mu:

 mysql> select now() + interval '2 minute'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> select now() + interval 2 minute; +---------------------------+ | now() + interval 2 minute | +---------------------------+ | 2013-01-22 13:38:24 | +---------------------------+ 1 row in set (0.02 sec)
-2
source

More articles:


All Articles