[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
Cheers,
Smilie
[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?
}
}
[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;
}
}
[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
Thanks!
Cheers,
Smilie
[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.
[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
[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.
[eluser]smilie[/eluser]
Phil,
All clear now! Thanks again :-)
* El Forum goes to definitely create a new piece of code
Cheers,
Smilie
|