Register sequential number per year PHP [closed]

Question

Register sequential number per year PHP [closed]

Navigation

#1 by (2 votes)

0

I wanted to create an automatic numbering record but would reset every year. For example, 170001 ... 170002 and for the year 180001 ... 180002

But with this code I am always registering 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 was able to do the code. When you need to, here's 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();
?>

php mysql html

asked by anonymous 19.12.2017 / 13:25

1 answer

Can not validate "undefined" - JS Printing php results in divs

score 2 · Accepted Answer

In pure SQL you can get the next serial like this:

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)

It should probably be unnecessary to say, but you need to enter the ID in the bank, so the next one is obviously increased.

Points of Interest:

CONCAT : joins several values in a string only;
LPAD : completes the leading zeros of id ;
COALESCE : chooses a default value if the ìd (the first of the year) is null.

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

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