Account Number Generation

[bart]

When a users registers a domain on my web site, I want to automatically assign thenan account number from the first 3 letters of their domain name followed by 3 digits - 001. However, if for example test.com is registered the account number will be TES001, then if testing.com is registered, TES001 has already been assigned, so they should get TES002.

I have a MySQL table called Accounts which has a list of all accounts in use.

I have tried querying this table - SELECT MAX(account_id) from accounts WHERE account_id LIKE $acct_prefix% but can't seem to get it right, can anyone suggest a better way of doing this?

[barand]

That seems a reasonable approach. Depends what are you doing after the query. Something along these lines

$sql = "SELECT MAX(account_id) as lastused from accounts WHERE account_id LIKE '$acct_prefix%' ";

$result = mysql_query($sql);

if (mysql_num_rows($result) == 0) {
    $newaccount = $acct_prefix . '001' ;
}
else {
    $row = mysql_fetch_assoc($result);
    $num = substr($row['lastused'], 3);
    $num++;
    $newaccount = sprintf('%s%03d', $acct_prefix, $num);
}

hth

[bart]

This works if there are other account numbers in the table, ie if TES001 exists the $newaccount value is TEC002.

However, i tested the domain zzr.com and it returned ZZR000, it needs to be ZZR001. Any ideas?

Here is the code...

// Calculate the Account Number
	db_connect();
	$acct_prefix = strtoupper( substr ( "$domain_name",0,3 ) );
	$sql = "SELECT MAX(account_id) as lastused from accounts WHERE account_id LIKE '$acct_prefix%' ";

$result = mysql_query( $sql );

if ( mysql_num_rows ( $result ) == 0 ) {
$newaccount = $acct_prefix.'001';
}
else {
$row = mysql_fetch_assoc ( $result );
$num = substr($row['lastused'], 3);
$num++;
$newaccount = sprintf ( '%s%03d', $acct_prefix, $num );
}
mysql_close();
echo $newaccount;
exit;

[barand]

Sorry, forgot we're always going to get a row returned. This might be better

// Calculate the Account Number
    db_connect();
    $acct_prefix = strtoupper( substr ( "$domain_name",0,3 ) );
    $sql = "SELECT MAX(account_id) as lastused from accounts WHERE account_id LIKE '$acct_prefix%' ";

$result = mysql_query( $sql );

$row = mysql_fetch_assoc ( $result );

if ( is_null($row['lastused'] ) ) {
$newaccount = $acct_prefix.'001';
}

else {

$num = substr($row['lastused'], 3);
$num++;
$newaccount = sprintf ( '%s%03d', $acct_prefix, $num );
}
mysql_close();
echo $newaccount;
exit;

[bart]

perfect - thank you 😉