ALTER TABLE with PDO and parameters?

Question

Is it possible to add parameters with ALTER TABLE with PDO.

I have tried,

$q = $dbc -> prepare("ALTER TABLE emblems ADD ? TINYINT(1) UNSIGNED NOT NULL DEFAULT '0', ADD ? DATETIME NOT NULL");
$q -> execute(array($emblemDB, $emblemDB . 'Date'));

But it failed.

Thanks.

Answer 1

The nature of alter table queries, to my knowledge are not prepared statements. But you should call the beginTransaction and commit() functions for most table altering queries.

$dbh->beginTransaction();

/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
SET name = 'hamburger'");
$sth = $dbh->exec("ALTER TABLE `dessert` ADD `field1` VARCHAR(24) NOT NULL");


/* Commit changes */
$dbh->commit();

Although you could use the prepared statments and execute as far as I know.

NOTE:
MySQL implicitly calls the commit() function on CREATE TABLE and DROP TABLE queries, so rollback is not possible.

Also, if you're wanting to pass variables to an alter table query, make sure to sanitize your user input (if that's where it's coming from), and create a stored procedure on your db, then call it using PDO and attach your variables for inout. Just a thought in regards to how your question was worded.