How to use prepared statements in this query?

Question

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.

Answer 1

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.

Answer 2

You can't use params for stuff like table and column names, it's meant to be used for values, to prevent SQL Injections in values.

This should work:

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

Answer 3

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.

Answer 4

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

Answer 5

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