Generate number and verify it's uniqeness in mysql or advance by 1

[Jason89002]

So I'm pretty new to php and mysql and have been searching for an answer to this for day's with no success and I figure the reason is I'm asking the question in the wrong manner. 

Here is what I'm want to do:

I have a work order form that I currently enter the next available work order number in manually based on what the last number used was and the ticket is filled out then submitted to a mysql db.

I'm looking for a way to auto generate a new work order number based on the highest number existing in the mysql db and increment that number by 1 and have it auto populate the box.

 

is there a simple way to do this using java, jquery, ajax or just plain php with the mysql on duplicate command?

[Barand]

That what MySql gave us AUTO_INCREMENT function for.

 

Define you work order number as the PRIMARY KEY with AUTO_INCREMENT

[Jason89002]

That's not an option as "id" is the primary key using auto_increment and is also used to search, edit, update and delete records. That would require changing code for the entire process instead of one form. Changing 5 years of database records isn't an option.

[Adam]

Why would it? Just set the initial value to whatever is the latest record's ID.

[QuickOldCar]

Making the work order number the id would be the simplest solution, maybe even 5 years ago.

 

You could try doing MAX on the column and get it's highest value, then a +1

SELECT MAX(work_order) AS work_order_number FROM tablename;

Just be aware if is multiple submissions or slow this can mess up, so maybe also set a unique index on the work order column to prevent duplicates

[Jason89002]

Thanks QuickOldCar, your solution helps for the time being but I think my question wasn't clear enough for what I'm actually looking for.

What I want to happen is when the work order form is open that the work order number is auto populated immeadiately with the next available number based on the last work order number in the mysql database. So basically I was looking for a way to have the work order box query the row in mysql and fill in the box with the next incremented number.