通过php运行许多mySQL查询

Question

我有一个有很多表的数据库。在第1面，我有一个按钮把我传递给第2面。在第2面，我希望代码删除每个命名表中的每一行，这些表中有我存储在变量中的特定pid。我在第二面有个密码，但它什么也做不了。

第2部分的代码：

$pid3 = $_POST['pid2'];

$del = "DELETE FROM answer_det WHERE pid='$pid3'";
$del .= "DELETE FROM project WHERE pid='$pid3'";
$del .= "DELETE FROM question WHERE pid='$pid3'";
$del .= "DELETE FROM respondent WHERE pid='$pid3'";
$del .= "DELETE FROM result WHERE pid='$pid3'";
$del .= "DELETE FROM users WHERE pid='$pid3'";

$run = mysqli_query($mysqli,$del);

if($run)
{
   echo "<h1>Project is deleted!</h1>";
}

Answer 1

使用查询

$pid3 = $_POST['pid2'];

$del = "DELETE FROM answer_det WHERE pid='$pid3';";
$del .= "DELETE FROM project WHERE pid='$pid3';";
$del .= "DELETE FROM question WHERE pid='$pid3';";
$del .= "DELETE FROM respondent WHERE pid='$pid3';";
$del .= "DELETE FROM result WHERE pid='$pid3';";
$del .= "DELETE FROM users WHERE pid='$pid3';";

$run = mysqli_multi_query($mysqli,$del);

if($run)
{
   echo "<h1>Project is deleted!</h1>";
}

Answer 2

在存在pid的情况下，您可能会考虑修改mysql表，以便将该字段作为外键，该外键将在删除时级联。有点像

ALTER TABLE `answer_det` 
ADD FOREIGN KEY (pid) 
REFERENCES table_where_pid_is_the_primary_key (pid)
ON DELETE CASCADE;

用其他表名替换answer_det，以便在那里创建键。如果您没有权限更改表，那么您的查询可能会失败，因为缺少分号来分隔它们：

$del = "DELETE FROM answer_det WHERE pid='$pid3';";
$del .= "DELETE FROM project WHERE pid='$pid3';";
$del .= "DELETE FROM question WHERE pid='$pid3';";
$del .= "DELETE FROM respondent WHERE pid='$pid3';";
$del .= "DELETE FROM result WHERE pid='$pid3';";
$del .= "DELETE FROM users WHERE pid='$pid3';";

Answer 3

或者，您可以使用joins运行一个查询，如下所示：

$pid3 = $_POST['pid2'];

$del = "DELETE a,b,c,d,e,f FROM answer_det a "
$del .= "JOIN project b on b.pid=a.pid "
$del .= "JOIN question c  on c.pid=a.pid "
$del .= "JOIN respondent d on d.pid=a.pid "
$del .= "JOIN result e on e.pid=a.pid "
$del .= "JOIN users f on f.pid=a.pid "
$del .= "WHERE a.pid='$pid3'";

$run = mysqli_query($mysqli,$del);

if($run)
{
   echo "<h1>Project is deleted!</h1>";

}

如何删除MySQL中的多个表？