How to use prepared statements in this query?

问题:

I'm new to PHP and PDO, and I try to use prepared statements here. After 1 hour of trying around I give up. Or my tutorial was just horribly bad.

EDIT:

This works perfectly without prepared statements:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
    $prepared = $dbh->prepare('SELECT * from sys_navigation_point WHERE name="root"');
    //$prepared->bindParam('foo', 'root');

    $prepared->execute();

    foreach($prepared as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

But this does not work at all with a prepared statement. Getting a totally blank page when doing this:

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');
    $prepared = $dbh->prepare('SELECT * from sys_navigation_point WHERE name=:foo');
    $prepared->bindParam('foo', 'root');

    $prepared->execute();

    foreach($prepared as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

foo should be replaced with root. However, it doesn't.

回答1:

You can't use params for stuff like table and column names, it's meant to be used for data only, not for fully dynamic queries

This should work:

$prepared = $dbh->prepare('SELECT * from sy_navigation_point WHERE Foo=:whatever');
$prepared->bindParam('whatever', 'Bar');

EDIT: This should be the real solution.

By looking at the documentation, it's clear that the pattern has to be:

$prepared = $dbh->prepare('SELECT * from sy_navigation_point WHERE Foo=:whatever');
$prepared->bindParam('whatever', $value);

Then you do:

$value = 'Bar';
$prepared->execute();

回答2:

Try using the colon in the name, too while binding:

$prepared->bindParam(':foo', 'root');

As it is done in the docs: http://php.net/manual/en/pdostatement.bindparam.php

回答3:

Your bindParam's second parameter has to be a variable, otherwise you'll get a fatal error. So,

$value='root';
$prepared->bindParam('foo', $value);

or:

$prepared->bindValue('foo', 'root');

It's easy to figure out when error messages are displayed:

if ($in_development) ERROR_REPORTING(E_ALL);
// ... code

回答4:

http://www.php.net/manual/en/pdo.prepare.php A commenter there says that it doesn't work properly for keywords, table names, view names and field names So you'd need $prepared = $dbh->prepare('SELECT * from ' . $table);

As it only really works for column variables.

回答5:

You cannot bind a table in a MySQL prepared statement, you can only bind values. From the manual:

However, they are not allowed for identifiers (such as table or column names), or to specify both operands of a binary operator such as the = equal sign.