Welcome Guest, Not a member yet? Register   Sign In
DB transactions question
#1

[eluser]smilie[/eluser]
Hi All!

I have read the user manual - but I still have a question regarding usage of transactions (I think it is not strictly CI related).

Oke, here it goes.
In the model I have a couple of if statements (I know, they shouldn't be there, but...).

Now, let's say this is the code flow:

Code:
<?php

function process($id='',$action='',$result='')
{
   if($action == "new" AND $result == "success")
   {
      # Here I will update a table row
      $db->set('row_name',$value);
      $db->where('row_name',$value);
      if($db->update('table_name'))
      {
         # Then, I need to update another table
         $db->set('row_name',$value);
         $db->where('row_name',$value);
         if($db->update('table_name'))
         {
            # Then I have an array which will be loop-ed and DB updated
            foreach($loopvar as $key=>$val)
            {
                $db->set('row_name',$val['some_val']);
                $db->where('row_name',$key);
                if($db->update('table_name'))
                {
                   # Save the result of updates
                   $result[] = 'true';
                }
                else
                {
                   $result[] = 'false';
                }
            }
            # Check the results to see if any update has failed
            $test = array_search('false',$result);
            if(isset($test) and $test != '')
            {
               # At least some queries have failed;
            }
            else
            {
               # All above queries went OK
            }
         }
      }
   }
}

Now, my question is:
Is it possible to set $this->db->trans_start(); before very first query and $this->db->trans_complete(); at the very end (and also - at the end of each if statement which would do return FALSE;

Would this work this way - and if not - please advise Smile

Cheers,
Smilie
#2

[eluser]Madmartigan1[/eluser]
Is this what you're going for?

The trans_status will be false and will rollback if ANY queries fail.

Code:
<?php

function process($id='',$action='',$result='')
{
    if($action == "new" AND $result == "success")
    {
        $this->db->trans_start();

        # Here I will update a table row
        $db->set('row_name',$value);
        $db->where('row_name',$value);
        $db->update('table_name');

        # Then, I need to update another table
        $db->set('row_name',$value);
        $db->where('row_name',$value);
        $db->update('table_name');

        # Then I have an array which will be loop-ed and DB updated
        foreach($loopvar as $key=>$val)
        {
            $db->set('row_name',$val['some_val']);
            $db->where('row_name',$key);
            $db->update('table_name');
        }

        $this->db->trans_complete();
        return $this->db->trans_status();
    }
    else
    {
        //return something else?
    }
}
#3

[eluser]Phil Sturgeon[/eluser]
You could simplify this massively to something like:

Code:
function process($id='',$action='',$result='')
{
   if($action == "new" AND $result == "success")
   {
      $this->db->trans_start();

      # Here I will update a table row
      $db->set('row_name',$value);
      $db->where('row_name',$value);
      if($db->update('table_name'))
      {
         # Then, I need to update another table
         $db->set('row_name',$value);
         $db->where('row_name',$value);
         if($db->update('table_name'))
         {
            # Then I have an array which will be loop-ed and DB updated
            foreach($loopvar as $key=>$val)
            {
                $result = true;
                
                $db->set('row_name',$val['some_val']);
                $db->where('row_name',$key);
                if( ! $db->update('table_name'))
                {
                   $result = false;
                   break;
                }
            }
         }
      }

      if ($result === false)
      {
        $this->db->trans_rollback();
      }
      
      $this->db->trans_complete();
      return $result;
   }
}
#4

[eluser]smilie[/eluser]
Hi,

Thanks Madmartigan1 and Phil.

I have just 1 more additional question. In Phil's example, first and second DB update queries are not tested for $result = false;

Does this mean, that in case update has failed, that CI will 'automagically' do a trans_rollback()?

Or should I add for each if($db->update) { do stuff } else { trans_rollback }.

In another words, if any query fails - do I need to perform check and action on it - or will CI roll it back without my intervention?

Edit
By re-reading post by Madmartigan1 I conclude that CI will actually automatically perform trans_rollback (which is then 'hidden' in trans_commit())?
Please let me know if I got this right :-) Would hate to have to re-re-write code... again Smile

Thanks!

Cheers,
Smilie
#5

[eluser]Phil Sturgeon[/eluser]
Yeah use Madmartigan1's, my was written in a rush and looking back is pretty far off.

You'd need to set $result = false at the start then set $result = true if the last query was successful.
#6

[eluser]smilie[/eluser]
Phil, so CI can not itself 'recognize' that one of queries has failed. I always have to do it with $result = true / false and then rollback of commit?

Thanks you guys!

Cheers,
Smilie
#7

[eluser]Phil Sturgeon[/eluser]
Sure it can, but why have it try to do an extra 10 (or however many) queries when it doesn't need to?

Either way will work.
#8

[eluser]smilie[/eluser]
Phil,

All clear now! Thanks again :-)

* El Forum goes to definitely create a new piece of code Smile

Cheers,
Smilie




Theme © iAndrew 2016 - Forum software by © MyBB