problem with db table id

[Niloofar24]

Hello everybody!
There is a problem with my db table. For example i send 6 posts (with title, author and content) to the db table one after an other, so their post's id would be 1-2-3-4-5-6.
Then if i delete the post number 6 and then write a new post (which it's id must be 7), after that when i echo all posts on the page to see them, i see posts with ids 1-2-3-4-5-7 !!
How can i solve this problem? When i delete the post with id 6, the next post should be set there into id 6.

[DaveAmour]

Auto incrementing ids work this way by design. If you want more control then use a column you can explicitly control.

[lps]

Supporting @DaveAmour's comment. You probably set the id field into auto incrementing. What you should do to your work is don't display the ids to users and instead, display an index.

$index = 1;
foreach($result as $row){
    echo "<tr class='tr'><td>" . $index . "</td><td> <a href='single-post-page.php?post=" . $row['ID'] . "'>" . $row['Title'] . "</a> </td><td>" . $row['Author'] . "</td><td> <a id='".$row['ID']."' class='btn_dlt' href='#'> delete </a></td></tr>";
    $index++;
}

The post_id is only used in processes but not for display.

[Niloofar24]

Well, it doesn't work, because in every page we will have 3 posts and with using $index = 1; and $index++; we will have posts with id number 1 to 3 in each page. In the first page will have posts with id number 1-2-3 and in the second page, posts will be changed but the id number wich is $index infact, won't change.

[lps]

My code is just an example for reference, not meaning it can actually work.
But for your knowledge, if you consider of paging, then you can try to modify the codes into pagination-friendly coding.

$pg = (isset($_GET['pg'])) ? $_GET['pg'] : 1;
$per_page = 10;
$index = (($pg - 1) * $per_page) + 1;
foreach($result as $row){
    echo "<tr class='tr'><td>" . $index . "</td><td> <a href='single-post-page.php?post=" . $row['ID'] . "'>" . $row['Title'] . "</a> </td><td>" . $row['Author'] . "</td><td> <a id='".$row['ID']."' class='btn_dlt' href='#'> delete </a></td></tr>";
    $index++;
}

[DJBirdi]

Supporting everyone else's responses, yes an "id" field set to being UNIQUE and AUTO_INCREMENT is supposed to never be the same as anything that came before it. However, when you say you try to 'echo all the posts', I'm assuming you mean you try to print everything on a page in some sort of an organized list.

I've overcome this problem in the past by separating unique IDs in db's and index numbers on the page. For example, my db might look like this:

ID |  foo  |  bar
------------------
1  |  bla  |  bloo
2  |  gah  |  goo
20 |  doo  |  dah
53 |  goo  |  gah

...yet when I list that db on a user-viewable page, I just count the total rows and increment the index number so it shows:

ID |  foo  |  bar
------------------
1  |  bla  |  bloo
2  |  gah  |  goo
3  |  doo  |  dah
4  |  goo  |  gah

Hope that helps!