First Available Incrementation

Andros

i have a column that contains row data such as this:
Item1
Item2
Item3 etc...

Let's say that the row containing Item2 was deleted..
Now when another row is inserted i would like the data in that column to be the FIRST AVAILABLE INCREMENTATION...or in this case Item2 - NOT Item4

My goal is to keep roughly 300 rows at any one time and have one column that is sequential without missing a beat!

ideas?

blindeddie

I am curious as to why you want to do this. please elaborate if you don't mind!

Andros

i don't mind...and it is likely my not minding that has gotten me into this to begin with...but thats another enigma

my 300 Items are actually Objects used in a Flash Movie
My objects in the Flash movie call upon the Other Data in the table by querying based upon this one column's Incremented Data

here goes..

id Item Date Distance
1 Item1 May1 10000
2 Item2 May2 20000
3 Item3 May3 30000

An object in Flash Named Item1 displays data " May1" & "10000"
An object in Flash Named Item2 displays data " May2" & "20000"
An object in Flash Named Item3 displays data " May3" & "30000"

Now, when the Second row is deleted....Item2 will be gone.

When i go to insert a new row..i need it to be 'Item2' so that the related information is displayed within Flash based upon the value "Item2"

The Value, in this case 'Item2' must be static. Therefore, it must be replaced. It is not an immediate requirement that it be replaced...it will just not show data while 'Item2' is unavailable. But i cannot have 'Item301', 'Item302' etc.. otherwise an autoincrementing column would be fine.

you asked 🙂

BuzzLY

Off the top of my head... here's a solution. It might not be the most elegant or simple -- if there is a simpler one, someone please pipe up.

Do a query returning all items: SELECT item FROM movies ORDER BY item ASC

Next, put that list in an array.

Next, create an array with 300 elements, from Item1 to Item300

Perform an array_diff on the two arrays. This will return an array of all available values. Pick the first one in the array, and use that value when you do your insert.

I leave the coding up to you. Good Luck!

If there is an easy way to determine the first missing value in SQL, then by all means use it -- it will probably be faster. I just don't know how, if it's possible.

blindeddie

Ok one more question before I try to answer your question. When you are deleting a row and and inserting a new row, what information (Item, date or distance) is being changed? Now without a response from you I am going to give you my answer so if I am totally off base, let me know.

I would not delete the row in the database, I would just update the row. for example if you were changing just the distance field, it would go something like...

update movies set distance = '40000' where Item = 'Item2'

or by the id...

update movies set distance = '40000 where ID = '2'

That would update the row without upsetting your ID field numbering due to the autoincrementation.

Hope I a n the right track here... let me know

another thought is, since it seems that the ID needs to be static and not go past 300, disable autoincrement in that field and when you delete a record keep track of the record idandthe just insert that as well when inserting the new data. If you need to add NEW records to the database, just select the MAX(ID)field and add 1 to it beforeinserting the new record.

Andros

Blindeddie
thanks for your interest and input

First to answer your question:
Ideally, id and Item will remain constant...all other info is to be altered.

you Are on the right track
i suppose that i do Not have to totally delete the row
But when the row is updated..it is likely to be updated with Null Information at first..then a day or two later updated with Actual Data.
Now when this Actual Data is being inserted into the Empty Row..it first needs to know which row is Empty. If it was just me doing the data input i could easily search for a recordset that had no Data in it but I need to supply an end user with ONLY the available (empty) rows to choose from.

Is there a way to have a dropdown list which dynamically shows Empty Recordsets?

But better yet, have the First Available Empty recordset be the one to which the User will be filling with their information.

id=1 and Item=Item1 and Distance=10000
id=2 and Item=Item2 and Distance=20000
id=3 and Item=Item3 and Distance=30000

ie: if row with id=2 and Item=Item2 is emptied (distance is emptied, Item remains) then
How can i have a User go Directly to row id=2 when they are inputting their Data.

you mentioned "select the MAX(ID)field and add 1 to it beforeinserting the new record."
- would this not choose the highest ID currently existing
ie: 300 - and then make a new one called 301 ?

i hope this didn't complicate things
andros

mystrymaster

you could select from the database where date and distance = NULL and that would give them to you and then add the id as the value of the option drop down and the item name as the text the user sees and then do an update on the id

Andros

Thanks Guys
you got me on the right train

the solution was:

•creating an Update Page ( instead of an add record )
•sort the Update Page by Distance
an empty value brings the First Available Row to the top of the list. With no repeated region..it is the Only one they have a choice for.

Instead of using a Delete Record page..i will use another Update page where it deletes only the columns i specify..'Distance, Date etc..'
thus bringing it to the top of the List when the AddUpdate page sorts by 'Distance'

thanks again and i gotta say...the response has been excellent and Faster than i imagined.

This Site and the Wizards who occupy it are Dinamite!

BuzzLY

Rather than make it Null, you could make distance = '-1' in any available, updatable row. Any method would work, of course, as long as you used a value that would not normally be set. I don't like NULL because it is quite possible that some data in your table didn't get set right, and distance could contain NULL in a row that otherwise has good data in it. You would hate to overwrite that. By using a value like -1, you KNOW it's ok to overwrite it, because you set it that way on purpose.

Then, to find the first updatable row:

SELECT id FROM movies WHERE distance = '-1' LIMIT 1;

assign that value to $id, then do your update:

UPDATE movies SET distance = '$distance', item = '$item' WHERE id = '$id'