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

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.

Solution

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.