Development – Automatic Number Generation

The system required to generate serial numbers for Users, Applications, Clients and for many other records. These serial numbers were supposed to follow a certain format; they should have a defined character width; and also they should comprise of two parts (the prefix part and the incrementing numeric part).

For an example, the serial number for an application should look like this.

APP000001
 

In this, the “APP” part is the prefix for Applications. The “000001” is the incrementing numeric part.

For generating auto incrementing numbers the easiest way is to use “Auto increment” facility in MySQL. However that facility cannot be used to create serial numbers this complex.

So here’s what I did. This method is inspired from a project that I participated a few years back.

The auto number generator uses data from a table called “sys_autono”.
 

ER Diagram for the sys_autono table

The table that has been filled with data looks like this.

sys_autono filled with data - powered by phpMyAdmin

Here is the code to generate serial numbers. You can find the DBAdapter class in Development – Database Connection and Transaction Management

require_once($_SERVER['DOCUMENT_ROOT'] . '/FixedDeposit/adapters/DBAdapter.php');

/**

* Get the current auto number from the database.

* @param $prefix String Prefix for the auto number

* @return String Auto number or an error message if the auto number

* generation faild

*/

function getAutono($prefix)
{
        $DBAdapter = new DBAdapter;
   
        $query = "SELECT AUTO_pre_name, AUTO_current, AUTO_length

                        FROM sys_autono

                        WHERE AUTO_prefix = '$prefix'";

        $result = $DBAdapter -> runQueryDirect($query);


        $autoPreName; //prefix name
        $autoCurrent; //current number
        $autoLength; //length of the auto number

        if($result)
        {
            while($row = mysql_fetch_array($result))
            {
                $autoPreName = $row["AUTO_pre_name"];
                $autoCurrent = $row["AUTO_current"];
                $autoLength = $row["AUTO_length"];
            }

            //generate the auto number
            $autoNumber = $autoPreName . str_pad($autoCurrent, $autoLength, "0", STR_PAD_LEFT);
            return $autoNumber;
        }
        else
        {
            return "Error - Auto number not found";
        }
}


Now, after you create one serial number and used it you have to update “sys_autono” so the above function will not create the same number again. The following function is used to do so.

function updateAutono($prefix)
{
        $DBAdapter = new DBAdapter;
   
        $query = "UPDATE sys_autono

                    SET AUTO_current = AUTO_current+1,

                        AUTO_rec_date = curdate(),

                        AUTO_sync = 'N'

                    WHERE AUTO_prefix = '$prefix'";

        return $DBAdapter -> runQueryTrans($query);
}


When you want to generate a serial number all you have to do is this. Call “getAutono” method with the prefix name as the parameter.

$autono = getAutono("APP");

Then you can use that number for the required purpose. And afterwards update the auto number table by calling updateAutono with the prefix name as the parameter.

updateAutono("APP");