PHP PDO Query isn't reading bind values

Question

So I'm trying to execute the following sql query:

$stmt = $connect->query("SELECT `FID`,`StorageID`,`DestructionDate` FROM `files` WHERE `DestructionDate` < ':date'");
$stmt->bindValue(":date",$date);
$stmt->execute();

while ($row = $stmt->fetch()) {
$fid = $row['FID'];
echo  $fid . " ";
}

The above code will return all records from files, it simply ignores the WHERE statement at all, and just to be clear, when I run the same statement on phpMyAdmin it runs just fine, in fact I even tried binding the value inside the query itself like this

$stmt = $connect->query("SELECT FID,StorageID,DestructionDate FROM files WHERE DestructionDate < '$date'");

And the query was executed correctly and only gave me the records that satisfy the WHERE condition, so the error is definitely in the bindValue() and execute() lines.

The single quotes are for the statement itself, The original statement would be something like: SELECT FID,StorageID,DestructionDate FROM files WHERE DestructionDate < '2018-10-30 13:00' The code didn't run without them — user2196985
– user2196985, Commented Oct 30, 2018 at 11:04
For prepared statements you do not and should not have quotes surrounding the placeholders, :date — Jay Blanchard
– Jay Blanchard, Commented Oct 30, 2018 at 11:30

Álvaro González · Accepted Answer · 2018-10-30 11:37:10Z

4

From docs:

PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object

You possibly want PDO::prepare() followed by PDOStatement::execute(). (There's normally no need to painfully bind params one by one.)

Additionally, you have bogus quotes around the placeholder:

':date'

You'll note that as soon as you execute the statement because params won't match.

edited Oct 30, 2018 at 11:37

answered Oct 30, 2018 at 10:58

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2196985 Over a year ago

You're correct I fixed it, but the issue remains, it's still returning all the records, here's the edited code $stmt = $connect->prepare("SELECT FID,StorageID,DestructionDate FROM files WHERE DestructionDate < '?'"); $stmt->execute(array($date));

Álvaro González Over a year ago

Try ? instead of '?'. You want the param's value, not a literal quote.

user2196985 Over a year ago

Oh finally it worked, Thanks so much for your answer!

FragBis · Accepted Answer · 2018-10-30 11:12:16Z

1

2 solutions :

First:

$stmt = $connect->prepare("SELECT `FID`,`StorageID`,`DestructionDate` FROM `files` WHERE `DestructionDate` < :date");
$stmt->execute(array('date' => $date);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Second:

$stmt = $connect->prepare("SELECT `FID`,`StorageID`,`DestructionDate` FROM `files` WHERE `DestructionDate` < ?");
$stmt->execute(array($date));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

In both cases, you don't need to 'quote' the string to be replaced (:date or ?) because PDO parse the value in the right type corresponding to the column to match.

answered Oct 30, 2018 at 11:12

FragBis

736 bronze badges

1 Comment

user2196985 Over a year ago

Thank you for the detailed answer! :)

Collectives™ on Stack Overflow

PHP PDO Query isn't reading bind values

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related