Mysql INSERT and DELETE in a single statement with dependant on eachother

Question

I have the following two queries. Second query is dependent on first one.

$query1 = mysql_query("Insert into table_one set ---- ");
if($query1)
{
   $query2 = mysql_query("delete from table_two where condition---");
   if($query2)
   {
      $message = "both queries executed successfully";
   }
   else
   {
      $del = mysql_query("delete record inserted by $query1");
   }
}

Can we execute these two queries in a single statement so that both queries depend on each other.
If INSERT query fail, DELETE query also fail it's execution as well as if DELETE query fail INSERTION in query first fail.

Thanks

You use transactions. These can be rolled back upon failure. — Ed Heal, May 17 '15 at 17:25

Luki · Accepted Answer · 2015-05-17T17:41:03.000

2

If I good understand what you need, simply use transactions. Run this query before your insertion:

mysql_query('begin');

And then, if everything went fine, commit the transaction:

mysql_query('commit');

In case of any failures, you may rollback every change you made:

mysql_query('rollback');

Note that in case of MySQL, the MyISAM engine does not support rollback in transactions, so use InnoDB.

Read more about transactions here: https://dev.mysql.com/doc/refman/5.0/en/commit.html

Example with your code:

<?PHP

mysql_query('begin'); //start transaction
$query1 = mysql_query("Insert into table_one set ---- ");
if($query1)
{
   $query2 = mysql_query("delete from table_two where condition---");
   if($query2)
   {
      mysql_query('commit'); //both queries went fine, so let's save your changes and end the transaction
      $message = "both queries executed successfully";
   }
   else
   {
      mysql_query('rollback'); //query2 failed, so let's rollback changes made by query1 and end the transaction
   }
}
else
    mysql_query('rollback'); //query1 failed, so let's end the transaction

edited May 17 '15 at 17:41

answered May 17 '15 at 17:28

Luki

214
1
7

Thanks for replying. Sir please guide me how i will use these two queries asked in question with begin , commit and rollback – Aftab Ahmad May 17 '15 at 17:37
problem solved thank for help, but sir i have a little question more that can we do the same task and run all above queries in a single `mysql_query("statement");` – Aftab Ahmad May 17 '15 at 17:59
`mysql_query` supports only one statement at once, so no. But there is something like `mysqli_multi_query`, but you would need (and you should) to change all mysql_* functions to mysqli_* functions. – Luki May 17 '15 at 18:02
dear please check my answer , and tell me whether this solution is perfect or not, actually my answer is updated form of your answer – Aftab Ahmad May 17 '15 at 20:16

score 1 · Answer 2 · answered May 17 '15 at 17:43

1

If query2 fails it doesn't check query1.

$query1 = mysql_query("Insert into table_one set ---- ");
$query2 = mysql_query("delete from table_two where condition---");
if( $query2 && $query1)
{

      $message = "both queries executed successfully";
}
else if(!$query2)
   {
      $del = mysql_query("delete record inserted by $query1");
   }

answered May 17 '15 at 17:43

lakshya_arora

791
5
18

thanks for replying, you give me good idea, this concept is fine when both queries done successfully but in-case second query fails what will be the the query `$del = mysql_query("delete record inserted by $query1");` – Aftab Ahmad May 17 '15 at 17:48
if query2 fails then it does't go further. If it doesn't fail then query1 will be executed. I think it solves your probem. – lakshya_arora May 17 '15 at 17:53

score 0 · Answer 3 · answered May 17 '15 at 17:27

0

You can use transaction, if any query fails then call rollback, otherwise commit

answered May 17 '15 at 17:27

calynr

1,264
1
11
23

1

Please give me a little concept how to use it, I am new to it. thanks – Aftab Ahmad May 17 '15 at 17:28

score 0 · Answer 4 · answered May 17 '15 at 20:14

I found a best solution.
Extending idea of @Luki i wrote the following code and it give me too much satisfied answer. First use the following function.

function multi_statement()
{
    global $conn;
    $total_args = func_get_args();
    $args = implode($total_args,";");
    $args = "begin;".$args.";commit;";
    $number = 0;
    if($conn->multi_query($args))
    {
        do
        {
            if ($conn->more_results())
            {
                $number++;
            }
        }
        while($conn->next_result());
    }
    if($number < (count($total_args)+1))
    {
        $conn->query('rollback');
        echo "Sorry..!!! Error found in Query no:".$number;
    }
    else
    {
        echo "All queries executed successfully";
    }
}

Then I called the function with number of statements, all these statements are dependent on each other. In-case there is error in any query, no one query occur any changes in database.

    $statement1 = "INSERT INTO `pic_gall`.`admin` (`admin_id`, `username`, `password`) VALUES (NULL, 'as1', 'as1')";
    $statement2 = "INSERT INTO `pic_gall`.`admin` (`admin_id`, `username`, `password`) VALUES (NULL, 'as2', 'as2')";
    $statement3 = "INSERT INTO `pic_gall`.`admin` (`admin_id`, `username`, `password`) VALUES (NULL, 'as3', 'as3')";
    $statement4 = "INSERT INTO `pic_gall`.`admin` (`admin_id`, `username`, `password`) VALUES (NULL, 'as4', 'as4')";
    $statement5 = "DELETE from user where user_id = '12'";
multi_statement($statement1,$statement2,$statement3,$statement4,$statement5);

Mysql INSERT and DELETE in a single statement with dependant on eachother

4 Answers4