I'm developing a little script with draggable boxes and saving their positions in MySQL table. But I have a little problem. I dont know how to write SQL query for deleting and adding new boxes. Let say we have table like this:
-----------------------------------
| id | position | title |
-----------------------------------
| 23 | 1 | something|
| 24 | 2 | something|
| 26 | 3 | sometging|
| 32 | 4 | something|
.............etc...................
-----------------------------------
ID is ID for boxes (unique number for box). Position is position of every box in layout, and title is a title (simple) :) I dont know how to make query to add/delete box and change all rows in "position" column, after that new or deleted row.
Let say I want to delete box with ID=24. Here is table after changes:
-----------------------------------
| id | position | title |
-----------------------------------
| 23 | 1 | something|
| 26 | 2 | sometging|
| 32 | 3 | something|
.............etc...................
-----------------------------------
And this is table when I adding a new box in table. (new box have ID=10)
-----------------------------------
| id | position | title |
-----------------------------------
| 10 | 1 | something|
| 23 | 2 | something|
| 24 | 3 | something|
| 26 | 4 | sometging|
| 32 | 5 | something|
.............etc...................
-----------------------------------
How to write query for adding and deleteing boxes from table?
P.S. My english is really bad, I hope that you will understand me, and give me a solution for my problem.
Cheers
I understand that you want to update positions while inserting or deleting a row. Am i correct ?
For this purpose, you could use triggers to do both operations at the same time. Not all versions of MySQL support triggers so you'll have to check your MySQL documentation.
Otherwise, you'll have to use several queries in your code : at least one for updating your table before insert/delete and one for insert/delete.
Ex : if 1 is the position for your new row :
UPDATE my_tbl SET position = position + 1 where position >= 1
INSERT INTO my_tbl (id, position, title) VALUES (10, 1, 'something')
Or : if 2 is the actual position for the deleted row :
UPDATE my_tbl SET position = position - 1 where position > 2
DELETE FROM my_tbl where id = 24
The WHERE clause will depend on your specifications, of course.
BUT : if your table has a unique index on the position field, more shall be done to process consistenly your data...
it's like this brother 1. insert into boxes (id,position,title) values('99','12','something') ; 2. insert into boxes values('99','12','something') ; 3. if you use automatic increase filed for 'ID' insert into boxes (position,title) values('12','something') ; db will assign u new id. you will best to refer www.w3schools.com
Have fun