[SOLVED] category order advice

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

[SOLVED] category order advice

Post by rsmarsha »

I have a categories table with an auto inc id, to which i want to add a new field containing a number to determine order.

As in order = 1 will be the first category shown. At the moment i was thinking of working it around calculating the highest order number and when a new cat is added, adding that as +1. To change the order i would have to take the current order of a cat, then -1 or +1 to that and alter the rest.

So if a category set to order = 2 wanted to be made order = 1 i'd have to run a query to set it to order = 1, then set the other category to 2.

Just wondered, is this is the best way to go about doing it?
Last edited by rsmarsha on Mon Feb 06, 2006 4:59 am, edited 1 time in total.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Re: category order advice

Post by raghavan20 »

rsmarsha wrote:I have a categories table with an auto inc id, to which i want to add a new field containing a number to determine order.

As in order = 1 will be the first category shown. At the moment i was thinking of working it around calculating the highest order number and when a new cat is added, adding that as +1. To change the order i would have to take the current order of a cat, then -1 or +1 to that and alter the rest.

So if a category set to order = 2 wanted to be made order = 1 i'd have to run a query to set it to order = 1, then set the other category to 2.

Just wondered, is this is the best way to go about doing it?
sorry..i could not understand when you say, "i want to add a new field containing a number to determine order.
" . If you do not mind can you provide examples of what you want to do.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Ok if i have 3 categories:

Code: Select all

category_id  category_name category_order
1               test cat              1

2               testing               2

3               newcat               3
The categories are shown in the order of "category_order" so are shown as :

test_cat
testing
newcat


If i want newcat to move up one position, i would have to change it's "category_order" to 2and testing's to 3. I have up and down arrow buttons for shifting a cat's position.

To input the "category_order" number, i am working on the highest current number (in this case 3), and subtracting 1, then adding 1 to the cat which is moving down 1 position.

The problem with this is when a category is deleted it leaves a gap in "category_order", as a new category is added by calculating +1 to the current highest "category_order".

Any suggestions on the best way to work this?
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

I have checked a forum software i use and am going to try their method. :)
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Sorted it now. :)
Post Reply