mysql php how to get auto increment field value before inserting data

mysql php how to get auto increment field value before inserting data  using -'php,mysql'

I'm uploading image file to storage server. Before uploading I should compose filename, which contains AUTOINCREMENT VALUE in it (for example, 12345_filename.jpg).

How could I get autoincrement value before inserting into DB?

I see only one solution

insert empty row
get it's autoincrement value
delete this row
insert row with real data using autoincrement value from p.1

Is there any other solutions?

Thank you

asked Oct 13, 2015 by mannumits1
0 votes

8 Answers

0 votes

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
answered Oct 13, 2015 by rajeevr642
0 votes

well, try this:

$query = "SHOW TABLE STATUS LIKE 'tablename'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);


array(18) {
["Auto_increment"]=> string(4) "3847"

This will be your next auto_increment ID.

answered Oct 13, 2015 by atulpariharmca
0 votes

@Pascal Martin makes a good point. In cases like this, I personally like to add another ID column, containing a random, 16-digit ID (which will also be the "public" ID in web apps and on web sites for security reasons). This random ID you can set beforehand in your application, work with it, and then set when the record is actually created.

answered Oct 13, 2015 by vibhorsingh
0 votes

There is no solution. You get the auto-increment value when you insert a new row, full stop. Inserting and deleting won't help, since the next auto-increment value will be one higher. Do to possibly multiple clients talking to the database at the same time, you can't predict the next value since it might be incremented between your guessing and your actual insert.

Find a different solution. Either insert a row and update it later, or generate an id for the filename that's independent of the auto-increment id.

answered Oct 13, 2015 by gauravsinghal83
0 votes

INSERT INTO CONTACTS1 (firstname, lastname) values('hi', select auto_increment from information_schema.TABLES where TABLE_NAME='CONTACTS1' and TABLE_SCHEMA='test')

Where ID is the Primary Key & Auto number column.

answered Oct 13, 2015 by shegokar.anjeet
0 votes

Well this is to old ,but if someone else need it.

You can get this kind of value at "information_schema" table where you could do something like

select AUTO_INCREMENT from TABLES where TABLE_SCHEMA = 'You're Database' and TABLE_NAME = 'Table Name'

So this kind of Meta Data are always stored in Information_schema .

answered Oct 13, 2015 by okesh.badhiye
0 votes

I think you can do it in following way:

  • in first call (ajax preferably) retrieve the current max value of autoincrement column from db using MAX in query.
  • insert your record with (above value + 1)_filename.jpg
answered Oct 13, 2015 by santosh soni
0 votes

This function is useful. Use this function in the trigger.


FUNCTION `fn_autoincrementvalue`(`pTableName` VARCHAR(50))
    RETURNS int(11)
    COMMENT ''
             FROM   information_schema.`TABLES`
             AND    TABLE_NAME    = pTableName
             LIMIT  1
END @@

answered Oct 13, 2015 by nikhilapatil