-2

i am making s simple registration page using php and mysql database.

id column of Table is AUTO INCREAMENTED Registration Process working well i.e., data is getting Inserted in database.

currently i have 3 records in my table. like

  1. Raghvendra
  2. Surendra
  3. mohit

Now My Problem is:

When i deleted The 3rd Record i.e

  1. mohit

My database looks like

  1. Raghvendra
  2. Surendra

Now, When I add another Record, my table looks like

  1. Raghvendra
  2. Surendra

4.varun

What is want is like

  1. Raghvendra
  2. Surendra
  3. varun

sorry for my english..Help me!

Drew
  • 24,851
  • 10
  • 43
  • 78
Itz Raghu
  • 457
  • 2
  • 6
  • 16
  • 4
    oh man another "i hate gaps" question. First rational Answer gets my vote – Drew Sep 08 '15 at 11:34
  • 4
    Toss the auto-increment if you want to achieve something like that, and write your own counter, checking for every insert what the max value of ID is.. but then again, what's the point of using auto-increment?? – Naruto Sep 08 '15 at 11:35
  • 4
    Primary ids have no inherent meaning. Their guaranteed uniqueness is much more important than any OCD reason for having them be contiguous. In fact, as soon as you start linking other data to those ids, it can be pretty darn important to **never reuse ids**. – deceze Sep 08 '15 at 11:40
  • To achieve this, you should use manual increment & check for the max ID. – Harshit Sep 08 '15 at 11:41

2 Answers2

2

Don't worry about the gaps. Either you need a unique number for each person, in which case, reusing 3 is a bad idea (does it refer to Varun or Mohit?) or you're only concerned with the names, and you can use CSS (an ordered list) to number them on the webpage when they're displayed.

It's generally very useful to preserve the order in which people are added to a database, and trying to "fill in the gaps" prevents that.

Also, remember there's no guarantee that for every user you delete, leaving a gap, there will be a new user available to fill it.

William Turrell
  • 3,227
  • 7
  • 39
  • 57
  • 1
    I second that. Imagine if you had 1000 users and the 5th one was deleted. Does that mean you now need to insert the next person in that place, or renumber all the rest fall back a place? – Kevin Nagurski Sep 08 '15 at 11:46
1

The harsh answer is to stop caring what the id is (it's really just an internal unique identifier, so no need to show it to the user).

If you are displaying a listing to the user, just use an <ol> element.

echo '<ol>';

while ($row = $results->fetch_row()) {
    echo '<li>' . $row->name . '</li>';
}

echo '</ol>';

That will render the results are you expected, but you really should just treat the id as an internal thing and not worry about its actual value.

Kevin Nagurski
  • 1,889
  • 11
  • 24