php/mysql category order

[Kingy]

I'm making a basic forum for the site i'm doing and one of the functions I want for the admins is to set the order in which the categories appear;

 

ie. The current order might be (PC Games - XBOX Games - PS GAMES - MISC Games). However, when an admin adds the category WII Games he may want it to appear between XBOX and PS categories.

 

What I was thinking is just put a categoryOrder field in the database and give it a value (1,2,3,4 etc etc) and just ORDER BY categoryOrder when the select statement is run. Now ok that makes some sense but if I give WII the categoryOrder row a value of '3' to put it in the third position, PS already has that value which leaves me in a pickle.

 

Is there an easy way to then loop through the database and change each individual row to add one to each value, PS becomes 4 - MISC becomes 5 and so on and so forth?

 

Or would you suggest I use some other technique of doing this.

 

Thanks in advance

[jl5501]

The idea of having a sequence field in the database is correct.

 

You would have an admin page that displayed the category and the sequence number, for each category, and the sequence number could be changed.

 

This is a hassle for the admin, as he has to type a new number for each category.

 

What I do in these situations is to display the categories in the way I just outlined. and then use jquery with the dnd extension to allow drag/drop of the categories with automatic reswuencing. I have used this on a number of sites now, and it is very successful

[jl5501]

Go to http://www.isocra.com/2008/02/table-drag-and-drop-jquery-plugin/ for reference. That is what I based the code I use, on