How to use multi_query for insert and delete records of two tables

Question

I want to insert two different records into two different tables and delete original one. like this

$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'";

It possible to use multi_query for this query.

I used multi query but first query only working

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

Here we want to run Member Deleted one time instead four time till complete query.

While working on related SQL commands, do not forget to use transactions. It helps you to rollback to the previous versions of stored data if any of commands fails to run successfully. — Mohammad Saberi, Mar 08 '15 at 12:57
I don't want to talk about mysqli_multi_query() because I never used it. What I said is about SQL Transactions. When you have more than one SQL command that must execute, and all of them are related with each other, in this case you must use Transaction to prevent applying changes on tables and records if any error occurs while executing one of those commands. Take a look at [Transactions in PDO](http://php.net/manual/en/pdo.begintransaction.php). PDO is much better than MySQLi if you want work with any database with PHP. — Mohammad Saberi, Mar 09 '15 at 04:50
@Fussionweb The accepted answer uses an illogical processing block for `mysqli_multi_query`. Please shift the accepted answer back to mine, where the best code block on this page exists. I don't know what the conspiracy is here, but SO readers need direct access to the best answer. No answer should be including `mysqli_store_result`. And my `while` condition will avoid potential failures. Reference: http://stackoverflow.com/questions/14715889/strict-standards-mysqli-next-result-error-with-mysqli-multi-query/22469722#22469722 — mickmackusa, Apr 08 '17 at 20:19
@Fussionweb please explain what you are doing with the accepted answers? — mickmackusa, Apr 09 '17 at 14:47
The accepted answer's update does ABSOLUTELY no query value escaping, error checking or affected row checking. So, yes, this will work when all queries work, but it will fail miserably when the slightest thing goes wrong. This is a decidedly lazy/poor answer. — mickmackusa, Apr 09 '17 at 14:51
@mickmackusa this code is working, finally i fixed the error it's working for 2 years, am updated with new code too.. — Fussionweb, Apr 09 '17 at 15:08
You are sending a bad message with the accepted answer, as I have listed in an earlier comment. Uninformed SO readers may copy/paste it and use bad practices. What was the error? And how is my answer not suitable? The reason it was so hard to find the error, was because the code block wasn't properly set up to give the correct feedback. — mickmackusa, Apr 09 '17 at 15:13

FactoryAidan · Accepted Answer · 2015-03-09T06:10:27.340

I completely rewrote this answer.

Since it is hard to splice what I wrote into what you wrote, I would use this code in it's entirety or not at all.

The free_result and fetch_row commands shouldn't be there. They will cause an error because there is no result set from INSERT and DELETE queries.

$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;
}

If you find the the first query executes but not any others like you said earlier, it is not because of the php code. It is because of the query(ies) you wrote. In mysqli_multi_query(), all queries are executed in the same call, so if one is executed, they were all executed... even if not all resulted in success.

Basically this means that any PHP code after the multi_query doesn't affect the results of your other SQL queries at all. We could essentially throw away EVERYTHING and just write this to get it to work:

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

Also, for table to table direct INSERT queries without the columns defined like when using SELECT * as you have written, double check your database to make sure the two tables have the same column structure and that the column fields match up. However, this is not likely to break the queries.

You would only see that message if `$msgid = POST_('roll_id');` was giving you a `null` value. Are you sure you don't want that command to be `$msgid = $_POST['roll_id'];` ? — FactoryAidan, Mar 08 '15 at 10:11
But you're still seeing the `ERROR! Delete Operation Unsuccessful` message. So that means this `if` statement `if (strlen($msgid) > 0):` is evaluating to `false`. Which means `$msgid` is not being defined. You need to make sure `$msgid` is receiving a value from `$_POST['roll_id']`. What if you do `var_dump($msgid);` after the first line, what value does it give you? — FactoryAidan, Mar 08 '15 at 10:55
So before you even worry about the SQL queries, you need to make sure the `'roll_id'` is being received from the `
` you sent the POST variable from. The SQL queries will not happen if that value is `null`. — FactoryAidan, Mar 08 '15 at 11:40
I updated the Answer. Since `INSERT` statements always return a false result set, I added `if($result)` in front of `mysqli_free_result($result);` so that no error would be generated from that scenario. Update the code and let us know what happens. — FactoryAidan, Mar 08 '15 at 12:48
See updated Answer on that same line we edited before. I had to fully wrap the `if():` statement by adding a colon `:` and a `endif;` — FactoryAidan, Mar 08 '15 at 12:58
See updated Answer. I also had to remove the `{` after the `do` statement and the `}` before the `while()`statement. — FactoryAidan, Mar 08 '15 at 13:14
Ok, in your question code. Look for this line `mysqli_free_result($result);` Change it to this: `if($result){mysqli_free_result($result);}` There is also an extra single slanted quote at the end of this line `Unsuccessful'); — FactoryAidan, Mar 08 '15 at 14:08
That is a controlled error. That message means the script is working but you did not send any `$_POST` data to test it with. — FactoryAidan, Mar 10 '15 at 05:20
I have posted the answer! `if(isset($_POST['submit']))` mistake we committed — Fussionweb, Mar 12 '15 at 10:19

mickmackusa · Answer 2 · 2017-04-09T14:57:06.780

There are too many flaws for me to provide an itemized solution, so I'll offer a rewrite, assuming your queries work (and I am rather certain they need a bit of work):

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

i really didn't tried your answer, where my update work's fine, let me check with your answer, thank you for your time — Fussionweb, Apr 09 '17 at 15:22

Fussionweb · Answer 3 · 2015-03-12T11:36:11.043

Here is the working code with array

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

}

Which give exact result what we expected

How to use multi_query for insert and delete records of two tables

3 Answers3

Here is the working code with array