Generate sequential number within one month


How to generate sequential numbers consisting of year, month, 5-digit sequence. with the digits reset when changing the month?


14 // ANO
11 // MÊS

Getting: 141100000 .. 141100001 .. 141100002

When you change the month: 141200000 .. 141200001 .. 141200002

These numbers are assigned to a work order and saved in the DB, of course they will be unique.

My function looks like this:

function generateNumber(){
     return date('ym').'00000'; //141100000
asked by anonymous 17.11.2014 / 16:57

3 answers


You can extract the last service order number and generate a new one by doing a query that returns the highest number of that month and adding one more, if the query does not return anything it means that it is the first OS, as this does not change fix the code in another function.

function conectarBanco(){
    return new mysqli('localhost', 'usuario', 'senha', 'base');

function primeiraOrdemMes(){
    return date('ym'). '00001';

function novaOrdemServico(){
    $db = conectarBanco();
    $sql = 'SELECT max(numero_ordem_servico) as ultimo_id FROM ordem
            WHERE month(data_entrada) = month(now()) ';

    $result = $db->query($sql);

    //já existe uma ordem cadastrada no mês
    if($result === true){
        $ordem_servico = $result->fetch_assoc();
        return ++$ordem_servico['ultimo_id'];
        return primeiraOrdemMes();

$nova_ordem = novaOrdemServico();
17.11.2014 / 19:11

I wrote an algorithm that generates the id increment if there is already a case in the bank for the current month and year - otherwise it generates the first id.

public function geraId()
    $mysqli = new mysqli("localhost", "login", "senha", "db");
    $result = $mysqli->query("
        SELECT id
        FROM tabela
        ORDER BY id DESC
    if (sizeof($result) == 1) {
        $lastId = $result[0]["id"];
        $lastIdYear = substr($lastId, 0, 2);
        $lastIdMonth = substr($lastId, 2, 2);
        if ($lastIdYear == date('Y') && $lastIdMonth == date('m')) {
            $id = substr($lastId, 4, 5);

            return $id;
    $id = date('Y-m') . '00001';

    return $id;
17.11.2014 / 17:15

One possible solution to your problem would be to create a sequence in the database and reset it with a scheduler of your own database at the beginning of each month.

That way your code would look like this:

function generateNumber(){
    // obtem o numero da sequencia do banco de dados
    $proximoNumeroSequencia = $banco->proximoNumeroSequencia('nome_sequencia');

    return date('ym') . $proximoNumeroSequencia; //141100000

In banks like Oracle and PostgreSQL, it is easy to do this by using the CREATE SEQUENCE database command to create the sequence, the nextval command to get the next value of the sequence, and use the native scheduler to reset the sequence at the beginning of each month.

17.11.2014 / 17:14