Reorder based on group index

[Dal1980]

Hi All

 

I wonder if anyone know of a way around this scenario.

IDCatItem

11Pencil

21Pen

32Ruler

42Rubber

52Stapler

63Paper

73Highlighter

 

If I delete Cat 1 from the list so the data now looks like

IDCatItem

32Ruler

42Rubber

52Stapler

63Paper

73Highlighter

 

How do I rebuild the Cat groups so they would now look like the following?

IDCatItem

31Ruler

41Rubber

51Stapler

62Paper

72Highlighter

 

I've used the following statement in the past for rebuilding indexes where they would be individual but this can't be used when I refer to an index list as a group (i.e. multiples of the same index).

SET @var= 0;
UPDATE `mytable` SET `orders` = (SELECT @var := @var +1) WHERE `cat` = '1' ORDER BY `orders` ASC

Many thanks

 

[mac_gyver]

 

 

when I refer to an index

 

 

yes, and what if your category values have been referred/linked/bookmarked to by visitors to your site. they would expect to be able to revisit your site and have their links/bookmarks take them to the same category information if it still exists.

 

unless you are using test data in your tables that you will completely delete and start over with real data, you wouldn't ever alter the referral values between tables after it exists.

[Dal1980]

yes, and what if your category values have been referred/linked/bookmarked to by visitors to your site. they would expect to be able to revisit your site and have their links/bookmarks take them to the same category information if it still exists.

 

unless you are using test data in your tables that you will completely delete and start over with real data, you wouldn't ever alter the referral values between tables after it exists.

Thanks for your input.

 

Unfortantely, your concerns are not relevant in this situation. I used false headings as my actual data is a lot more complex. Just to be clear, this isn't anything to do with a product shop or anything that even touches the URL.

 

My question still stands (please don't worry about the premise of my question but rather the question itself).

 

Many thanks

[Barand]

In this instance, $deletedCat = 1

UPDATE mytable SET cat = cat - 1 WHERE cat > $deletedCat

But, as Mac_Gyver said, in a production environment, don't.

[Dal1980]

In this instance, $deletedCat = 1

UPDATE mytable SET cat = cat - 1 WHERE cat > $deletedCat

But, as Mac_Gyver said, in a production environment, don't.

 

Thanks Barand, I'll give that a try and write back.

 

The actual data I have in my tables is a lookup of data groups. This is not related to any stationary or shop website. I just used the example above so that it made the example clear rather than complicating things with a 4 tier group system.

 

 

[Dal1980]

Sorry for the delay. Just wanted to report that everything worked spot on! Thanks very much Barand!