代码之家 › 专栏 › 技术社区 › Fussionweb

如何使用multi_query插入和删除两个表的记录

multi-query mysql php

Fussionweb · 技术社区 · 11 年前

我想将两个不同的记录插入到两个不同表中,并删除原始记录。这样地

$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 一次而不是四次,直到完成查询。

3 回复 | 直到 8 年前

FactoryAidan 11 年前

我完全改写了这个答案。

由于很难将我写的内容拼接到你写的内容中,所以我会完整地或根本不使用这段代码。

这个 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 根本不会影响其他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);

此外,对于桌子到桌子直接的 插入,插入 使用时未定义列的查询 SELECT * 正如您所写的,请仔细检查数据库,以确保两个表具有相同的列结构,并且列字段匹配。然而,这不太可能中断查询。

mickmackusa Tom Green 9 年前

我有太多的缺陷,无法提供详细的解决方案,所以我将提供一个重写,假设您的查询有效(我很确定他们需要一些工作):

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);

-1

Fussionweb 11 年前

下面是数组的工作代码

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);

}

这给出了我们预期的确切结果