Record sequential number per year PHP

Question

I wanted to create an automatic numbering record but reset each year. Getting for example 170001...170002 and for the year 180001...180002

But with this code, I always register 1700001

<?php 
   function conectarBanco(){
      return new mysqli('localhost', 'root', '', 'bvmarco');
   }
   function primeiraOrdemAno(){
      return date('y'). '00001';
   }
   function novaOrdemServico(){
      $db = conectarBanco();
      $sql = 'SELECT max(Id) as `ultimo_id` FROM `participacao` WHERE year(data) = year(now()) ';

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

   if($result === true){
      $ordem_servico = $result->fetch_assoc();
      return ++$ordem_servico['ultimo_id'];
   }else{
      return primeiraOrdemAno();
   }
   }
   $nova_ordem = novaOrdemServico();
?>

<input type="hidden" name="id" class="form-control" value="<?php echo $nova_ordem ?>" required>

With help I managed to make the code. When you need a tip.

<?php
   function conectarBanco(){
      return new mysqli('localhost', 'root', '', 'bvmarco');
   }
   function primeiraOrdemAno(){
      return date('y'). '00001';
   }
   function novaOrdemServico(){
      $db = conectarBanco();
      $sql = 'SELECT max(Id) as `ultimo_id` FROM `participacao` WHERE year(data) = year(now()) ';
      $result = $db->query($sql);
      if($result === true){
         $ordem_servico = $result->fetch_assoc();
         return ++$ordem_servico['ultimo_id'];
      }else{
         return primeiraOrdemAno();
      }
   }
   $nova_ordem = novaOrdemServico();
?>

Answer 1

In pure SQL you can get the next serial so:

SELECT
   CONCAT(
      YEAR(CURRENT_DATE) % 100,
      LPAD( COALESCE( MAX(serial_do_ano), 0 ) + 1, 4, '0' )
   ) AS serial
FROM
   tabela
WHERE
   YEAR(CURRENT_DATE) = YEAR(data)

Probably it should be unnecessary to say, but you need to enter the ID in the bank, so that the next one is incremented, obviously.

Points of interest:

• CONCAT: unites several values in one string only;
• LPAD: complements the zeros to the left of the id;
• COALESCE: chooses a value default if the ìd (the first of the year).

In case we use 4 in the LPAD. If you want the number of boxes not to be limited, you can adapt with a IF, sort of like this:

IF( valor > 9999, valor, LPAD( valor, 4, '0' ) )