PHP/MySQL : How to delete all data from database which contains specific id in one query

Question

In my database i have 32 tables, and most of the tables has contain same ids. How can I delete all the data in the whole database that contains only an specific ID in one query(I mean each tables that contain that specific id).

DELETE * from (ALL TABLES) where id = 3;

There's no shortcut. Could you be more specific about your use-case? — 1000111, Sep 24 '16 at 08:20
The only at least a little bit viable way would be to do `DELETE * FROM table1, table2, table3, etc WHERE table1.id = 3, table2.id = 3, table3.id = 3, etc;` — icecub, Sep 24 '16 at 08:25
I tried inner join it works but yes can't find a shortcut way — Echoing Throughout Tel Numara, Sep 24 '16 at 08:27
Handling it in DB?: If you know this is always the case, you can set a table to be the "master" one. All the others with have FK to it along with `ON DELETE CASCADE`. Then you only delete the row from the master [(example)](http://stackoverflow.com/questions/4839905/mysql-delete-from-multiple-tables-with-one-query) — urban, Sep 24 '16 at 08:33

Rohan Khude · Answer 1 · 2016-09-24T19:00:54.067

Good question. Query might be optimal solution for this.

I have give solutions using PHP here below as PHP tag specified by you

<?php
require("db_connect.php");
$tables = mysqli_query($con, "SHOW TABLES FROM dbname");
while ($row = mysqli_fetch_assoc($tables)) {
    $table_name = $row["Tables_in_dbname"];
    mysqli_query($con, "DELETE FROM $table_name WHERE `id`=3");
}
?>

OR

create one more table where you make daily entries for id which needed to delete from all tables and everyday you might get the different list of id to delete from all tables.

Below here i have created a table ids_to_delete where i specified list of ids to be deleted.

<?php
require("db_connect.php");
//get ids from table where specified ids to be deleted
$ids     = mysqli_query($con, "SELECT `id` FROM `ids_to_delete`");
$id_list = '';
//create ids list like 1,4,3,9,5,6,...
while ($row_id = mysqli_fetch_assoc($tables)) {
    $id_list .= $row_id['id'] . ',';
}
$id_list = trim($id_list, ',');

$tables = mysqli_query($con, "SHOW TABLES FROM dbname");
while ($row = mysqli_fetch_assoc($tables)) {
    $table_name = $row["Tables_in_dbname"];
    mysqli_query($con, "DELETE FROM $table_name WHERE `id` IN ($id_list)");
}
//clear the ids_to_delete table
mysqli_query($con,"DELETE FROM `ids_to_delete`");
?>

Happy to help. If this answer or any other one solved your issue, please mark it as accepted. — Rohan Khude, Sep 25 '16 at 08:58

PHP/MySQL : How to delete all data from database which contains specific id in one query

1 Answers1