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.
“Live as if you were to die tomorrow. Learn as if you were to live forever.”
― Mahatma Gandhi
子曰:"三人行,必有我师焉;择其善者而从之,其不善者而改之."