(Mysql, PHP) How to Get Auto_Increment Field Value Before Inserting Data

(mysql, php) How to get auto_increment field value before inserting data?

The autoincrement value is generated by the database itself, when the insertion is done ; which means you cannot get it before doing the actual insert query.

The solution you proposed is not the one that's often used -- which would be :

• insert some half-empty data
• get the autoincrement value that's been generated
• do your calculations, using that autoincrement value
• update the row to put the new / full data in place -- using the autoincrement generated earlier in the where clause of the update query, to identify which row is being updated.

Of course, as a security precaution, all these operations have to be made in a transaction (to ensure a "all or nothing" behavior)

As pseudo-code :

begin transaction
insert into your table (half empty values);
$id = get last autoincrement id
do calculations
update set data = full data where id = $id
commit transaction

Php Mysql - Get value auto increment before and insert it to another column

Simple workaround:

Save the record, get the inserted record's id, update it to the proper value

$r = mysqli_query($conn, "INSERT INTO `test`(`name`) VALUES ('myvalue')");
//1. save the record

$id=mysqli_insert_id($conn));
//2.  get it's id

mysqli_query($conn, "UPDATE `test` set `name` = $id"."'_myvalue' where id=$id limit 1");
//3. update the record appending correct id to the value as desired

How to set initial value and auto increment in MySQL?

Use this:

ALTER TABLE users AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

How would you get an auto_increment value on an insert query?

Codeigniter simplifies the MySQL query SELECT LAST_INSERT_ID() through the function $this->db->insert_id().

Although this is a query, it's a very light one that won't be a performance issue. One thing to have in note is this:

• If you want the ID on the row you inserted; $this->db->insert_id()
• If you want the auto_increment value, that is the next value that an INSERT will have as id, simply $this->db->insert_id() + 1

best way to get the auto_incremented value of one table at the time of insertion

The last insert ID is unique to each client connected, it points to the last ID inserted by that client only, and not other IDs inserted by other clients.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

The ID that was generated is maintained in the server on a
per-connection basis. This means that the value returned by the
function to a given client is the first AUTO_INCREMENT value generated
for most recent statement affecting an AUTO_INCREMENT column by that
client. This value cannot be affected by other clients, even if they
generate AUTO_INCREMENT values of their own. This behavior ensures
that each client can retrieve its own ID without concern for the
activity of other clients, and without the need for locks or
transactions.

In other words you do not need to perform any kind of locking for this if you have multithreading in mind.

get auto increment value for an inserted row safely

as far as I know, the id returned from mysql_insert_id is the auto increment id from the last insert query for the current connection (mysql_connect) to the sql database. The only reason for having to call it right after the query is that if you run two insert queries right after each other, mysql_insert_id returns just the last query. It wouldn't be reliably possible to get the id from the first query. Also, it gets the id from the last query, so if you did an insert then update, then ran mysql_insert_id. It would return 0 because the last query (update) isn't an insert.

Also, php just calls the mysql function mysql_insert_id. From the mysql manual:

The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients.

How retrieve in PHP the generated PRIMARY KEY from an AUTO_INCREMENT column for each row with an INSERT of multiple rows in MySQL?

There is no way to retrieve more than one generated auto-inc variable. Calling LAST_INSERT_ID() returns you the first value generated by the last INSERT.

To get all the values, insert one row at a time, and call LAST_INSERT_ID() after each one. This results in more overhead, but it's the only workaround.

This is especially an issue if you're doing INSERT...SELECT or LOAD DATA INFILE, generating many new id values in a batch.

See also MySQL LAST_INSERT_ID() used with multiple records INSERT statement

---

Related Topics