Get last auto increment PDO key

Question

Get last auto increment PDO key

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (0 votes)

2

I have a problem with my system that looks like this,

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'm getting 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 inserted the id 998, after inserting it I deleted it, 998 again, where it was certain to get the 999 id; Any light on that?

php sql pdo

asked by anonymous 26.10.2015 / 17:21

3 answers

How to read a excel file in C # and save the data into strings? [duplicate] What is it and how do I use it? Why use ViewDIdLoad?

score 0 · Accepted Answer

0

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

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";

26.10.2015 / 20:55

2

The two least bad outputs for this problem, which I see are:

1) Create a column named excluido or ativo it can be bit or boolean, instead of deleting the record, make an update in that 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 in your main table and in that new table and always read the last value in that table.

26.10.2015 / 17:38

2

If the table has a primary key with autoincrement you can query the value of the next record that will be inserted with the query below, since the value of AUTO_INCREMENT is not changed by row deletions.

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

This way should have the same effect as Nid+1 of the question.

26.10.2015 / 17:43