Inserting a record in db and maintaining order

[yellowBee]

Hi,

How do I attack this problem? :evil:

I've records in a special order. When I want to insert a record in between other records, how do I preserve the new order?

Example:
record A
Record B
Record C

I want to insert a record between B and C.

Anyone?

thnx

Rico

[pootergeist]

you don't

there is no reason for wanting to store records in any order in a database - you should only want to manipulate the order when you pull the records out - using ORDER BY methods.

[yellowBee]

I agree. but the order by needs a key to order. How to choose a key and maintaining it?

[pootergeist]

ORDER BY can reference any field, whether a key or not.

You should though have at least one index field in any table.

[yellowBee]

Hi Pootergeist,

You don't understand my problem. I need to be clearer.
The ORDER BY is not the issue.

The records are in a particular order in the DB. With the ORDER BY I get them in the right order on my screen. I want to insert a new record somewhere between. The place is determined by a key. How to choose the value for the key?

new example:
Key content
10 direction 1
20 direction 2
30 direction 3

Need to add a direction between key 20 and 30.
Ok you say, Give the key a value of 25. Thats ok, but I don't know how many extra directions needs to be added so maybe 9 extra couldn't be enough.
The key's needs to be changed very often, and that gives a lot of programming, and not foolproof I think.

So I need another way to maintain the keys.

I hope this clarifies my problem.

Thnx in advance,
Rico

[*JaH*]

well you can ofcourse give the new entry the right id value and add one to the ids of the ones above (or the same) as the id you gave to the new value...it's shitty though.

Could you tell us why you want it like that? There must be something you order it by, because otherwise you couldn't tell where to put the new record

[yellowBee]

Hi,

It will be ordered by the key. For every record there will be a button to insert a record before the one where it is showing.

The whole idea is to develope a route-programm. the visibile fields are, direction, image-id, some remarks.
The not visibile field is the key for ORDER BY.

cheers,
Rico