Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    924
    Thanks
    76
    Thanked 29 Times in 29 Posts

    How do I 'reset' the ID field once deleting a row?

    Good evening,

    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:

    PHP Code:
    $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(); 
    Is there a certain command I can use to reset the id at the same time I delete a row?

    Kind regards,

    Tom.

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •