我想在两个不同的表中插入两条不同的记录，并删除原始记录。像这个
$msgid = POST_['roll_id'];
$query = "INSERT INTO del_subscription SELECT * from subscription WHERE mem_id='$msgid'";
$query1 = "INSERT INTO del_user_data SELECT * from user_data WHERE mem1_id='$msgid'";
$query2 ="DELETE FROM subscription WHERE mem_id='$msgid'";
$query3 ="DELETE FROM user_data WHERE mem_id='$msgid'";
可以对此查询使用multi_query。
我使用了多个查询，但第一个查询只能工作
$msgid = POST_['roll_id'];
if (strlen($msgid) > 0)
{
$query = "INSERT INTO del_subscription SELECT * FROM subscription WHERE name='$msgid' AND renewal='yes';" ;
$query .= "INSERT INTO del_user_data SELECT * FROM user_data WHERE name='$msgid'AND wait='no';";
$query .= "DELETE FROM subscription WHERE name='$msgid' AND renewal='yes';" ;
$query .= "DELETE FROM user_data WHERE name='$msgid' AND wait='no'";
if (mysqli_multi_query($con, $query))
{
do {
/* store first result set */
if ($result = mysqli_store_result($con)) {
while ($row = mysqli_fetch_row($result)) {
echo "null";
}
if($result) { mysqli_free_result($result); }
}
/* print divider */
if (mysqli_more_results($con)) {
echo "<html><head><script>alert('Member Deleted');</script></head></html>";
echo "<meta http-equiv='refresh' content='0; url=view_mem.php'>";
}
} while (mysqli_next_result($con));
}
}
else {
echo "<html><head><script>alert('ERROR! Delete Operation Unsuccessful');</script></head></html>";
echo "<meta http-equiv='refresh' content='0; url=view_mem.php'>";
}
/* close connection */
mysqli_close($con);
在这里，我们希望运行Member Deleted一次，而不是四次，直到完成查询。
我完全重写了这个答案
由于很难将我写的内容拼接到你写的内容中，我会完整地使用这段代码，或者根本不使用。
free_result和fetch_row命令不应该存在。它们将导致错误，因为INSERT和DELETE查询没有结果集。
$msgid = $_POST['roll_id'];
if(strlen($msgid) > 0):
$queries = array();
$queries[] = "INSERT INTO del_subscription SELECT * FROM subscription WHERE `name`='".$msgid."' AND `renewal`='yes';";
$queries[] = "INSERT INTO del_user_data SELECT * FROM user_data WHERE `name`='".$msgid."' AND `wait`='no';";
$queries[] = "DELETE FROM subscription WHERE `name`='".$msgid."' AND `renewal`='yes';";
$queries[] = "DELETE FROM user_data WHERE `name`='".$msgid."' AND `wait`='no';";
// Set flag TRUE unless we find otherwise
$ALL_SUCCESSFUL = TRUE;
// Executes query and enters if the first query was successful.
if ( mysqli_multi_query($con, implode(' ',$queries)) ):
// This do-while tests if all other queries were successful
do
if( $result = mysqli_store_result($con) ):
if(!$result):
$ALL_SUCCESSFUL = FALSE ;
endif;
endif;
// Changes internal pointer to next result.
while( mysqli_next_result($con) );
endif; // If first query was successful
if( $ALL_SUCCESSFUL ):
echo showMessage('Member Deleted!');
else:
echo showMessage('ERROR! Delete Operation Unsuccessful');
endif;
else:
echo showMessage('ERROR! No roll_id given');
endif; // If roll_id is valid
/* close connection */
mysqli_close($con);
function showMessage($msg){
$html = '';
$html.= "<html><head>";
$html.= "<meta http-equiv='refresh' content='0' url='view_mem.php' />";
$html.= "<script>alert('".$msg."');</script>";
$html.= "</head></html>";
return $html;
}
如果您发现第一个查询执行了，但没有像前面所说的那样执行任何其他查询，那不是因为php代码。这是因为你写的查询。在mysqli_multi_query()中，所有查询都在同一个调用中执行，所以如果执行了一个查询，它们都被执行了。。。即使不是所有的都取得了成功。
基本上，这意味着multi_query之后的任何PHP代码都不会影响其他SQL查询的结果。我们基本上可以扔掉所有东西，只写这篇文章让它发挥作用：
$msgid = $_POST['roll_id'];
$queries = array();
$queries[] = "INSERT INTO del_subscription SELECT * FROM subscription WHERE `name`='".$msgid."' AND `renewal`='yes';";
$queries[] = "INSERT INTO del_user_data SELECT * FROM user_data WHERE `name`='".$msgid."' AND `wait`='no';";
$queries[] = "DELETE FROM subscription WHERE `name`='".$msgid."' AND `renewal`='yes';";
$queries[] = "DELETE FROM user_data WHERE `name`='".$msgid."' AND `wait`='no';";
mysqli_multi_query($con, implode(' ',$queries));
mysqli_close($con);
此外，对于table到table直接的INSERT查询，如您所写的那样使用SELECT *时定义的列，请仔细检查数据库，以确保这两个表具有相同的列结构，并且列字段匹配。但是，这不太可能破坏查询。
我有太多的缺陷，无法提供逐项的解决方案，所以我将提供重写，假设您的查询有效（我确信他们需要一些工作）：
if(isset($_POST['submit'])){
$msgid=$_POST['roll_id'];
if(strlen($msgid)>0){
$esc_msqid=mysqli_real_escape_string($con,$msgid);
$queries[] = "INSERT INTO del_subscription SELECT * FROM subscription WHERE `mem_id`='$esc_msqid' AND `renewal`='yes'";
$queries[] = "INSERT INTO del_user_data SELECT * FROM user_data WHERE `newid`='$esc_msqid' AND `wait`='no'";
$queries[] = "DELETE FROM subscription WHERE `mem_id`='$esc_msqid' AND `renewal`='yes'";
$queries[] = "DELETE FROM user_data WHERE `newid`='$esc_msqid' AND `wait`='no'";
// Executes query and enters if the first query was successful.
if(mysqli_multi_query($con,implode(';',$queries))){
do{
list($current_key,$current_query)=each($queries); //advances array pointer to first or next element
if(mysqli_affected_rows($con)<1){
$alert="Query Logic Error @ Query#$current_key with id=$msgid";
}
} while(mysqli_more_results($con) && mysqli_next_result($con));
}else{
list($current_key,$current_query)=each($queries); //advances array pointer to first element
}
if($error_mess=mysqli_error($con)){
$alert="Syntax Error @ Query#$current_key with id=$msgid"; // str_replace("'","''",$error_mess);
}
if(!$alert){
echo "<html><head><script>alert('Member Deleted');</script></head></html>";
echo "<meta http-equiv='refresh' content='0; url=view_mem_del.php'>";
}else{
echo "<html><head><script>alert('ERROR! Delete Operation Unsuccessful'n$alert');</script></head></html>";
echo "<meta http-equiv='refresh' content='0; url=view_mem.php'>";
}
}else{
showMessage('ERROR! No msgid given');
}
}else{
echo showMessage('ERROR! No roll_id given');
}
mysqli_close($con);
以下是数组的工作代码
if(isset($_POST['submit']))
{
$msgid = $_POST['roll_id'];
if(strlen($msgid) > 0):
$queries = array();
$queries[] = "INSERT INTO del_subscription SELECT * FROM subscription WHERE `mem_id`='".$msgid."' AND `renewal`='yes';";
$queries[] = "INSERT INTO del_user_data SELECT * FROM user_data WHERE `newid`='".$msgid."' AND `wait`='no';";
$queries[] = "DELETE FROM subscription WHERE `mem_id`='".$msgid."' AND `renewal`='yes';";
$queries[] = "DELETE FROM user_data WHERE `newid`='".$msgid."' AND `wait`='no';";
// Set flag TRUE unless we find otherwise
$ALL_SUCCESSFUL = TRUE;
// Executes query and enters if the first query was successful.
if ( mysqli_multi_query($con, implode(' ',$queries)) ):
// This do-while tests if all other queries were successful
do
if( $result = mysqli_store_result($con) ):
if(!$result):
$ALL_SUCCESSFUL = FALSE ;
endif;
endif;
// Changes internal pointer to next result.
while( mysqli_next_result($con) );
endif; // If first query was successful
if( $ALL_SUCCESSFUL ):
echo "<html><head><script>alert('Member Deleted');</script></head></html>";
echo "<meta http-equiv='refresh' content='0; url=view_mem_del.php'>";
else:
echo "<html><head><script>alert('ERROR! Delete Operation Unsuccessful');</script></head></html>";
echo "<meta http-equiv='refresh' content='0; url=view_mem.php'>";
endif;
else:
echo showMessage('ERROR! No roll_id given');
endif; // If roll_id is valid
/* close connection */
mysqli_close($con);
}
这给出了我们所期望的的确切结果