CODEWITHSUNDEEP
phpmysql
Abdi Abdi
Abdi Abdi

Reputation: 11

MYSQL+PHP How to update auto-incremented id when delete row in table?

 $q = "DELETE FROM newfiles WHERE id=$id LIMIT 1;";
 $q .= "SET @i=0;";
 $q .= "UPDATE newfiles SET id=(@i +:=1)";

Ex:- I have 6 Rows

1 2 3 4 5 6 when i delete Row 4 i want the row 5 to become 4 and 6 to 5

Upvotes: 0

Views: 268

Answers (1)

Muznious
Muznious

Reputation: 86

Generally this is not a good practice. But you can try like this. This will work.

$q = "SET @id = $id;";
$q .= "DELETE FROM newfiles WHERE id = @id;";
$q .= "UPDATE newfiles SET id = id-1 WHERE id > @id;";

Upvotes: 1

Related Questions