Jump to content

Archived

This topic is now archived and is closed to further replies.

Flukey

Get Next ID in a Table

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
So if #274 was deleted. The next auto increment number would #274 again? I thought it doesn't work like that.....

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
no, it would be 275, the next number. auto increment increases with each new INSERT, it [b]never[/b] decreases!

me!

Share this post


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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.