urgent help ordering database rows in php

[busby]

ok will try be as clear as possible

 

i have a small web app where a user can create a shopping list, populate it with categories..and populate categories with items...so for example...they make a list called tescos...they create 3 categories (frozen food, fruit and veg, electronics)...they then create items under each of those categories (burgers, apples, DVD's)

 

they can edit, delete and add new lists, categories and items.#

 

however i need them ALSO to be able to order the categories...so they could arrange them in the order they will appear in the shop...so i DONT mean order by category ascending or descending...i mean move a category up or down a position in the list...maybe have an up and down arrow next to each category.

 

i know i need a field in my database called for example...cat_order

 

but how do i populate this field when the user creates a new category? i cant have it auto increment because i already have the ID field do that...so when a user creates a new category i dont know how to populate this order field.

 

now once i do manage to get the order field populated how would i do this ordering?

 

could i have code please? ive spent a LONG time on this and i cant do it...its very urgent i need it working by tomorrow morning.

 

So my category tables looks like:

 

catid --- category --- listid --- catorder

1 ---------Frozen ----- 2 --------- ???

 

please hurry and thanks in advance

[jdavidbakr]

No, you don't need (or want) to auto-increment, just do a query before the insert to get the maximum value of cat_order for that user and add one to it when you insert the new one.  If there isn't a category for that user yet, when you add one to it you'll get '1'.