我有一个包含许多表的数据库。在第1面,我有一个按钮,可以把我转到第2面。在第2方面,我希望代码删除每个命名表中的每一行,该表具有我存储在变量中的特定pid
。我在第2面有一个代码,但它什么都不做。
第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>";
}
使用mysqli_multi_query
$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>";
}
您可以考虑更改存在pid
的mysql表,将该字段作为外键,在删除时级联。类似的东西
ALTER TABLE `answer_det`
ADD FOREIGN KEY (pid)
REFERENCES table_where_pid_is_the_primary_key (pid)
ON DELETE CASCADE;
将CCD_ 3替换为其他表名以在其中创建键。如果您没有更改表的权限,那么您的查询很可能会失败,因为您缺少分隔它们的分号:
$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';";
或者,您可以只运行一个带有联接的查询,类似于以下内容:
$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中删除多个表?
简化您的查询,如下所示
$pid3 = $_POST['pid2'];
$del= DELETE FROM answer_det 't1',project 't2',question 't3' USING t1, t2,t3 WHERE t3.pid = t2.pid and t1.pid = t2.pid AND t1.pid = $pid3
$run = mysqli_query($mysqli,$del);
if($run)
{
echo "<h1>Project is deleted!</h1>";
}
请参阅。从多个表中删除行
有一个很好的解决方案:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
查询末尾缺少分号。您需要输入";"以便一起执行多个查询。
试试这个:
$pid3="1";
$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';";
echo $del