Welcome Guest, Not a member yet? Register   Sign In
[solved] insert and update the same table in one query


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

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

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

$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-gui...tions.html

Theme © iAndrew 2016 - Forum software by © MyBB