Use a transaction or not

PHP
1

I have made a function where 2 update statements take place:

function modify_appnt($connection,$id,$origin,$name,$staff,$start,$end,$serviceid){

        $connection->set_charset("utf8");
        $result=$connection->query('update appointments set name="'.$name.'",apps_origin="'.$origin.'",staffID="'.$staff.'",startDate="'.$start.'",endDdate="'.$end.'" where apID="'.$id.'"');
        $upservice=$connection->query('update appoint_servi_chosen set service_ID="'.$serviceid.'" where app_ID="'.$id.'"');

  }

The purpose is that the second query does not get executed if the first has not been executed successfully.
I am thinking to use transaction here but I have thought of an alternative and I want you to tell me what you think…here is the pseudocode:

if(!result)
{return false,}
else{execute $upservice}

What do you think of the above logic as an alternative to using a transaction?

Obviously I am not innovating here but just want to here some opinions.

2

This is basically the definition of a transaction. Why would you want to do it some other way, when there is already a proper way to do it?

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Reference

3

Normally, if the first query “fails”, the following queries (in a same transaction), won’t be executed. Plus, all the previous queries in the same transaction will be “rolled back”.

When I say “fail”, it could be a lot of things. The PHP server explodes just after the first query is executed? The DB will be intact if you used a transaction. With your solution, the first statement will still already be execute on the database (data will already be inserted, etc).

With a transaction, it’s all or nothing. With your solution, there are possibilities that the second part won’t be executed even if the first part worked, so transaction are more robust.
It might not be obvious with just 2 different calls, but if you have 10, it’s a lot of “if” :wink:

4

As other has mentioned, go with transactions.

In addition, if you use MySQL make certain that the tables use the InnoDB engine as else the transactions call will do nothing.

5

Ok I will go for a transaction…but I want to hear your opinion on the code-since I am not much “comfortable” working with transactions:

    $connection->autocommit(FALSE);
        $result=$connection->query('update appointments set name="'.$name.'",staffID="'.$staff.'",startDate="'.$start.'",endDate="'.$end.'" where apID="'.$id.'"');
        foreach ($serviceid as $value){//$serviceid is an array...that is why I use a foreach loop here
        $upservice=$connection->query('update appoint_servi_chosen set service_ID="'.$value.'" where app_ID="'.$id.'"');
        }     
        
       if(!$result||!$upservice)
              {   $connection->rollback();
                  $connection->autocommit(TRUE);
                  printf("Errormessage: %s\
", $connection->error);
                  return false;
              }
       else
              {
          $connection->autocommit(TRUE);
              }
                return true; 
              }

What do you think about the above logic?
Thanks