[Solved] Auto Insert into MySql Data Base

Question

This is quite simple:

1st what you want to do is to query the database for the previous registration no /ID.

something like this:

$query = mysqli_query($connect,'SELECT regCode From data ORDER BY regCode DESC LIMIT 1');

then you check if there is any result like this:

$results = count(mysqli_num_rows($query));

if it $results returns 0 (it is a fresh database) do this:
$regCode="A0001";

else:

create an array of alphabets.

$alphabet = ['B','C','D','E']; #from B to Z (A is omitted because it is the default prefix)

retrieve previous regCode like this:

list($regCode) = mysqli_fetch_row($query);

From here seperate the alphabet from the numbers

$prefix = substr($regCode,0,1);  //get prefix/alphabet
$storage_id = (int) substr($regCode,1,4) +1; //get code and add 1

Now test for the limit which in our case is 1000 (or any number you want):

$limit = 1000

if($storage_id>$limit){
    $storage_id= '0001'; //if limit exceeded reset storage id
    $index = array_search($prefix,$alphabet); //get alphabet/prefix index in the $alphabet array

    if($index>=0){
        $prefix = $alphabet[$index+1]; //Get next alphabet
    }
}else{
    //when limit is not exceeded do this
    if(strlen($store)!=4){ //check the length of our regCode (should be 4 letters in this case if not add some zeros)
        $storage_id = str_pad($storage_id,4,'0',STR_PAD_LEFT);
    }
}

$regCode = $prefix.$store; //combine alphabet to new storage_id
echo $regCode;

And that’s it !!!

This is the full code. Just copy this:

<?php
mysql_connect('localhost','root','');
mysql_select_db('test');

if(isset($_REQUEST['submit']))
{
$query = mysql_query('SELECT regno from details ORDER BY regno DESC LIMIT 1');
$results = mysql_num_rows($query);
if($results==0){
    $regCode="A0001";
    mysql_query("INSERT INTO details (regno) VALUES('{$regCode}')");
}else{
    list($regCode) = mysql_fetch_row($query);
    $alphabet = array('B','C','D','E');
    $prefix = substr($regCode,0,1);  //get prefix/alphabet
    $storage_id = (int) substr($regCode,1,4) +1;

    $limit = 1000;

if($storage_id>$limit){
    $storage_id= '0001'; //if limit exceeded reset storage id
    $index = array_search($prefix,$alphabet); //get alphabet/prefix index in the $alphabet array

    if($index>=0){
        $prefix = $alphabet[$index+1]; //Get next alphabet
    }
}else{
    //when limit is not exceeded do this
    if(strlen($storage_id)!=4){ //check the length of our regCode (should be 4 letters in this case if not add some zeros)
        $storage_id = str_pad($storage_id,4,'0',STR_PAD_LEFT);
    }
}
$regCode = $prefix.$storage_id; //combine alphabet to new storage_id
mysql_query("INSERT INTO details (regno) VALUES('{$regCode}')");
    }
echo $regCode;
}
?>
<form name="form" id="form" method="post">
<input type="submit" name="submit">
</form>

Accepted Answer