mysql get last auto increment value

Question

Hi I have a field in mySql table called jobnumber which auto increments with each new entry. What I want to do is when a user views my form, get the value of the next 'jobnumber' . But I want to do this before any entry is made to the table. IE so when a user looks at a form it will display something like 'this is job number 6954' I have tried $rr = mysql_insert_id() but this only work after I have made an entry

Can anyone help please

thanks

Answer 1

You could try a query along the lines of "SELECT MAX(jobNumber) FROM JobTable;" and display that number (plus one). As long as the id has always been sequentially auto-numbered and no one else performs an insert between that query and the submission of the form it should be correct.

Answer 2

(1) You could do it with looking at the last id in the table and incrementing it manually (but you won't be sure that the max(id)+1 is really the next id, because the last dataset might be deleted, ...):

mysql>  select max(id)+1 from mytable;

(2) Since MySQL does not support sequences like for example Oracle does, you can also implement something like a sequence manually. MySQL proposes the following:

mysql>  CREATE TABLE sequence (id INT NOT NULL);
mysql>  INSERT INTO sequence VALUES (0);

mysql>  UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql>  SELECT LAST_INSERT_ID();

Also take a look at this link for more information.