Generate a reference number

Hello,

I need to generate a reference number with php and insert into mysql. Reference number will start with letter A and 3 digit number and when it goes to number 999 it will start with letter B and when it reaches letter Z it will start again from letter A.

A001
A002
A003
...

A999
B001
B002

...

Z999
A001

How can I do this?

 

You mean something like this?

function int2str ($value, $length = 3)
{
	while (strlen($value) < $length) {
		$value = "0{$value}";
	}
	return $value;
}

list ($reference) = mysql_fetch_assoc(mysql_query("SELECT reference FROM your_table ORDER BY reference DESC LIMIT 1"));
	$reference = $reference ? $reference : "A001";
	
	$number = substr($reference, 1, 3) + 1;
	$letter = chr(ord(substr($reference, 0, 1)) + floor($number / 1000));
	if ($number == 1000) {
		$number = 1;
	}
	if (ord($letter) > ord("Z")) {
		$letter = "A";
	}
	$newReference = $letter . int2str($number);

PHP:

You can test its output in a browser like this:

<?php

function int2str ($value, $length = 3)
{
	while (strlen($value) < $length) {
		$value = "0{$value}";
	}
	return $value;
}

for ($i = 1; $i < 4000; $i ++) {
	$reference = $reference ? $reference : "A001";
	
	$number = substr($reference, 1, 3) + 1;
	$letter = chr(ord(substr($reference, 0, 1)) + floor($number / 1000));
	if ($number == 1000) {
		$number = 1;
	}
	if (ord($letter) > ord("Z")) {
		$letter = "A";
	}
	$newReference = $letter . int2str($number);

	/* Echo old reference */
	echo $reference . "<br/>";
	$reference = $newReference;
}

?>

PHP:

 

waww. thank you very much. i will try and let you know.

thanks again

 

When I echo $newReference I get A002 all the time.

echo "Next reference number is: $newReference";

PHP:

<?
$dbhost = "localhost"; // this will ususally be 'localhost', but can sometimes differ
$dbname = "x"; // the name of the database that you are going to use for this project
$dbuser = "root"; // the username that you created, or were given, to access your database
$dbpass = "x"; // the password that you created, or were given, to access your database

mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());
mysql_select_db($dbname) or die( "Unable to select database");


function int2str ($value, $length = 3)
{
    while (strlen($value) < $length) {
        $value = "0{$value}";
    }
    return $value;
}

list ($reference) = mysql_fetch_assoc(mysql_query("SELECT reference FROM users ORDER BY reference DESC LIMIT 1"));
    $reference = $reference ? $reference : "A001";
    
    $number = substr($reference, 1, 3) + 1;
    $letter = chr(ord(substr($reference, 0, 1)) + floor($number / 1000));
    if ($number == 1000) {
        $number = 1;
    }
    if (ord($letter) > ord("Z")) {
        $letter = "A";
    }
    $newReference = $letter . int2str($number);

echo "Next reference number is: $newReference";
?>

PHP:

 

Ok. I did a small change and it worked. Thank you very much.

<?
$dbhost = "localhost"; // this will ususally be 'localhost', but can sometimes differ
$dbname = "x"; // the name of the database that you are going to use for this project
$dbuser = "root"; // the username that you created, or were given, to access your database
$dbpass = "x"; // the password that you created, or were given, to access your database

mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());
mysql_select_db($dbname) or die( "Unable to select database");


function int2str ($value, $length = 3)
{
    while (strlen($value) < $length) {
        $value = "0{$value}";
    }
    return $value;
}
	$query = "SELECT reference FROM users ORDER BY id DESC LIMIT 1";
	list ($reference) = mysql_fetch_row(mysql_query($query));
	

    
    $reference = $reference ? $reference : "A001";
    $number = substr($reference, 1, 3) + 1;
    $letter = chr(ord(substr($reference, 0, 1)) + floor($number / 1000));
    if ($number == 1000) {
        $number = 1;
    }
    if (ord($letter) > ord("Z")) {
        $letter = "A";
    }
    $newReference = $letter . int2str($number);
    
echo "Next reference number is: $newReference";

?>

PHP: