The Assumption
One of the many requirements for an online reservation system states that while a customer is making a reservation via a web form, a unique ID, acting as the reservation number, is to be displayed on this form. It also specifies that each ID shall consist of a fixed prefix followed by a running integer, e.g., ABC_1
, ABC_2
, ABC_3
, and so on. These IDs will be used to identify individual reservations and be stored in the database upon submission. You are tasked to find a way to generate such IDs.
The Thinking
The key to upholding the uniqueness of each ID lies with the running integer. In a multi concurrent user environment, how to ensure that each new reservation activity gets an integer that is one larger than that of its immediate precursor? There is a handy way - that is, making use of the auto-increment primary key of a database table! In a nutshell:
-
Create a simple database table, say "uidTable
" that has an auto-increment primary key field called id
;
-
When a user navigates to the reservation form for the first time, fire a SQL INSERT
to the "uidTable
" followed by retrieving the newly generated ID; and
-
Concatenate this ID with the prefix and you get the unique ID for that reservation form.
Let's see how this can be accomplished in .NET-SQL Server and PHP-MySQL environments respectively.
The Doing
.NET-SQL Server
Create a table in SQL Server as shown below:
CREATE TABLE uidTable
(
id int IDENTITY PRIMARY KEY,
dummy int
);
These are the SQL statements that insert a value, say 1
, into the table and immediately retrieve the unique id generated for the insertion. Find out more on SCOPE_IDENTITY (Transact-SQL).
INSERT INTO uidTable (dummy) VALUES (1);
SELECT SCOPE_IDENTITY()
The code snippet in C# is given below:
using (SqlConnection con = new SqlConnection(ConnectionString)) {
int newID;
string sql = "INSERT INTO uidTable (dummy) VALUES (1); SELECT CAST(SCOPE_IDENTITY() AS int)";
using (SqlCommand cmd = new SqlCommand(sql, con)) {
con.Open();
newID = (int)cmd.ExecuteScalar();
}
}
PHP-MySQL
Create a table in MySQL as shown below:
CREATE TABLE uidTable (
id int AUTO_INCREMENT PRIMARY KEY
);
These are the SQL statements that insert a null
value into the table and immediately retrieve the unique id generated for the insertion. Find out more on LAST_INSERT_ID().
INSERT INTO uidTable VALUES (null);
SELECT LAST_INSERT_ID();
Besides LAST_INSERT_ID()
, you can use the mysqli_insert_id() function in PHP to return the unique id.
The code snippet in PHP is given below:
<?php
$con=mysqli_connect("IP_address","username","password","database_name");
if (mysqli_connect_errno())
{
echo "Unable to connect to MySQL: " . mysqli_connect_error();
}
mysqli_query($con,"INSERT INTO uidTable VALUES (null)");
$newID = mysqli_insert_id($con);
mysqli_close($con);
?>
Keep it Empty
The sole purpose of the "uidTable
" is to generate an auto-increment id for each insertion. The inserted rows in this table are of no use and can be deleted if storage space is of concern.