update category order after deletion
Moderator: General Moderators
- jimthunderbird
- Forum Contributor
- Posts: 147
- Joined: Tue Jul 04, 2006 3:59 am
- Location: San Francisco, CA
update category order after deletion
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
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
You can do it with one SQL query so long as you're careful.
Your table looks like this:
You delete the country with a rank of 4:
.. so your data is now:
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...
The data is now:
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.
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 6Code: Select all
DELETE FROM `myTable` WHERE `myTable`.`rank` = 4Code: Select all
ID Country Rank
1 Country1 1
2 Country2 2
3 Country3 3
5 Country5 5
6 Country6 6Code: Select all
UPDATE `myTable`SET rank = rank-1 WHERE `myTable`.`rank` > 4Code: Select all
ID Country Rank
1 Country1 1
2 Country2 2
3 Country3 3
5 Country5 4
6 Country6 5- jimthunderbird
- Forum Contributor
- Posts: 147
- Joined: Tue Jul 04, 2006 3:59 am
- Location: San Francisco, CA
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
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
Assume that you have a input box to get the category you want to delete , assign it to a variable like $del_cat
now
now assign the rank to a variable..........
It is now 2 more queries required to do the job.
now
Code: Select all
select rank from table_name where cat='$del_cat'It is now 2 more queries required to do the job.
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..
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
SET @r:=0;Code: Select all
UPDATE test SET rank = (@r:=@r+1);- jimthunderbird
- Forum Contributor
- Posts: 147
- Joined: Tue Jul 04, 2006 3:59 am
- Location: San Francisco, CA
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
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
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.
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.