CodeIgniter Forums
[solved] insert and update the same table in one query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: [solved] insert and update the same table in one query (/thread-37009.html)



[solved] insert and update the same table in one query - El Forum - 12-23-2010

[eluser]SPeed_FANat1c[/eluser]
Hi,

Lets say I have a table of meniu items. Each menu item has a position of where it is. 0 the most left, infinity - the most right (of course the number is finite but I haven't set some maximum value Smile ).
When I insert a new itemm, them items positions on the right of the new item has to be updated - moved to right (in other words we need to add 1 to them).

So I can do this by executing two queries and thats it. But the problem - if let's say connection from server to database interrups after positions are updated, new item is not inserted so this is a bug.
This is of course unlikely but we have to deal even with those unlikely happenings, right?

Only way to do this I can think is to create stored procedure. Then I can call with one query from php code. But I don't like because when I insert a new item, there is 4 parameters, so I will have to pass all of them to the procedure which does not look that good in code.
Code then would look like

Code:
$query = $this->db->query ('call procedure_name('.param1.','.param2.', //etc.

It would be better to pass an array of those parameteres like this:

Code:
$data = array(
               'pavadinimas' => $this->input->post('input_pavadinimas'),
               'title_' => underscore($this->input->post('input_pavadinimas')),
               'tekstas' => stripslashes($_POST['text']),
               'eil_nr' => $this->input->post('position')        //kuo mazesnis skaicius, tuo kairiau meniu juostoje
            );
            
        $this->db->insert('info_psl', $data);

One thought came yet - to create a function that calls a stored procedure. I could pass an array of parameters to that function and it generates the query to call a stored procedure and calls it.

So what do you think, is there any simpler way to do what I want? Or I just should not worry about those unlikyly to happen things because they are extremely unlikely?

Edit: solved this problem using transactions http://ellislab.com/codeigniter/user-guide/database/transactions.html