Level 39
Write a recursive function that tests $reference_no
if it exists, elaborate next one (003, ...) by recalling your function
if not, create it and end your function
Aug 15, 2022
9
Level 4
PHP Auto Increment in Database
So i have a database which has contact_name and also referenc_no.
So when i save a fresh contact into the database it auto assigns the reference number by taking the first 4 letters from the name and add 001 to the back.
$name = 'John Doe';
$number = 1;
$justname = explode(' ', $name);
$firstname = $justname[0];
$reference_no = strtoupper(substr($firstname, 0, 4)) . str_pad($number, 3, '0', STR_PAD_LEFT);
E.G Contact name is John Doe so reference number is JOHN001.
But i want to know how i can write a code that checks if the name John exists in the database and add 002 to it and also 003 if another user with the name John exists.
The below code checks if the any record has the name John in it and adds 002 behind it
$jah = $contact_name;
$just = App::sql()->query_row("SELECT * FROM customer WHERE contact_name = '$jah'" );
$final = explode(' ', $jah);
$justfinal = $final[0];
if($justfinal == $firstname){
$name = explode(' ', $contact_name);
$majorname = $name[0];
$ref = 1;
$ref++;
$reference_no = strtoupper(substr($majorname, 0,4)). str_pad($ref, 3, '0', STR_PAD_LEFT);
}
The above code checks and adds 002 but if another record with john exists it does not add 003 it adds 002
Level 39
What do you not understand?
Level 10
First, If I understand things right, your query to the database to check referenc_no doesn't look good. It seems you're using an exact full contact name in the where clause but to create the calculated field you use only 4 characters.
So if the name is "Johnny" that query will miss all the "John"s that might previously been added and you'll end up creating another "john001". I am assuming you want those referenc_no unique so that would be a problem.
For example
$jah = substr($contact_name, 0,4); and then
$just = App::sql()->query_row("SELECT * FROM customer WHERE contact_name ilike '$jah%'" ); which will get you all the referenc_no for either john or johnny
Then you're pretty much done. Just iterate all those rows and parse the reference_no to get number portion of it and grab the max so you can easily get the next one.
I don't know if you have a unique index on that field but it might be a good idea to add one in case you run into some concurrency issue.
Level 122
You create a reference number
then check if database already contains that reference number.
If it does, increment and repeat.
Why are you bothering with customer name when checking for conflicting reference numbers?
Level 4
$row = App::sql()->query_row("SELECT * FROM customer WHERE reference_no LIKE '" . strtoupper(substr($firstname,0,4)) . "%'");
$number = $row[0] + 1;
$new_reference_no = strtoupper(substr($firstname,0,4)) . str_pad($number, 3, '0', STR_PAD_LEFT);
Level 122
@monstajamss Keep the number separate until you know its ok
$name = strtoupper(substr('John Doe', 0, 4));
$number = 1;
while(Customer::where('reference_no', $name . str_pad($number, 3, '0', STR_PAD_LEFT))->exists() {
$number++;
}
$customer->update(['reference_no' => $name . str_pad($number, 3, '0', STR_PAD_LEFT)]);
Please or to participate in this conversation.