[RESOLVED] PDO execute with prameter array failing

druidmatrix

I am having a strange problem using PDO with parameters being passed in - the execute fails with the following errorInfo:

<?php

    $query = "select user_id from USERS where username='?'";
    $params = array('root');

    try {
            $pdo = new PDO('mysql:host=localhost;dbname=dbname', 'db_user', 'db_pass');
    }
    catch (PDOException $e) {
            die ('PDO Connect Error (' . $e->getMessage() . ') '. "\n");
    }

    $stmt = $pdo->prepare($query);
    if (!$stmt)
            echo "Failed to prepare statement: (". print_r($pdo->errorInfo()).")\n";

    $stmt->execute($params);
    echo "Issueing SQL prepared statement\n";
    $stmt->debugDumpParams()."\n";
    if ($stmt->errorCode()) {
            echo "Failed to execute statement: \n=======\n";
            $arr = $stmt->errorInfo();
            print_r($arr);
            echo "\n========\n";
    }
    if ($row = $stmt->fetch(PDO::FETCH_ASSOC))
                    echo "Query Result: ".print_r($row)."\n";
    else
            echo "Query failed\n";

?>

And the run results:

Issueing SQL prepared statement
SQL: [44] select user_id from USERS where username='?'
Params: 1
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=2

Failed to execute statement:

Array
(
[0] => 00000
)

========
Query failed

I am new to PHP and would be grateful for any help from the experts out there!

halojoy

Are you sure you have a username = 'root'

errorCode() does not return false if no error
It always returns an array of 5 digits number
'00000' is most probably the errorCode when is no error

$stmt->execute()
will return TRUE on success or FALSE

I would change to this:

<?php

    $query = "select user_id from USERS where username='?'";
    $params = array('root');

    try {
            $pdo = new PDO('mysql:host=localhost;dbname=dbname', 'db_user', 'db_pass');
    }
    catch (PDOException $e) {
            die ('PDO Connect Error (' . $e->getMessage() . ') '. "\n");
    }

    $stmt = $pdo->prepare($query);
    if (!$stmt)
            echo "Failed to prepare statement: (". print_r($pdo->errorInfo()).")\n";

    $success = $stmt->execute($params);

    echo "Issueing SQL prepared statement\n";
    $stmt->debugDumpParams()."\n";

    if (!$success) {
            echo "Failed to execute statement: \n=======\n";
            $arr = $stmt->errorInfo();
            print_r($arr);
            echo "\n========\n";
    }
    if ($row = $stmt->fetch(PDO::FETCH_ASSOC))
                    echo "Query Result: ".print_r($row)."\n";
    else
            echo "Query failed\n";

?>

druidmatrix

Thank you for your reply halojoy. Yes I have the root user in my DB. On CLI:

mysql> select user_id from USERS where username='root';
+---------+
| user_id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

halojoy

Good.

It is a mystery. I think your code should work.

One little detail.
There is no need to escape '?' in this code from PHP Manual.
But I hardly think this would hurt, like you have done.

<?php

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));

?>

druidmatrix

I should have also mentioned tried changing the code to look for the boolean return from execute as you suggested, and running the modified code you posted - same results, except the errorInfo output is not printed:

Issueing SQL prepared statement
SQL: [44] select user_id from USERS where username='?'
Params: 1
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=2
Query failed

I am curious why the errorCode is a single element array - according to the docs there should be a driver specific code and error message as well.

To make sure that it is actually connecting to the DB, I tried a bad password, and as expected I get:

PDO Connect Error (SQLSTATE[28000] [1045] Access denied for user 'db_user'@'localhost' (using password: YES))

So I am pretty much at a loss on how to proceed......

druidmatrix

Holy Schmolly! You are right Halojoy....you are the Guru!!! 🙂

Yes - the issue is with the single ticks around the anonymous placeholder - so it works without the ticks:

Issueing SQL prepared statement
SQL: [42] select user_id from USERS where username=?
Params: 1
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=2

Failed to execute statement: Errorcode = 00000

Array
(
[0] => 00000
)

========
Array
(
[user_id] => 1
)
Query Result: 1

Thank you very much!

halojoy

Thanks.
So now I know this, too:
Put no quotes around ? in PDO SQL Statements
because they certainly can effect the result!