[solved] insert and update the same table in one query |
[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 ). 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( 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 |
Messages In This Thread |
[solved] insert and update the same table in one query - by El Forum - 12-23-2010, 04:05 AM
|