Page 1 of 1
update category order after deletion
Posted: Fri May 18, 2007 1:36 am
by jimthunderbird
Hi All,
Recently I'm having headache with this problem:
I have a mysql table with a very simple structure:
id int(12) auto_increment primary key,
category_name varchar(120),
rank int(12)
Basically I stored a bunch of category with their corresponding order(rank) in a linear way, like:
1. US
2. Canada
3. Mexico
4. France
5. Finland
6. Italy
7. Singapore
...
Now if I delete Mexico, Finland and Italy
The table will become:
1. US
2. Canada
4. France
7. Singapore
...
But I wish they will look like:
1. US
2. Canada
3. France
4. Singapore
...
Thus removing the "empty holes".
I can achieve this with 3 update sqls, but with the category list getting longer and longer, the number of sqls will increase correspondingly.
Can I achieve this effect with one more two sqls only?
Thank you very much.
With my best,
Jim
Posted: Fri May 18, 2007 5:49 am
by onion2k
You can do it with one SQL query so long as you're careful.
Your table looks like this:
Code: Select all
ID Country Rank
1 Country1 1
2 Country2 2
3 Country3 3
4 Country4 4
5 Country5 5
6 Country6 6
You delete the country with a rank of 4:
Code: Select all
DELETE FROM `myTable` WHERE `myTable`.`rank` = 4
.. so your data is now:
Code: Select all
ID Country Rank
1 Country1 1
2 Country2 2
3 Country3 3
5 Country5 5
6 Country6 6
You know you deleted the record with a rank of 4, so all you need to do is update everything with a rank of more than 4...
Code: Select all
UPDATE `myTable`SET rank = rank-1 WHERE `myTable`.`rank` > 4
The data is now:
Code: Select all
ID Country Rank
1 Country1 1
2 Country2 2
3 Country3 3
5 Country5 4
6 Country6 5
NOTE: I've assumed you're talking about updating the ranks rather than updating the autoincremented ID. If you want to update the id the process is the same, but it's a really, really bad idea. Never mess with an auto-incremented value unless you have absolutely no choice. Personally, I don't even delete records most of the time. I have a boolean column that flags them as deleted. That way I can tell what data has existed in the past.
Posted: Fri May 18, 2007 7:27 am
by jimthunderbird
Hi,
Thanks for your reply. I'm kind of using your way of updating already but that will lead to 3 sqls (well actually 6 sqls, 3 of them are doing the update).
Suppose my table look like
id name rank
13 cat_1 1
16 cat_2 2
18 cat_3 3
19 cat_4 4
23 cat_5 5
28 cat_6 6
30 cat_7 7
Now I wish to delete cat_2,cat_4 and cat_5, I need to do these:
1. Delete from [cat_table] where id = 16 (delete cat_2)
2. Update [cat_table] set rank = rank - 1 where id > 16
Now the table looks like:
id name rank
13 cat_1 1
18 cat_3 2
19 cat_4 3
23 cat_5 4
28 cat_6 5
30 cat_7 6
3. Delete from [cat_table] where id = 19 (delete cat_4)
4. Update [cat_table] set rank = rank - 1 where id > 19
Now the table looks like:
id name rank
13 cat_1 1
18 cat_3 2
23 cat_5 3
28 cat_6 4
30 cat_7 5
5. Delete from [cat_table] where id = 23 (delete cat_5)
6. Update [cat_table] set rank = rank - 1 where id > 23
Now the table looks like:
id name rank
13 cat_1 1
18 cat_3 2
28 cat_6 3
30 cat_7 4
This is what I want. But it takes 6 sqls to achieve it.
I wish to know if there's a way to do the delete in one sql and do the update in another sql then achieve the same result?
I do know that I can delete 3 cats with one sql like:
Delete from [cat_table] where id in (16,19,23)
After this, the table will look like:
id name rank
13 cat_1 1
18 cat_3 3
28 cat_6 6
30 cat_7 7
I'm having problems after this to have what I want, how to turn the rank from 1,3,6,7 to 1,2,3,4 in one sql only?
Here's a bigger picture of why I wish to do this:
Ultimately I wish to display this:
1. cat_1
2. cat_3
3. cat_6
4. cat_7
Currently I'm using php + mysql combination solution to do it but this is only a hacking solution not the "real" one I think.
In php
I first get cat_list from "select * from [cat_table] order by rank asc"
k start from 1 to length of cat_list
display k. cat_list['name']
I'm actually looking for a pure sql solution to it.
Hope I explain my problem more clearly and thank you for your help.
With my best,
Jim
Posted: Fri May 18, 2007 8:03 am
by Ind007
Assume that you have a input box to get the category you want to delete , assign it to a variable like $del_cat
now
Code: Select all
select rank from table_name where cat='$del_cat'
now assign the rank to a variable..........
It is now 2 more queries required to do the job.
Posted: Fri May 18, 2007 8:55 am
by onion2k
We're a bit beyond fetching the rank from the table Ind007. That's not very helpful.
As for a pure, single query method of updating a series.. I've not managed 1, but I can do it in 2..
Code: Select all
UPDATE test SET rank = (@r:=@r+1);
Posted: Fri May 18, 2007 11:55 am
by jimthunderbird
Hi Onion2k,
You solution is very good:), I think it's the way to go, 2 sqls to do the update job is ok.
So now I think I can do the delete and update in 3 sqls no matter how many cats I delete.
Now brings a furthur question:
If there are around 30 people playing this delete and update operation at this table, how to guarantee that two or more concurrent operation won't affect the result? I've read something about lock table and not sure if it's the safest way to do. Are there any better solutions?
With my best,
Jim
Posted: Sat May 19, 2007 8:32 pm
by Begby
You need to read about 'transactions' in order to get around concurrency issues with multiple users.
As for trying to get this all into one SQL statement vs 3, or even 6, its not that big of a deal unless there is a measureable and real performance hit. Its not worth spending hours trying to optimize it only to save 20 milliseconds unless your site is getting hammered.