How to generate auto id in php

I have a reservation system where I have to generate an new id each time the user click on "New Reservation", this id is passed into hidden input as they are later on used in other SQL tables that are linked to the reservation table. So the tables in questions are deposit, nights ect..

What I did first was to take the max value of the column id, and add 1 to it. The problem was that when multiple computer(people) where using it, the same id would be generated. The id is not inserted into the database until all information have been filled in.

Then what I did was that each time the user clicks on the button "Create new reservation" I would store that id in a temporary database and get the maxvalue of this table to generate another id. This works 2/3.

Do you have any idea on what would be the best way to get a unique id each time a reservation is created?

I am not asking for code, just a better logic.

asked May 21, 2018 at 10:47

3

make the column id an auto increment, this will keep giving a new id each time a user clicks on new reservation auto increment

the link above will show you how to do this.... with regards to the nights and reservation tables, you can copy the data from the nights table and add it to the reservation table using the insert into select statement insert into

the link above will help you do that.

answered May 21, 2018 at 10:52

Use mysql_last_id to fetch the last inserted ID:

$mysqli = new mysqli("localhost", "user", "password", "database");
$mysqli->query("INSERT INTO table (id, column1, column2) VALUES (NULL, 'Value #1', 'Value #2')");

printf("<p>ID of the previous record: %d.</p>", $mysqli->insert_id);

To add an auto-incremented id column, use this:

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

You can also choose the start of the next id with:

ALTER TABLE `table` AUTO_INCREMENT=123;

answered May 21, 2018 at 10:55

DanDan

4,7902 gold badges12 silver badges28 bronze badges

One idea I've seen elsewhere is to create a number based on the current datetime (preferrably accurate to millisecond) AND a field unique to the computer/user account that creates the record.

Now, "based on" means you can customize this kind of function.

answered May 21, 2018 at 10:51

George MenoutisGeorge Menoutis

6,3182 gold badges16 silver badges37 bronze badges

Better way is to use SQL Auto-increment. You don't have to send a "id" in your form. When you're sending the form, just insert datas you received : MySQL will insert automatically the right value in "id". If you need to get back this id, you could use a SELECT statement.

answered May 21, 2018 at 10:54

ArtheriomArtheriom

111 silver badge3 bronze badges

You can also view this video below which shows you exactly how to create a table and add a field as an auto increment: Auto increment video

answered May 21, 2018 at 10:59

Not the answer you're looking for? Browse other questions tagged php html sql or ask your own question.

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

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`
)
);

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

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.

Maximum value of inserted id

Starting from higher number

ALTER TABLE student AUTO_INCREMENT = 50;

Pre defined value for a MySQL auto increment field

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 ;

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

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

How to Generate auto number in PHP?

How to automatically Generate id in MySQL?

How can generate Autoincrement serial number in PHP?