multiple auto increment in mysql

Question

I'm using php and mysql. I have a table with the id column set to auto increment as the primary key. I'm trying to add another column called sort_order. The sort_order column should auto increment when the row is inserted. Then a user will be able to change the sort_order value. But mysql does not allow auto increment for more than one column?

What is the best way to auto increment the sort_order value?

By popular Request, some more explanation.

In administration area, the user will have a list of categories. Using javascript the user can drag the order they want the categories in. The script then posts a list of all the ids in the new order, and the sort_order values in the old order.

I then have a php function which updates mysql with the new sort_order value.

All of this is already done, except I had manually filled out all the sort_order values. I want it to be able to have a value when a user creates a new category.

Then I can use the sort_order to display the category order correctly on the front end.

I have everything done already. But in development I manually filled in the values for the sort_order.

Answer 1

You can leave your sort column equal to NULL by default. The only thing you need is a little smarter query. For instance:

select * 
from something
order by ifnull(sort, id)