SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    use a transaction or not

    I have made a function where 2 update statements take place:
    Code:
    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:

    Code:
    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. #2
    SitePoint Addict bronze trophy mawburn's Avatar
    Join Date
    Apr 2014
    Posts
    204
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by designtrooper View Post
    The purpose is that the second query does not get executed if the first has not been executed successfully.
    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. #3
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    145
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    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"

  4. #4
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,196
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
        $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\n", $connection->error);
                      return false;
                  }
           else
                  {
              $connection->autocommit(TRUE);
                  }
                    return true; 
                  }
    What do you think about the above logic?
    Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •