[RESOLVED] PDO execute with prameter array failing

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

druidmatrix

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

$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"; 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!

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";
            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)
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......
                    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!