mysql_insert_id: get Unique number generated by auto increment field after a insert query

mysql_insert_id() to get the unique id created after insert command of a record

On many occasions we want to generate a unique ID from the operation we do on a database. For example take the case of generating a trouble ticket number in case of a help desk. Here a ticket number is generated while the record is inserted to the table. This ticket number is nothing but the value of an auto incremented numeric field, which is unique also. Now in this case once the record is created we have to display or process (sending mail etc) along with the trouble ticket number (auto incremented unique ID) to the system. Here we will use mysql_insert_id() function to get the id generated after the insert command. We have to just echo or print the mysql_insert_id() to show the trouble ticket number. Here is an example of this. We will start with an insert command.



$query=mysql_query("insert into help_desk(userid,type,domain,detail) values('$userid','$type','$domain','$detail')");




/* Note that here auto increment field is not shown as it automatically add the next incremented value with every insert command
*/



echo "Your trouble ticket number is = " mysql_insert_id();

This way we can display the generated ID of auto increment field of recent insert command.

However you may not like to show 1 or 2 as trouble ticket number so you can start the id at a higher value. You can manipulate and add strings ( text or today's date ) by various string function to this insert ID to make it a meaning full number.

PDO lastInsertId()

If you are using PDO then you can get the unique auto incremented id by using lastInsertId() function. Here is the code.

$sql=$dbo->prepare("insert into help_desk(userid,type,domain,detail) values('$userid','$type','$domain','$detail')");

if($sql->execute()){

$ticket_id=$dbo->lastInsertId(); 

echo " Your trouble ticket number is = $ticket_id ";

}

Usually as a practice we can add today's date to the trouble ticket number like this.


$dt=date("m/d/y");

$id=5; // as an example

$ticket_id=$dt.'-'.$id; 

echo "Your trouble ticket ID = $ticket_id";

Output of above code is here

Your trouble ticket ID = 04/23/24-5

PHP MySQL functions MySQL autoincrement field

Subscribe to our YouTube Channel here

This article is written by plus2net.com team. https://www.plus2net.com

plus2net.com

Tom345	23-06-2009
On insert of a new user (multipule fields) into a new record in db table, how can I concat username.mysql_insert_id() to insert a unique nickname field for that record during the insert? I would like to perform this during the insert with that users unique record ID, not querying for the previous ID value.

smo	24-06-2009
mysql_insert_id() you will get after the insert query is executed. After this you can use one update query and add the data to the field for this perticular record. In a single query I don't think this is possible.

joe	10-02-2010
what happens if another web page does an insert between your insert and mysyl_insert_id() call ? Do you need to lock the table to stop this?

grin4	23-03-2010
Hi Guys, your script is great. I have one small problem: when I click refresh it automatically creates post with the details in the cookie (the previous post). please let me know if there is a solution for this. thanks

smo	23-03-2010
This problem you will face if you use same page or another page to execute insert command and display result. You need to process all the data in a different page and do a header redirect back to form page or thank you page based on the success or failure of the insert command.