I need to generate a code that is equal to id which will be auto-incremented in the database. It will be a routine that checks the last id generated and generates a code that will be id later before inserting the data.
For example:
If the last id was id 2 , the routine will generate code 3 which is the id that will be auto-incremented when the data is entered in the database.
How can I get this result with PHP and MySQL?
query to get the next ID:
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'nome_da_tabela'
AND table_schema = DATABASE();
Or one of these:
SHOW TABLE STATUS FROM nome_da_base_de_dados LIKE 'nome_da_tabela';
SHOW TABLE STATUS FROM nome_da_base_de_dados WHERE Name='nome_da_tabela';
Note that these queries are very specific, probably only serving in MySQL itself. The option with SHOW TABLE STATUS returns multiple columns , including the next auto-increment.
An alternative would be to give an UPDATE on the line you just inserted, using the column id + 1, which gives you the same as last_insert_id() .
Anyway, it is worth saying that probably this solution you are looking for sooner or later will give you trouble, either by insertion failure, two users trying to insert almost at the same time, or even the simple mismatch of data in the time for some removal.
I can not say this, because you did not give details of what you are doing, but from the initial question, I have a slight feeling that, whatever it is you're trying to do, maybe you have other ways to be resolved.
Can solve the problem with the following code:
LESS CREATIVE OPTION
public function geraCod(){
//Conexão
$db = new MysqliDb('localhost', 'root', 'usbw', 'atendimento');
//Seleciona o último código
$new_cod = $db->query('SELECT MAX(cli_cod) as id FROM clientes');
$new_cod = $new_cod[0];
$new_cod = ($new_cod['id'] + 1);
echo str_pad($new_cod, 4, "0", STR_PAD_LEFT);
}
SECOND OPTION:
public function geraCod(){
$db = new MysqliDb('localhost', 'root', 'usbw', 'atendimento');
$cols = Array ("clientes");
$new_cod = $db->rawQuery("SHOW TABLE STATUS FROM atendimento WHERE Name = ?", $cols);
$new_cod = $new_cod[0];
echo $new_cod['Auto_increment'];
}