Sequential Unique IDs

thecrypto

I'm working on a journal program in PHP and MySQL and I'm trying to find a way to do Sequential IDs for the posts. However, I will be deleting entries and that would leave holes if I used auto_increment. Is there a way to do this with as little database thrashing as possible?

Anon

If you're deleting rows, how do you expect the IDs to stay sequential? Autoincrement is the best way to go... perhaps if you can explain what you're trying to do we can find the best way to do it.

Andy

--
<a href="http://www.keen.com/andy+triggs">http://www.keen.com/andy+triggs</a>

thecrypto

OK,
I'm making a journal. A journal is made of posts. I keep track of the posts by an ID number. However, I need to know what the previous entry and the next entry are if you are in the middle of the journal. The only way I can keep track of the things in order is an ID number that is sequential. Sometime people like to remove entries they've made in the past. But, with the current system, if I want to delete 1 post, I have to move all the posts after it's ID number 1 back to stop any holes from being made. Basicially, I want to know if A) How to get the IDs sequential without any holes with the least about of database accessing or 😎 a better way to implement sequiental entries

mattr

You don't need to know what the previous/next ID is, that is what SQL is for.

SELECT MIN( id ) AS nextID
FROM yourtable
WHERE id > $currentID

SELECT MAX( id ) AS previousID
FROM yourtable
WHERE id < $currentID

thecrypto

That's exactly what I needed. Thank you so much.