Increment even if delete record

Question

I have the following problem, I have the following function:

public function codigo_denuncia() {         
    $db = new mysql();

    //pegar as linhas do ano
    $ultimoRegisto = $db->executar("SELECT id FROM denuncia WHERE ano=year(now()) ");   

    //verifica se tem registro no ano 
    //se igual ao ano atual, incrementa, se não começa novamente com valor 1
        if($ultimoRegisto->num_rows > 0){
        return $ultimoRegisto->num_rows +1 . '-' . date('Y');
    }

    return 1 . '-' . date('Y');
}

At first it worked OK, always incrementing correctly, the problem occurs if by chance you need to delete a random record of this table, it would generate the next number with duplicate record, for example I have 40 record and insert one more, the next code would be 41-2017, after deleting one record from the table and entering another again, it would generate the same code 41-2017. Any viable alternative to that function? It was done with PHP and mysql and the call is as follows::

$codigo = $this->codigo_denuncia();
$arraycodigo = explode("-", $codigo);
$numero_denuncia = $arraycodigo[0].'/'.$arraycodigo[1];

Answer 1

You’re taking the number of existing records and adding one more is why it doubles when you remove a record

To correct try to do this way, take the id of the last record and sum one more:

 public function codigo_denuncia() {         
    $db = new mysql();

    //pegar as linhas do ano
    $ultimoRegisto = $db->executar("SELECT id FROM denuncia WHERE ano=year(now()) ORDER BY id DESC LIMIT 1"); 
    $row = $ultimoRegisto->fetch_assoc();

    //verifica se tem registro no ano 
    //se igual ao ano atual, incrementa, se não começa novamente com valor 1
    if($ultimoRegisto->num_rows > 0){

        return $row['id'] + 1 . '-' . date('Y');
    }

    return 1 . '-' . date('Y');
}