Hi

My Id in sql db is set to be auto increment. Of course it shows the last one if I query a select file from whatever.

But I would like to see the next id value to appear in a form that I'm filling up, BEFORE saving.

In another words:

Ref: (value of the next value ID)
Country:bala..bla...
and so on...


I did this but does not work:

$ref="<b>".$regist["id"].+1"</b>";

Thanks

SELECT MAX(idcolumn) +1 as nextid FROM table ORDER BY id ASC LIMIT 1;

OR
for MySql V 5.1.12 + you can use
SELECT LAST_INSERT_ID(idcolumn + 1) AS nextid FROM table;

Bear in mind if more than one process or user is inserting records this may not be be accurate as the auto increment on fires on the INSERT process.

Originally Posted by chrishirst

SELECT MAX(idcolumn) +1 as nextid FROM table ORDER BY id ASC LIMIT 1;

OR
for MySql V 5.1.12 + you can use
SELECT LAST_INSERT_ID(idcolumn + 1) AS nextid FROM table;

Bear in mind if more than one process or user is inserting records this may not be be accurate as the auto increment on fires on the INSERT process.

Thanks

That's the point. Bacause it will be adding quaite a few records by diferente users at the same time. At the moment a lot of ref are recorded in duplicate and triplicate due the fact one user do not know when another is adding records.

I just did this way, but no success:


<? $sql2="SELECT LAST_INSERT_ID(id + 1) AS nextid FROM table";
$result2=mysql_db_query("database",$sql2);
$regist2=mysql_fetch_array($result2);
$ref=$regist2["id"];
?>
<? echo"$ref"; ?>

The idea of using an autoincrement numeric column for the ID is to avoid duplications on multi-user systems. The server will assign the next ID as soon as the insert query fires and the auto increment index is updated before a subsequent id number is issued to the next queued query.
By taken the process away from the server you actually INCREASE the risk of duplication and prevent the server from optimising the queries

Why do you need to show the next id to the user before they add a record anyway? It is just as simple to return the ID to the users AFTER their record has been added.

You simply send the insert query and immediately fire a query for the LAST_USED_ID() like so;

INSERT INTO table ..... ;SELECT LAST_INSERT_ID() AS nextid FROM table";

Or if you are using INNODB as the storage engine, you could use transactions.

Originally Posted by chrishirst

The idea of using an autoincrement numeric column for the ID is to avoid duplications on multi-user systems. The server will assign the next ID as soon as the insert query fires and the auto increment index is updated before a subsequent id number is issued to the next queued query.
By taken the process away from the server you actually INCREASE the risk of duplication and prevent the server from optimising the queries

Why do you need to show the next id to the user before they add a record anyway? It is just as simple to return the ID to the users AFTER their record has been added.

You simply send the insert query and immediately fire a query for the LAST_USED_ID() like so;

INSERT INTO table ..... ;SELECT LAST_INSERT_ID() AS nextid FROM table";

Or if you are using INNODB as the storage engine, you could use transactions.

Let me try that and I will see. Thanks a lot.