Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
03-25-2014, 12:24 AM #1
- Join Date
- Jan 2011
- The Pleiades
- Thanked 30 Times in 30 Posts
How do I 'reset' the ID field once deleting a row?
I am creating a mini custom CMS for my website and I have just created a little function where I can delete rows from my contact table. I am using Ajax to display and retrieve the data and it's working just fine.
Problem is, I just sent myself another message to check things and the id which has returned has left off from where the last id was.
I'm using mysqli prepared statements which you can see below:
$conn = new mysqli("#","#","#","#") or die();$stmt = $conn->prepare("DELETE FROM myTable WHERE id=?") or die();$stmt->bind_param("i", intval($_GET['rid']));$stmt->execute();$stmt->store_result();
03-25-2014, 03:05 AM #2
- Join Date
- Sep 2005
- Sydney, Australia
- Thanked 780 Times in 769 Posts
autoincrement id fields don't work that way. Their purpose is to uniquely identify each row that has ever existed. As records get deleted the ids for the deleted records will form gaps in what is not supposed to be a continuous sequence in the first place.
Whatever id gets used next will be unique - which is all that is ever guaranteed when you use ids.
While there are ways to reset the id it will cause issues unless you delete all of the data from the database at the same time and then reload whatever is to be kept after zeroing all the ids. Even then there is a possibility of errors.