mysql get last auto increment value

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 wi开发者_开发技巧ll 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

$ret = mysqli_query("SHOW TABLE STATUS LIKE 'table' ");
$row = mysqli_fetch_array($ret);
$Auto_increment = $row['Auto_increment'];
mysqli_free_result($ret);

(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.

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.

There are at least a few problems regarding your approach. First of all, there is virtually no possibility to know that number reliably. In order to "reserve" that number, you need one of 2 options:

1: lock the table for all clients until this one decides to insert something, therefore being able to guess the next auto increment (as you can guess, pretty bad)

2: Trying to "reserve" the auto incremented field by forcing a temporary insert (while this looks better, it is not, because it will lead to alot of unusuable primary keys, just sitting there).

Choose either one of them you like, or just drop completely this tiny bit of functionality.

You can get a last ID in your table before inserting a value and incrementing by 1!