Laravel make an autoincrement code/records and will reset daily

Question

I'm trying to make an autoincrement code with date and it will reset the code on the other day.

I have this migration.

public function up()
{
    Schema::create('records', function (Blueprint $table) {
        $table->id();
        //other codes here...
    });
}

Then, I add column that will auto increment 5 digits using

ALTER TABLE id ADD my_id AS RIGHT('00000' + CAST(id AS VARCHAR(5)) , 5) PERSISTED

I have this query on my controller

public function get_records(Request $request){
    $records = DB::table('records')->select(
        'records.my_id',
        //other codes here...
    )
    ->get();
    $recordlist = [];
    $currentMonth = Carbon::now()->format('mm');
    $currentDay = Carbon::now()->format('dd');
    foreach($records as $data):
        $data->CODE = 'Z' . $currentMonth . $currentDay . '-' .$data->my_id;
        $recordlist[] = $data;
    endforeach;
    return recordlist;

    // OUTPUT -->  CODE: 'Z0331-0001'
}

I would like my output like..

today is 03/31

CODE:
Z0331-00001
Z0331-00002
Z0331-00003
.....
Z0331-00010
Z0331-00011
Z0331-00012
.....
Z0331-00100
Z0331-00101
Z0331-00102

//The other day(04/01)..
CODE:
Z0401-00001
Z0401-00002
Z0401-00003
.....and so on

I just come up with this transaction.

I just add table,

ALTER TABLE records DROP COLUMN my_id

ALTER TABLE records ADD my_id VARCHAR(5) NOT NULL

Then, my query is..

$createdrecord = //last created record ex(CODE: 'Z0330-00265' created_at: '03/30(yesterday)' )
$today = Carbon::now();
foreach($records as $data){
    if($today != $createdrecord){
        $data->my_id = '00000';
    }else{
        $data->my_id += 1;
    }
}

I think this will OK, But my code doesn't go well..

Thank for your help.

Answer 1

You need a secondary table where you store counters. It has to have 2 columns:

• date
• counter

Every time you generate a record you have to check if there is a something in counters table for current date.

If it's empty - use ID = 1 and store it in counters table.

If it's not empty then use counter + 1 and increment counter

Answer 2

UPDATE In the process of trying to answer this question, we established that Author has MS Sql Server 2008, and not MySQL as originally stated.

Since MS Sql Server has Sequence objects, this idea could be implemented using a sequence naming scheme, where the sequences are named by Date.

1. Search for Sequence named "SEQ_YYYY-MM-DD". The name of a sequence object can't start with a number, so you can use the SEQ_ prefix to get around this. For example:

select count(*) as count_of from sys.sequences where name = "SEQ_2023_04_04"

2. If not found create one for the Day

create sequence SEQ_2023_04_04 START WITH 1 AS INT

3. Now you can use the sequence to generate sequence numbers

SELECT NEXT VALUE FOR SEQ_2023_04_04

Note:

I leave the PHP details of these queries to you. For example, to know what the sequence name is for the current day in your code, you could use

$seq = 'SEQ_' . date('Y_m_d');

I omitted Schema here, as I don't know what you are actually doing, but schema names may have to be referenced in these queries as appropriate. You should make sure you understand the MS Sql server concepts of database vs. schema.

---

This is a MySQL implementation of the idea provided by NoOoZ24, so make sure to give him an upvote/credit.

First you need a sequence table:

CREATE table sequence (id DATE PRIMARY KEY, counter int unsigned DEFAULT 1);

Here is a MySQL stored procedure that will act as your sequence generator:

DELIMITER //
CREATE PROCEDURE `getSequenceCounter`(
    IN p_date DATE
)
BEGIN
  DECLARE v_id DATE;
  DECLARE v_counter INT DEFAULT 0;
  DECLARE v_errorMsg VARCHAR(256);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  IF NOT EXISTS(
    SELECT *
    FROM sequence
    WHERE id = p_date
  ) THEN    
    INSERT INTO sequence (id) values (CURDATE());
  END IF;

  SELECT id, counter
  INTO v_id, v_counter
  FROM sequence
  WHERE id = p_date
  FOR UPDATE;

  UPDATE sequence SET counter = counter + 1
  WHERE id = v_id;
  COMMIT;
  SELECT v_counter as counter;
END;
//
DELIMITER ;

From a Command line, you can run this sproc using CALL:

call getSequenceCounter(CURDATE());

Looking at the code you should notice that it will create a sequence row for a date, should one not exist, and return you the initial value which will be 1.

call getSequenceCounter(CURDATE());
+---------+
| counter |
+---------+
|       1 |
+---------+

Run it again and you will see the next value, and so on.

Last I looked, there is not a way to call a mysql stored procedure from Eloquent, so you'll have to use DB::select or build the query. Both of those techniques are shown in answers to this question.