Grab last key from auto increment PDO

Question

I have a problem with my system that is the following,

try {
$sql = "SELECT Nid+1 AS Nid  FROM noticias ORDER BY Nid DESC LIMIT 1";
$stmt = $DB->prepare($sql);
$stmt->bindValue(":Nid", intval($_GET["Nid"]));

$stmt->execute();
$results = $stmt->fetchAll();
$Npasta = $results[0]["Nid"];

} catch (Exception $ex) {
echo $ex->getMessage();
}

I am taking the last registered ID and creating a folder with the same number but when I delete the last id the count becomes wrong because EX: I entered the id 998, after inserting deleted, the next that I will insert it will get 998 again , where the right was to get the id 999; Some light on that?

Answer 1

The two less bad exits to this problem, which I see are:

1) Create a column called excluido or ativo can be bit or Boolean, instead of deleting the record, update this column, this partially solves the problem of holes in the sequence.

2) Create a new table with two columns an id and the other the value of the last Insert, for each record record in its main table and this new one and always read the last value in this table.

Answer 2

If the table has a primary key with autoincrement you can consult the value of the next record that will be inserted with the query below, because the value of AUTO_INCREMENT is not amended by line exclusions.

SELECT `AUTO_INCREMENT`
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'noticias';

That way should make the same effect as Nid+1 of the question.

Answer 3

searching on the subject I found this method that is in my opinion the safest because it takes directly the value of auto_increment follows the final code of the solved problem

try {
$sql = "SHOW TABLE STATUS LIKE 'nomedatabela' ";  
$stmt = $DB->prepare($sql);
$stmt->execute();
$resultado = $stmt->fetch();
$proximoID = $results['Auto_increment'];  // a chave esta aqui
} catch (Exception $ex) {
 echo $ex->getMessage();
}
echo "$proximoID";

Done even if the id is deleted it will always fetch the next one to be inserted