Volviendo al tema de PHP, para ejecutarlo, deben ser llamadas a la base con cada sentencia SQL como una ejecución independiente.
Ahora bien, como un proceso de multiples selects puede fallar por razones variadas, y dejar los datos inconsistentes, es conveniente que tal secuencia de borrados sólo se aplique a bases de datos con tablas de tipo InnoDB, a fin de implementar transacciones.
En ese contexto, la cosa podría ser mas o menos así (siempre usando la librería MYSQLI y no la librería MYSQL):
Código PHP:
<?php
$db = new mysqli($host, $user, $pass);
$db->select_db("tuBase");
$qry = "START TRANSACTION;";
$result = $db->query($qry);
//Deletes achievements from non-existant users
$qry = "DELETE FROM user_achievements WHERE \n";
$qry .= "user_achievements.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id =user_achievements.user_id);";
$result = $db->query($qry);
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes effects from non-existant users
$qry = "DELETE FROM user_effects WHERE \n";
$qry .= "user_effects.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id =user_effects.user_id);";
$result = $db->query($qry);
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes badges from non-existant users
$qry = "DELETE FROM user_badges WHERE \n";
$qry .= "user_badges.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id =user_badges.user_id);";
$result = $db->query($qry);
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes favorites from non-existant users
$qry = "DELETE FROM user_favorites WHERE \n";
$qry .= "user_favorites.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_favorites.user_id);";
$result = $db->query($qry);
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes favorites from non-existant rooms
$qry = "DELETE FROM user_favorites WHERE \n";
$qry .= "user_favorites.room_id NOT IN \n";
$qry .= "(SELECT id FROM rooms where rooms.id = user_favorites.room_id);";
$result = $db->query($qry);
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes info from non-existant users
$qry = "DELETE FROM user_info WHERE \n";
$qry .= "user_info.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_info.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes pets from non-existant users
$qry = "DELETE FROM user_pets WHERE \n";
$qry .= "user_pets.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_pets.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes pets from non-existant rooms
$qry = "DELETE FROM user_pets WHERE \n";
$qry .= "user_pets.room_id NOT IN \n";
$qry .= "(SELECT id FROM rooms where rooms.id = user_pets.room_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes non-existant presents
$qry = "DELETE FROM user_presents WHERE \n";
$qry .= "user_presents.item_id NOT IN \n";
$qry .= "(SELECT id FROM items where items.id = user_presents.item_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes quests from non-existant users
$qry = "DELETE FROM user_quests WHERE \n";
$qry .= "user_quests.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_quests.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes roomvisits from non-existant users
$qry = "DELETE FROM user_roomvisits WHERE \n";
$qry .= "user_roomvisits.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_roomvisits.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes roomvisits from non-existant rooms
$qry = "DELETE FROM user_roomvisits WHERE \n";
$qry .= "user_roomvisits.room_id NOT IN \n";
$qry .= "(SELECT id FROM rooms where rooms.id = user_roomvisits.room_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes stats from non-existant users
$qry = "DELETE FROM user_stats WHERE \n";
$qry .= "user_stats.id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_stats.id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes subscriptions from non-existant users
$qry = "DELETE FROM user_subscriptions WHERE \n";
$qry .= "user_subscriptions.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_subscriptions.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes tags from non-existant users
$qry = "DELETE FROM user_tags WHERE \n";
$qry .= "user_tags.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_tags.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes wardrobe from non-existant users
$qry = "DELETE FROM user_wardrobe WHERE \n";
$qry .= "user_wardrobe.user_id NOT IN \n";
$qry .= "(SELECT id FROM users where users.id = user_wardrobe.user_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes non-existant wired_items
$qry = "DELETE FROM wired_items WHERE \n";
$qry .= "wired_items.item_id NOT IN \n";
$qry .= "(SELECT id FROM items where items.id = wired_items.item_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes non-existant teleports
$qry = "DELETE FROM tele_links WHERE \n";
$qry .= "tele_links.tele_one_id NOT IN \n";
$qry .= "(SELECT id FROM items where items.id = tele_links.tele_one_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
//Deletes non-existant teleports
$qry = "DELETE FROM tele_links WHERE \n";
$qry .= "tele_links.tele_two_id NOT IN \n";
$qry .= "(SELECT id FROM items where items.id = tele_links.tele_two_id);";
if(!$result)
{
$qry = "ROLLBACK;";
$result = $db->query($qry);
return;
}
// Y así sucesivamente todas y cada una de las sentencias
// para cerrar finalmente con una única llamada a confirmar la transaccion
$qry = "COMMIT;";
$result = $db->query($qry);
return;
?>
¿Se entiende?
Lo que tiene que quedar claro es que una operacion como la que quieres hacer no es buena idea si usas tablas MyISAM, ya que este motor de tablas de MySQL no admite transacciones. En ese caso el borrado se complica y mucho...