I'm writing a class that programmatically creates a table given certain column names. I'm using PDO prepared statements which seem to be causing some problems.
Here's the basic procedure:
// create a query string to be sent to $pdo->prepare
$sql =
'CREATE TEMP TABLE :tmp_table_name (
:person_id bigint,
:encntr_id bigint,
:prsnl_id bigint,
:program_detail text,
:program_name text
);';
$stmt = $pdo->prepare($sql);
// Bind table name
$stmt->bindValue(':tmp_table_name', 'tmp_patients', PDO::PARAM_STR);
// Bind column names
$columnNames = [
'person_id',
'encnt_id',
'prsnl_id',
'program_detail',
'program_name',
];
foreach($columnNames as $name) {
$stmt->bindValue(':'.$name, $name, PDO::PARAM_STR);
}
$ret = $stmt->execute();
// $ret is false! The statement fails.
Here's what $stmt->errorInfo()[2]
displays:
ERROR: syntax error at or near "$1"
LINE 1: CREATE TEMP TABLE $1 ($2 bigint,
$3 bigint,
$4 bigint,
$5 text,
$6 te...
Why are $1, $2, $3, etc. showing up in the query? Do you have any tips on how to further debug this PDO statement?
Update I'm trying a different approach, not using bindParam, just passing in an array of params to $stmt->execute(). I'm still getting the same error, though...
$stmt = $this->pdo->prepare($this->createSql);
$keys = [];
// Bind column names
foreach($this->columnNames as $name) {
$keys[] = ':'.$name;
}
$params = array_combine($keys, $this->columnNames);
// Bind table name
$params[':tmp_table_name'] = 'tmp_'.$this->objName;
$ret = $stmt->execute($params);
if (!$ret) {
throw new Exception('execSchema() failed! '. $stmt->errorInfo()[2]);
}
It looks to me as if you are trying to bind to the table name/columns, which you cannot do using PDO - you can only bind values to placeholders.
as seen in your code here:
It feels as though it should work, but it does not.
eg this will not work:
However, something like this would work, which should proffer your solution or something like it: