0
One have a mysql table (tbl_test) that has a unique ID, name string and an int order.
ID | Nome | Ordem
1 | name1 | 1
2 | name2 | 4
3 | name3 | 3
4 | name4 | 5
5 | name5 | 2
I wonder if it is possible to insert a new element with order 3 to pass all elements from the old three one upwards. That is to say when inserting the 6 | name6 | 3 the table would look like this:
ID | Nome | Ordem
1 | name1 | 1
2 | name2 | 5
3 | name3 | 4
4 | name4 | 6
5 | name5 | 2
6 | name6 | 3
Yes, but you will need to work with Trigger if you don’t want to do via php.
– rLinhares
Perform a
updatebefore performing theinsert, within a transaction, obviously.– Woss
@Andersoncarloswoss what will require a querry inside for each element that gets very slow if it has many elements, thank you anyway.
– Pbras
@rLinhares I have no problem doing in php but I’ll take a look at Trigger thanks
– Pbras
It will be just one more query, not one for each, but yes, the solution will be O(N) and will grow with the number of records, but no other solution will be different from that.
– Woss
I wouldn’t recommend using Trigger, better do in php, Trigger will be faster to implement now but in the long run will give you more trouble.
– fajuchem
Running a query like "update tbl_test set order = order+1 Where order >= ordem_do_element_a_insert" before inserting the new element would already suffice.
– fajuchem