在查询中使用MONTH（）时，PDO无法正常工作

Question

I'm trying to execute a PDO query, but it returns zero results. But when I run the query in MySQL, then it returns two rows. This issue only occurs when I add MONTN(column) in the PDO query.

What I have in PHP is this:

$sql = 'SELECT * FROM payment WHERE  `user_id` = :userid_53e3d3275e364  AND  MONTH(pay_date) = :monthpaydate_53e3d3275e373  ORDER BY `pay_date` DESC';

$bindings = array ( 'userid_53e3d3275e364' => 8, 
                    'monthpaydate_53e3d3275e373' => 'MONTH(CURDATE())' );


$stmt = $this->db->prepare ( $sql );
$stmt->execute ( $bindings );
$result = $stmt->fetchAll ( \PDO::FETCH_CLASS, $class, $ctorArgs );

With this code $result is empty.

When I paste in the binding value into the query manually and run it in phpMyAdmin, then I get two records back (which is what I expect):

SELECT * 
FROM payment
WHERE  `user_id` = 8
AND MONTH(pay_date) = MONTH(CURDATE()) 
ORDER BY `pay_date` DESC

And when I run the above PDO code WITHOUT the MONTH(CURDATE()), then PDO also returns records:

$sql = 'SELECT * FROM payment WHERE  `user_id` = :userid_53e3d3275e364 ORDER BY `pay_date` DESC';
$bindings = array ( 'userid_53e3d3275e364' => 8 );

$stmt = $this->db->prepare ( $sql );
$stmt->execute ( $bindings );
$result = $stmt->fetchAll ( \PDO::FETCH_CLASS, $class, $ctorArgs );

But for some reason something, somewhere seems to go wrong when I put MONTH(CURDATE()) as binding value.

Is there a way to solve this problem?

Answer 1

things like CURDATE() and NOW() must be present in the query, not in the values:

$sql = 'SELECT * FROM payment WHERE  `user_id` = :userid_53e3d3275e364  AND  MONTH(CURDATE()) = :monthpaydate_53e3d3275e373  ORDER BY `pay_date` DESC';

The reason for that is quite logical: parameters are passed so that they do not need to be evaluated by mySQL (values being evaluated by mySQL is a big security issue - sql injection). So in fact, when your query is acually executed, it compares your field to the characters "CURDATE()" and not to the result of the function, which is what you want. But! We just have to put it in the query at the very start.

moreover, the way you bind params lacks the :

$bindings = array ( ':userid_53e3d3275e364' => 8, 
                    ':monthpaydate_53e3d3275e373' => 'MONTH(CURDATE())' );

But I must say I don't understand this MONTH(CURDATE()) = :monthpaydate_53e3d3275e373

you sure you do not mean monthField = MONTH(CURDATE()) ??

I don't know your database schema, so monthField is actually you own column name