Delete data from multiple mysql tables

Question

tableOk so what I want to do is the following

I have multiple tables on one database in mysql

database1

table1

table2

table3

table4

table5

each table has a column id

for every id matched i want to delete that row. so delete 954001 row in every table. What is the best way to do this WITHOUT killing my database. Btw all ids match across the board.

<?php
// get value of id that sent from address bar
$customer_id=$_GET['id'];
$id=$_GET['id'];
// Delete data in mysql from row that has this id
$sql="DELETE FROM teable_users WHERE customer_id = $customer_id;
DELETE FROM table_parties WHERE id = $id;
DELETE FROM table_weddings WHERE id = $id;
DELETE FROM table WHERE id = $id;
DELETE FROM table_request_client WHERE id = $id;
DELETE FROM table_requests WHERE id = $id;
DELETE FROM table_users WHERE id = $id;";

$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='text.php'>Back to Event    Manager</a>";
}

else {
echo "ERROR";
}

// close connection
mysql_close();

?>

Answer 1

You need a DELETE statement for each table. You could use a transaction if you are worried about consistency.

BEGIN TRANSACTION;
DELETE FROM table1 WHERE id = 1;
DELETE FROM table2 WHERE id = 1;
DELETE FROM table3 WHERE id = 1;
DELETE FROM table4 WHERE id = 1;
DELETE FROM table5 WHERE id = 1;
COMMIT;

PHP example using PDO (not tested)

$id = (int) $_GET['id'];
$pdo->beginTransaction();
$st = $pdo->prepare('DELETE FROM table1 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table2 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table3 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table4 WHERE id = :id');
$st->execute(array(':id', $id));
$st = $pdo->prepare('DELETE FROM table5 WHERE id = :id');
$st->execute(array(':id', $id));
$pdo->commit();

Answer 2

You could write a trigger with the deletes happening on the initial delete

Answer 3

I think essentially you'll have to do separate statements for each table.

This forum may be of interest to you:

http://forums.devshed.com/database-management-46/one-query-to-delete-from-multiple-tables-71959.html

Something like this, I believe -- although my SQL is rusty.

delete from table1 where id='954001';