how to generate a serial number provided that it is not exist in the database

Question

is there anyone who can tell me the best way to do this?

1. I am trying to generate a serial number with a unique code

2. I want to check whether the serial number exists or not in the database

3. if the serial number already exists in the database, it will generate a new serial number and will insert afterwards.

4. if there is no serial number, then insert it directly into the database

               <div class="col-sm-9">
                 <input type="text" name="sn" value="AXJ<?php echo rand(10000, 99999) ?>" class="form-control" id="sn" placeholder="" readonly>
               </div>
   

my question is, how is the function to check in the database?

Answer 1

I've created an example. Beginning with a test database and table structure of:

-- Database: `serials`

DROP TABLE IF EXISTS `serial`;
CREATE TABLE IF NOT EXISTS `serial` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `serial` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

Create a db.php file to connect to the database:

function connect()
{
    $dsn = 'mysql:host=localhost;port=3306;dbname=serials';

    try{
        return new PDO($dsn, 'root', '');
    }catch( \Exception $e ){
        echo $e->getMessage();
        exit;
    }
}

If you aren't ready for insert yet and just want to display a unique SERIAL to the user, I created form.php and did this:

<?php
require_once('./db.php');

    function createSerial() {
        $timestamp = time() . rand(10000, 99999);

        for($i=1;$i<=strlen($timestamp);$i++){
            $piece = substr($timestamp, $i - 1, 1);
            $shuffle_array[] = $piece;
        }

        shuffle($shuffle_array);

        return implode('', $shuffle_array);
    }

    $pdo = connect();

    //try up to 5 times
    for($i=0;$i<=4;$i++) {
        $serial_number = createSerial();

        $sql = "SELECT * FROM serial WHERE serial = :serial";
        $dbh = $pdo->prepare($sql);
        $dbh->execute( [
            ':serial' => $serial_number
        ]);

        if( $dbh->rowCount() ) {
            $serial_number = '';
        }
    }


if( $serial_number) { ?>
<form action="form.php" method="post">

    <div class="col-sm-9">
        <input type="text" name="sn" value="AXJ<?php echo $serial_number; ?>" class="form-control" id="sn" placeholder="" readonly>
    </div>

<?php
}
?>

Also did an example for inserting :

function createSerial() {
    $timestamp = time() . rand(10000, 99999);

    for($i=1;$i<=strlen($timestamp);$i++){
        $piece = substr($timestamp, $i - 1, 1);
        $shuffle_array[] = $piece;
    }

    shuffle($shuffle_array);

    return implode('', $shuffle_array);
}

//database
$pdo = connect();

//try up to 5 times
for($i=0;$i<=4;$i++) {
    $serial_number = createSerial();

    $sql = "SELECT * FROM serial WHERE serial = :serial";
    $dbh = $pdo->prepare($sql);
    $dbh->execute( [
        ':serial' => $serial_number
    ]);

    if( ! $dbh->rowCount() ) {
        //insert

        $sql = "INSERT INTO serial VALUES ( :id, :serial)";
        $dbh2 = $pdo->prepare($sql);
        $dbh2->execute([
            ':id' => null,
           ':serial' => $serial_number
        ]);
        break;
    }
}