How to auto generate id in php mysql?

We can get a unique auto-generated number from MySQL by creating an auto-incremented field. MySQL will generate a unique number by incrementing the last number of the table and will automatically add to the auto incremented field.

This is the best way to generate a trouble ticket number for a help desk system. In a school, if a new student joins then all details of the student we can feed to student table and while using the insert query. MySQL will add one auto generated a unique number to this auto incremented field. We need not have to specify any data in our query while adding the other details ( except auto-incremented field data).

Getting the unique ID

After successfully adding the record we can get this unique number by using mysql_insert_id(). Read the details on how to get this number by visiting the tutorial here. Now let us try how to create such an auto incremented field.

We have to make auto increment field integer ( obvious ) and also unique. The property set to auto increment. Here is the sql to create a student table with one auto increment field to assign one unique student ID.

CREATE TABLE `student` ( 
`student_id` INT( 3 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 50 ) NOT NULL ,
UNIQUE (
`student_id`
)
);

Now we will add one record to this table using one insert command.

INSERT INTO `student` (  `name` , `email` )
VALUES (  'john', '' );

You will see automatically the student_id field will be added with 1 and for next record it will get the next incremented value 2.

This way we can generate auto increment id for each record in our table.

Use lastInsertId() PHP PDO function

Use insert_id() PHP MySQLI function

Examples of uses of autoincrement field

• Assigning unique userid to member after signup.
• Assigning employee number after adding details of a new employ to our employ table.
• Giving tracking ID for a consignment after it is received for dispatch.
• Application Number generated after successful registration for an examination.

Note that all these numbers are unique and system generated.

Maximum value of inserted id

The maximum value of the inserted id depends on the type of numeric field we have selected. For TINYINT it will take value up to 127 for unsigned it is 255. After this the creation of auto increment ID will fail. So take care while creating the auto increment field to match your future requirements.

Starting from higher number

Change the auto increment field like this

ALTER TABLE student AUTO_INCREMENT = 50;

Pre defined value for a MySQL auto increment field

We can start an auto increment field from a pre-defined value. For example when we issue a student ID number, where student ID is an auto increment field we can generate IDs with values 100001 from the first record. This helps as we don't want to give a number like 1 to the first student and end with number 10004. This way we can have the first number as 100001 and last number 110005. This way all the students will have six-digit number.

This we can take care while creating the table by adding auto increment value at the end. Here is the SQL for creating a student table with auto increment field is set to 100000

CREATE TABLE student (
  id int(7) NOT NULL auto_increment,
  name varchar(50) NOT NULL default '',
  class varchar(10) NOT NULL default '',
  mark int(3) NOT NULL default '0',
  sex varchar(6) NOT NULL default 'male',
  UNIQUE KEY id (id)
) auto_increment=100000 ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

If you delete all records by truncate table sql command then while adding the first record the auto increment field will start from 1.

zerofill

We can fill the number by adding zeros ( 00001 or 00025 ) by using zerofill.

CREATE TABLE IF NOT EXISTS `student4` (
   `id` int(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `mark` int(3) NOT NULL DEFAULT '0',
  `gender` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

Alter table and make one numeric field as auto increment

Read here on how to create one incremental auto generated record number in MSSQL table.

SQL References Collecting Unique ID after inserting data
PHP MySQL How to delete Records in different tabels

---

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

---

Syntax for MySQL

The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

---

Syntax for SQL Server

The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons (
    Personid int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

---

---

Syntax for Access

The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons (
    Personid AUTOINCREMENT PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.

Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

---

Syntax for Oracle

In Oracle the code is a little bit more tricky.

You will have to create an auto-increment field with the sequence object (this object generates a number sequence).

Use the following CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned the next number from the seq_person sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".

How do I set Autoincrement in MySQL workbench?

What is Uniqid function in PHP?

How can I get last inserted ID after INSERT query in PHP?

What is Auto_increment in MySQL?