PDO execute with array returns null

Question

I'm trying to use good PDO as always and almost everything works but one query:

$primary = 'my_id';
$table = 'my_table';
// This or...
$statement = $this->conn->prepare("SELECT MAX(:id) AS id FROM :table");
        $statement->bindParam(':id', $primary, PDO::PARAM_STR);
        $statement->bindParam(':table', $table, PDO::PARAM_STR);
        $statement->setFetchMode(PDO::FETCH_ASSOC);
        $statement->execute();

// This one. Both doesn't work.

$statement = $this->conn->prepare("SELECT MAX(:id) AS id FROM :table");
        $statement->setFetchMode(PDO::FETCH_ASSOC);
$arr = array(
            ':id' => 'my_id',
            ':table' => 'my_table',
        );
        $statement->execute($arr);

These just return a null array. I feel so confused. So I have tried that:

$statement = $this->conn->prepare("SELECT MAX(".$primary.") AS id FROM ".$table);
        $statement->setFetchMode(PDO::FETCH_ASSOC);
        $statement->execute();

And it works. I feel like I'm missing something but can't figure it out. So clearly there's a problem with binding I tried different variations such as writing one of the variable manually, but no luck so far.

Thanks in advance for any help...

PLEASE read here http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter — Fabio, Aug 17 '14 at 01:41
@Fabio I never thought that. That was what I'm missing. Thanks a lot... — s.alem, Aug 17 '14 at 01:46
Is there a side effect or how bad would it be if I just check table or columnt name string with regex and use it like "SELECT * FROM $table"? — s.alem, Aug 17 '14 at 02:07

score -1 · Answer 1 · edited May 23 '17 at 12:20

-1

You can't use parameters as table names in PDO, so you will have to change this to avoid that. This is not a limitation of PDO, but a limitation of MySQL directly. The manual states that

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

Table and column names are identifiers, so using placeholders for them is not supported. See this question for an alternative method.

edited May 23 '17 at 12:20

Community

1
1

answered Aug 17 '14 at 01:41

Austin

2,982
2
28
36

No way for this to be an answer – Fabio Aug 17 '14 at 01:41
@Fabio: Why not? Even the accepted answer in the question you linked to as well says this … – CBroe Aug 17 '14 at 01:45
That's why I posted a comment and you an answer. Comments are not the same as answers. Anyway that answer explains why, you only said: `you can't use, read here` – Fabio Aug 17 '14 at 01:53
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Reto Koradi Aug 17 '14 at 02:12
@RetoKoradi How's that? – Austin Aug 17 '14 at 02:17

PDO execute with array returns null

1 Answers1