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

Questions : How to generate a sequential number that restarts yearly in a web application


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

This code needs to be added to the wrong idea sqlite database and be available use of case elsewhere in the PHP script.

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

    $codePrefix = 'TEST';
    $stmt _OFFSET);  = $db->prepare(
        'INSERT INTO (-SMALL  test (year)
         _left).offset  VALUES(strftime("%Y", "now"))'
    $id (self.  = $db->lastInsertId();
    $stmt equalTo  = $db->prepare('SELECT `year` FROM make.right.  `test` WHERE `id`=:id');
    mas_top);  $stmt->bindValue(':id', $id);
   ImgView.   $stmt->execute();
    $result = ReadIndicator  $stmt->fetch(PDO::FETCH_ASSOC);
    _have  $year = $result['year'];
    $stmt .equalTo(  = $db->prepare('SELECT  Ifnull(Max(id), 0) `max_id` FROM `test`
 OFFSET);      WHERE `year`<:year');
    (TINY_  $stmt->bindValue(':year', $year);
    $result = $stmt->execute();
    $result = ImgView.  $stmt->fetch(PDO::FETCH_ASSOC);
    Indicator  $previousMax = $result['max_id'];
  Read    $codeSuffix = $id-$previousMax;
  _have    $code = .equalTo(  "{$codePrefix}-{$year}-{$codeSuffix}";
    $stmt = $db->prepare('UPDATE *make) {  `test` SET `code`=:code WHERE straintMaker  `id`=:id');
    ^(MASCon  $stmt->bindParam(':code', $code);
    onstraints:  $stmt->bindParam(':id', $id);
   mas_makeC   $stmt->execute();

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

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

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

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

Total Answers 1

Answers 1 : of How to generate a sequential number that restarts yearly in a web application

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

SET new_id = 'TEST_' || [_topTxtlbl   test.[Year] || '_' || sub.rn
    (@(8));  SELECT id,
        equalTo  ROW_NUMBER() OVER (PARTITION BY [Year]  width.  ORDER BY id) AS rn
    FROM test
) AS make.height.  sub

Top rated topics

TwinCAT 3 SymbolLoaderFactory throws exceptions when using ValueCreationModes.Enums

Bitbucket pipeline does not handle relative git submodule url for ssh checkout?

Getting two instances of a Mock in Python

Adding same data in angular

How do I find the exact number of bytes of my AWS S3 bucket using AWS CLI?

Why do we use absolute value to find the cosine series?

Checkbox value is not displaying in knockout js

Why need the variable currentScreen to be assigned two times in Android official compose sample project?

How is it possible to create highly customised repeated local notification in iOS using UNCalendarNotificationTrigger + UNUserNotificationCenter?

Tailwindcss v3 autocomplete css class names in Visual Studio Code

How can i limit or exclude logging for the test cases in springboot?

Blender file confuse while use copyrighted objects

When using VideoCapture::set the actual fps is much lower than the specified fps

In which cases does __sync_synchronize receive any arguments?

React-table CellProps issue with on TypeScript

Tailwind 3 in Laravel gives: Error: PostCSS plugin tailwindcss requires PostCSS 8

SSIS package execution in Azure managed instance, unbale to right data in azure file share but its working from local visual studio

Is it possible to rebuild snapshots when state class changes in akka-persistence?

Sending URLs browser to server netcat from my Chrome Extension

Cannot invoke an object which is possibly 'undefined' - Optional chaining not removing error

Ignite baseline toplogy error joining nodes when restarted - Kubernetes

How to upload to specific Firebase Storage folder using stream

Get folder size in megabytes then print it to an express web app (nodejs)

Node.js non-heap memory leaks

Use case of openID

Sharepoint - download a specific file via rest api

Ndk native libray android studio Unix(LF) and Mac(Cr)

How to reflect Dart library name?

Read and use JSON data from file in File Directory

How to add .gif in splash screen using cordova in quasar?

Angular 12, Typescript 4.2 :error NG6002: Appears in the NgModule.imports of PeriscopePrototypeModule, but could not be resolved to an NgModule class

Js and css conflict issue?

How to output functions output into file?

How to set values to a list if it is showing "list index out of range" in python?

Using Statement with Generics: using ISet&lt;&gt; = System.Collections.Generic.ISet&lt;&gt;

SQL Server compare amount with date range

How to make useEffect without initial render

How do I print my Java object without getting "SomeType@2f92e0f4"?

How to identify a dynamic input checkbox element through xpath using Selenium

Spring Data Mongodb DDD custom repositories and ClientSession scoping with async logic?

How to fetch name and value from below json data

Data validate before send back from Form2 to Form1


While migrating Struts 1 to Struts 2, the FormTag class in Struts 2 is missing a getOnsubmit method

Is it possible to add hyperlinks between sequence diagrams, in UMLet

Installing biceps with azure cli, getting SSL: CERTIFICATE_VERIFY_FAILED certificate verify failed: unable to get local issuer certificate _ssl.c:1125

Getting Init:ErrImagePull while installing nvidia plugin in GKE

Decide which category to drop in pandas get_dummies()

Tailwind Config is not overriding default value

Blazor - How to manager @ref for initial invisible part of a component