Error binding PDO query in PHP

Question

I am having a bit of a problem binding a value with PDO and PHP. When I replace the values with variables the script returns the proper result but for some reason I can't get this to work.

// Connect to database in function on different page
function db1() {
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=**********", '**********', '**********');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
        return $pdo;
        $pdo = null;
    } catch (PDOException $e) {
        die($e->getMessage());
    }
}

// Scrit with problems
$db = db1();
$query = $db->prepare("SELECT column1, column2, column3, column4 FROM table_name WHERE (column4=2 OR column4=3) AND column5=:col1 AND column6=:col2"); // Prepare the statement to prevent any SQL injection
    $query->bindValue(':col1', $var1, PDO::PARAM_INT);
    $query->bindValue(':col2', $var2, PDO::PARAM_STR);
    $query->execute();
// I also tried the following.
// $query->execute(array(':col1' => $var1, ':col2' => $var2));

// Fetch result row
$row = $query->fetch(PDO::FETCH_ASSOC);

Any ideas?

UPDATE Did var_dump before and after the query. var1 = string(1) "2" and var2 = string(1) "1"

Removed $pdo = null;

See [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) for how to get an error message from PDO. — Pekka, Oct 11 '12 at 18:06
"Can't get this to work" isn't a valid error message. What's not working exactly? — Madara's Ghost, Oct 11 '12 at 18:06
The extra $ is an error when i rewrote the text here. i'll fix the code in 2 seconds — Konrad Margiel, Oct 11 '12 at 18:09
and I'll read the "how to squeeze error....." and see what I can come up with — Konrad Margiel, Oct 11 '12 at 18:09
Yes, when I rewrote the code into stackoverflow I accidently typed in the $. The code can connect to the database no problem. When I print_r the variable $query before and after the execute function I get the the following. PDOStatement Object ( [queryString] => SELECT column1, column2, column3, column4 FROM table_name WHERE (column4=2 OR column4=3) AND column5=:col1 AND column6=:col2 ) — Konrad Margiel, Oct 11 '12 at 18:20
Uh, you say "`...AND column5=:col1 AND column6=col2`". There is a colon missing there, before `col2`. Is that a typo, or...? — LSerni, Oct 11 '12 at 18:21
Remove the `$pdo = null;` after your `return`. What are the `var_dump` from $var1 and $var2? Can you add those to your question. — PenguinCoder, Oct 11 '12 at 18:22
I have 4 vars. var_dump returns values for all strings. the strings used are "2" and "1" so they do display. string(4) "user" string(1) "2" string(1) "1" NULL — Konrad Margiel, Oct 11 '12 at 18:27

LSerni · Accepted Answer · 2012-10-11T18:58:10.217

Except for the $pdo = null line which is after a return and therefore never executed, and the $db = $db1() where the function is used as a variable, the code hasn't anything obviously wrong. Since you say that replacing the bindings with variables work, I assume the last is a typo.

So I suggest you try and var_dump what the values of $var1 and $var2 are at that point in the script.

UPDATE

I have duplicated the setup like this

<?php

    $db = new PDO("mysql:host=localhost;dbname=test");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $var1 = 1; // See answer text below
    $var2 = 2;

    $query = $db->prepare("SELECT * FROM test WHERE column5=:col1 AND column6=:col2");
    $query->bindValue(':col1', $var1, PDO::PARAM_INT);
    $query->bindValue(':col2', $var2, PDO::PARAM_STR);
    $query->execute();
    $row = $query->fetch(PDO::FETCH_ASSOC);
    print_r($row);
?>

Then I created a first table like this

CREATE TABLE test ( column5 INTEGER, column6 INTEGER );

and then like this

CREATE TABLE test ( column5 varchar(5), column6 varchar(5) );

and I tried defining $var1 and $var2 as 1,2 (integer) and "1","2" (strings).

It always worked, i.e., I expected to get a single row as output, and I did:

Array
(
   [column5] => 1
   [column6] => 2
)

At this point I think the only possibility left is something strange in the database content.

Therefore I also expect the test below, on your system, not to work, i.e. not to return the desired row:

$query = $db->prepare("SELECT column1, column2, column3, column4 FROM table_name WHERE (column4=2 OR column4=3) AND column5=2 AND column6='1'");
$query->execute();
$row = $query->fetch(PDO::FETCH_ASSOC);
var_dump($row);

right before I start the $query->db line I echo out the var1 and var2 and they both display — Konrad Margiel, Oct 11 '12 at 18:18
And they do contain an integer and a string? Did you use `var_dump`? (I had "echo" play tricks on me in the past...). — LSerni, Oct 11 '12 at 18:18
I have 4 vars. var_dump returns values for all strings. the strings used are "2" and "1" so they do display. string(4) "user" string(1) "2" string(1) "1" NULL — Konrad Margiel, Oct 11 '12 at 18:28
Excuse me: one of the strings you used is NULL? Is that the NULL value (PDO::PARAM_NULL) or a four-byte string with "NULL" in it? — LSerni, Oct 11 '12 at 18:35
the NULL on the 4th variable is never entered or queried into the database — Konrad Margiel, Oct 11 '12 at 18:36
Turns out I mixed up the OR variables in the SQL... I feel like a fool. Thank you for all the help. — Konrad Margiel, Oct 11 '12 at 19:10

Error binding PDO query in PHP

1 Answers1