Alter MySQL column with values to auto increment

Question

Hi all, I had a hard time with the title, so let me explain.

What I'm doing is using the jQuery UI to create sortable list elements on a page. Once the order is submitted, php assigns an incrementing value to the list elements based on their order, drops the existing id column, creates a new id column and inserts each list elements value WHERE title=x. This creates the proper order of ID's, and is working fine.

What I'd like to do now is change the column to auto_increment, such that if I insert a new entry, the id is assigned automatically, one number higher than the greatest number generated by the php script. I'm not using any foreign keys or anything, just this simple table.

Is this possible?

Answer 1

My mistake, I misread your question. You do not want to use the database itself to provide numbering based on your sort order. You can however use the SQL query itself to return an incrementing field. One sec and I'll update with that info...

Ok, here it is:

you need to use a variable like set @n=0;SELECT @n:=@n+1 as 'n', col1, col2 from table

However, i highly recommend you just create the numbering in your php code if at all possible.

----------------Original Post----------------

This is pretty easy with phpmyadmin. Let me know if your unable to install that and I'll dig up the necessary SQL.

All heck, here is the SQL:

alter table t1 modify f1 int(4) auto_increment alter TABLE tbl auto_increment = xxx; //change xxx to be the next id it should use

you may need to run these in opposite order depending on your existing data set it will fail to add auto_increment if you don't change the value of auto_increment to be something not already in use.