CodeIgniter transaction, starting it and canceling it in different functions

[eluser]Otemu[/eluser]
Hi,

you could have something like this:

Code:

function overview(){
//overview transaction has begun
$this->db->trans_begin();
$this->db->query('AN SQL QUERY...');
}
function cancel(){
   //roll back transaction
    $this->db->trans_rollback();
}

function save(){
//success complete
$this->db->query('AN SQL QUERY...');
    $this->db->trans_commit();
}

The difference between trans_begin() and trans_start()
trans_begin() is manual, if a query fails you will need to check if it failed and then decide whether to rollback or do something else, with trans_start if a query fails it will automatically roll back.

I don't think you need to use transactions for this however, here is some solutions that think be useful:

1) Your preview script builds a form filled with hidden elements that is populated with the data your user just POSTed; the "Save" button would then submit that form to your processing script.
2) Use a second database table that mirrors your structure and use it for temporary storage, sort of a "Save but don't publish" kind of thing.
3) Add a column to your database that stores the status, i.e. use a TINYINT column and store a 0 if the user has not published yet or a 1 if the user has.

source quote here

In your case option 2 probably seems the best option

The principle of draft is that the modifications are not saved and are visible by only creators.

This way creators/admin can see the changes, however users will see the published version.

Alternatively Option 3 you could cache the saved db query and clear that cached result only on next save.