How to generate a sequential number that restarts yearly in a web application-CodePudding

I need to generate a code which consists of some arbitrary prefix, a year, and an incrementing number. The incrementing number must start at 1 at the first time when the number is generated that year.

This code needs to be added to the sqlite database and be available elsewhere in the PHP script.

What i have done now uses 4 accesses to the database:

    $codePrefix = 'TEST';
    
    $stmt = $db->prepare(
        'INSERT INTO test (year)
         VALUES(strftime("%Y", "now"))'
    );
    
    $stmt->execute();
    
    $id = $db->lastInsertId();
    
    $stmt = $db->prepare('SELECT `year` FROM `test` WHERE `id`=:id');
    
    $stmt->bindValue(':id', $id);
    
    $stmt->execute();
    
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $year = $result['year'];
    
    $stmt = $db->prepare('SELECT Ifnull(Max(id), 0) `max_id` FROM `test`
     WHERE `year`<:year');
    
    $stmt->bindValue(':year', $year);
    
    $result = $stmt->execute();
    
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $previousMax = $result['max_id'];
    
    $codeSuffix = $id-$previousMax;
    
    $code = "{$codePrefix}-{$year}-{$codeSuffix}";
    
    $stmt = $db->prepare('UPDATE `test` SET `code`=:code WHERE `id`=:id');
    
    $stmt->bindParam(':code', $code);
    $stmt->bindParam(':id', $id);
    
    $stmt->execute();

Here i am abusing the fact that the id is an integer primary key, and autoincrements.

This works. But i feel that it is doing something very easy in a very complicated manner.

Is there a better solution? I need to assume that the midnight of the first of January can happen at any moment of the code, so i cannot do things like get the year information from PHP without hitting the database.

Before somebody asks, the reason i am using prepared statements even when no values are bound is because late on obviously more data will be inserted into the table.

CodePudding user response：

Consider a pure SQL solution using the ROW_NUMBER window function. Below assigns to new field, new_id:

UPDATE test
SET new_id = 'TEST_' || test.[Year] || '_' || sub.rn
FROM (
    SELECT id,
        [Year],
        ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) AS rn
    FROM test
) AS sub
WHERE test.id = sub.id;