Jump to content

Reorder based on group index


Dal1980
Go to solution Solved by Barand,

Recommended Posts

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

 

Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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

:)

Link to comment
Share on other sites

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.

 

:suicide: 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.