CODEWITHSUNDEEP
phpsqlsqlitepdo
Nathan Osman
Nathan Osman

Reputation: 73195

Why is this SQL SELECT statement not retrieving the row I just inserted?

I am creating an in-memory SQLite database and inserting some a row into it. Unfortunately, I am unable to select the row I just inserted.

<?php

const CACHE_SCHEMA = <<<EOD

CREATE TABLE test ( column1 TINYTEXT,
                    column2 MEDIUMTEXT,
                    column3 INT,
                    column4 INT )

EOD;

$db = new PDO('sqlite::memory:');

$statement = $db->query(CACHE_SCHEMA);
$statement->execute();

$statement = $db->prepare('INSERT INTO test (column1, column2, column3, column4) VALUES (?,?,?,?)');
$statement->execute(array('a', 'b', 123, 2));

$statement = $db->prepare('SELECT column2 FROM test WHERE column1 = ? AND column3 + column4 >= ?');
$statement->execute(array('a', 124));

var_dump($statement->fetch(PDO::FETCH_ASSOC));

?>

Output: bool(false)

As you can see, the 'SELECT' statement is not returning the row I inserted:

Upvotes: 2

Views: 128

Answers (1)

Nathan Osman
Nathan Osman

Reputation: 73195

It took a bit of thinking to figure this one out, but according to the documentation for PDOStatement::execute():

"All values are treated as PDO::PARAM_STR."

So I need to change:

$statement->execute(array('a', 124));

to:

$statement->bindValue(1, 'a');
$statement->bindValue(2, 124, PDO::PARAM_INT);
$statement->execute();

...and then instead of binding a string value to the last parameter, it will bind an integer.

Upvotes: 2

Related Questions