mysql problem

[Anon]

I am putting together a script using mysql for storage. my problem is with deleting item. If i delete a row, then i have a missing id number. unforunately my functions in many places are depend on having the ids in order. is there any way i can delete a row but not end up missing an id? I tried just setting up a function to change the ids but then it wont auto increment any longer. Does any one know how i might solve this?

[Anon]

no the problem is a little different the sequence is important but not in that way. What is important is that when returning ids i dont just have skips in number lik id=1 and then id=5000 The actual information in them isnt important. just that id is the easiest way to keep track of info but who do i do that if a use may delete any given row? Write the whole table to an array instead of trying to retireve a specific id trying to retrieve a specific element of an array?

[josephferris]

Jon,

I don't know if this will help you or not, but this is what I do. When I have a table with important information in it, I add an 'Archive' column which is a TINYINT.

Instead of deleting something, I set it's Archive value from 0 to 1. This way, I can "delete" without worrying about autoincrementing problems.

Show archived items:

SELECT * FROM TABLE WHERE ARCHIVED = 1

Show non archived items:

SELECT * FROM TABLE WHERE ARCHIVED = 0

Maybe this helps...

Joseph