0

I want to give my database record a unique code that contains a string of VDR-000X (X should be auto incremented).

So far, I have created a logic in my PHP file that look like this. It works fine but since it should be generated in the server side, I think doing this on MySQL so that it fills up the field automatically by default (like the id field) is more appropriate. But I don't know how. Is there any SQL commands that can do this?

My implementation uses Laravel framework, answers with laravel migration blueprint is prefered but native MySQL query is fine too and I will try convert it myself. Please give it a shot!

/**
 * Store a newly created resource in storage.
 *
 * @param  \App\Http\Requests\StoreVendorRequest  $request
 * @return \Illuminate\Http\Response
 */
public function store(StoreVendorRequest $request)
{
    // We want it to fill up the `code` field later, so we made a variable for the validated request.
    // User should not be able to input this manually so we do this here
    // on the server side. The `code` is permanent and shouldn't be updated either,
    // just like the `id` field
    $validated = $request->validated();

    // Before we fill the code, grab the `id` of the latest record
    $lastId = Vendor::latest()->orderBy('id', 'desc')->first()->id;

    // Increment the `$lastId` and give it at least 4 leading zeros
    $code = Str::padLeft($lastId++, 4, '0');

    // Set the `code` value
    $validated['code'] = "VDR-{$code}";

    // Store it in the database
    $created = Vendor::create($validated);

    // Return the result
    return new VendorResource($created);
}

The result should look like this

+---------------------------------------------------------------------------+
| id  | name         | code     | created_at          | updated_at          |
+---------------------------------------------------------------------------+
|   1 | something    | VDR-0001 | 2022-03-19 00:00:00 | 2022-03-19 00:00:00 | 
|   2 | something2   | VDR-0002 | 2022-03-19 00:00:00 | 2022-03-19 00:00:00 |
| ... | ...          |      ... |                 ... |                 ... |
| ... | ...          |      ... |                 ... |                 ... |
| 123 | something123 | VDR-0123 | 2022-03-19 00:00:00 | 2022-03-19 00:00:00 |
+---------------------------------------------------------------------------+

EDIT

I found this similar question about How to make MySQL table primary key auto increment with some prefix but it doesn't do what I want to do which is actually store the code automatically by that format in the field and let the id as is.

Yura
  • 1,937
  • 2
  • 19
  • 47
  • 1
    If You are using MySQL 5.7 or newer You can use [Generated column](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) (I even would suggest it) – Bogdan Kuštan Mar 19 '22 at 08:46
  • 1
    I tried your suggestion with the `id` column, it doesn't allow it since it was `auto increment`. Error: `Generated column 'code' cannot refer to auto-increment column` – Yura Mar 19 '22 at 09:09

1 Answers1

0

You can use BEFORE INSERT trigger:

DELIMITER $$
$$
CREATE DEFINER=`admin`@`%` TRIGGER UpdateCode
BEFORE INSERT
ON your_table FOR EACH ROW
SET NEW.code = concat('VDR-', lpad((SELECT MAX(id) from your_table) + 1, 4, '0')) $$
DELIMITER ;
Bogdan Kuštan
  • 5,427
  • 1
  • 21
  • 30