Get Next ID in a Table

[Flukey]

Hey guys,

I'm uploading an image, and the image names needs to be called the id from the next record in the table. Bearing in mind the field is an autonumber. Know of anyways to find out the next id? Cheers guys.

[jcbarr]

Query the table and order by the id field. That should give you the last id used and then just add 1 to that value and that will give you the next id that the table will use.

[Flukey]

True, but wouldn't this screw up if a record was deleted? Because the field is an autonumber, lets say it returned record id #275 and then it was deleted. When i query it again, it'll return record id #274. And then when i add one the image name will be called 275.jpg when in fact the record id would be #276 because it's an autonumber, thus it continues on, even though records have been deleted. Does that make sense? I hope so lol

[printf]

no it wouldn't, auto increment maintains the next row id even if a row or more is deleted, you shouldn't delete a auto increment row, just set to some flag, so you can UPDATE with new data at a later date.

me!

[Flukey]

So if #274 was deleted. The next auto increment number would #274 again? I thought it doesn't work like that.....

[printf]

It depends on how many rows the table has....

example...

If the table has 300 rows and you delete (250, 12, 18), the next auto increment number would still be!

#301

me!

[Flukey]

You're not answering my question fella.

If the last record was 274 and i deleted it, would the next record after i deleted it be 274 again?

[printf]

no, it would be 275, the next number. auto increment increases with each new INSERT, it [b]never[/b] decreases!

me!

[Flukey]

That was my whole blimin' point! lol Hence, why incrementing by 1 to get the image file name would be flawed.

[steveclondon]

You query the current highest id then add one

ie. $query="SELECT MyID FROM MyTable ORDER BY MyID DESC;

Then get the result from this and that will be your last id. Then add one and that is your new one.

[jcbarr]

Yes but what he's saying is that if the Highest ID in the table is 274, but the ids 275-280 were just deleted, the actual next ID is going to be 281 and the query is going to give you 274 and itn adding one is going to give you 275. When you actually insert the data in to the table it is going to be given the ID of 281.

[Flukey]

[quote author=jcbarr link=topic=109967.msg443841#msg443841 date=1159552525]
Yes but what he's saying is that if the Highest ID in the table is 274, but the ids 275-280 were just deleted, the actual next ID is going to be 281 and the query is going to give you 274 and itn adding one is going to give you 275. When you actually insert the data in to the table it is going to be given the ID of 281.
[/quote]

Yes, precisely.

I guess i could insert a blank field, find out what the id is, and then do sql update.

[counterbeing]

I'm actually dealing with a similar issue. I decided that it would be best just to not use the autonumber feature and use php to generate an ID based on how many rows there were. I'm no veteran but that seems like an easy solution.

Good Luck
C

[Barand]

[quote author=Flukey link=topic=109967.msg443848#msg443848 date=1159553157]
I guess i could insert a blank field, find out what the id is, and then do sql update.
[/quote]
That's the best way