Can I use placeholders for tables and columns in PDO?

If I try this:-

$sql = $db->prepare("DELETE FROM :table WHERE :col = :id") ;
$sql->execute(['table' => 'TableName', 'col' => 'id', 'id' => $id]) ;

it fails, I think because it puts the table and column names in ‘quotes’.
Is there a way to do this?
The idea is I execute the query twice, to delete an entry from two tables.
Or maybe I’m going about this the wrong way, and should use JOIN or something to delete from the main table and the LUT in one query?

Unfortunately, you can’t.

Thus, old good manual formatting is the only way with vanilla PDO. Note that identifier formatting rules are different from string formatting!

In your case I’d just run two queries.

$db->prepare("DELETE FROM table1 WHERE col = ?")->execute([$id]);
$db->prepare("DELETE FROM table2 WHERE col2 = ?")->execute([$id]);

See - it is not as much writing as one would want to get rid of.

If you still want to make this code as concise as possible, look towards an ORM, with which it will be just

Table1::find($id)->delete();
Table2::find($id)->delete();

You may want to take a look at Eloquent ORM at this chapter from the excellent Code Bright by Dayle Rees,

1 Like

I had it working with two prepare lines and two execute lines. I thought maybe I could lose one prepare line by doing that.
But formatting like you have will reduce it to just two lines.

Yes, luckily for the DML queries that do not return any data you can use method chaining.
Unfortunately, for the SELECT queries you can’t.

If the tables are connected correctly then you should be able to use one cascading query.

Connect the tables with a DELETE CASCADE foreign key and DELETE only from one (parent) table (what @felgall suggested - best solution). If it is impossible then simply execute two separate queries in a transaction.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.