Primary Key Auto Increment Order

[himynameismark]

I am working on a blog for a friend's website. Upon testing the database which holds her blog posts (for security issues and whatnot), and dropping random rows from it, I have noticed that the id field does not re-organize itself. The way I will be displaying her posts (5 per page, with previous and next buttons) depends on the id being in order and with no gaps. Is there any way I can put in a script using PHP or MySQL to organize these in case of a row being removed once the blog goes live? Also, I have noticed that I can put in one entry, drop it, and then when I test another one the id starts back at 2. The only way to get it to start at 1 again that I have seen is going into PHPmyAdmin and emptying the table. Thanks,Mark

[Synook]

That's just the way AUTO_INCREMENT works - if you think about it, it would be pretty weird for the ID of something to just change randomly based on the actions made on another record - like my employee number changing every time someone leaves the company. Ideally, you should change the behavior of your page so that it doesn't depend on the absolute ID of the post (are you using absolute positioning?), but if you really need that behavior then you will have to remove the AUTO_INCREMENT attribute and manually increment/modify the IDs.

[thescientist]

Yeah, I'm not sure why the view needs to be coupled to the data structure. Can you explain why and in what way you have chosen to implement that? To paginate you should just be using some sort of session object to track current page and views per page (5) to know where in the result to start from, and to end.

[Synook]

You can use the two-argument variant of the LIMIT clause to display a certain number of records, beginning from a certain one.