Jump to content


Photo

Get Next ID in a Table


  • Please log in to reply
13 replies to this topic

#1 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 29 September 2006 - 02:30 PM

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.


#2 jcbarr

jcbarr
  • Members
  • PipPipPip
  • Advanced Member
  • 219 posts

Posted 29 September 2006 - 02:34 PM

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.

#3 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 29 September 2006 - 02:40 PM

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

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 29 September 2006 - 02:46 PM

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!

#5 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 29 September 2006 - 02:48 PM

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

#6 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 29 September 2006 - 02:53 PM

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!

#7 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 29 September 2006 - 02:55 PM

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?

#8 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 29 September 2006 - 02:56 PM

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

me!

#9 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 29 September 2006 - 02:57 PM

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

#10 steveclondon

steveclondon
  • Members
  • PipPipPip
  • Advanced Member
  • 161 posts

Posted 29 September 2006 - 03:01 PM

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.

#11 jcbarr

jcbarr
  • Members
  • PipPipPip
  • Advanced Member
  • 219 posts

Posted 29 September 2006 - 05:55 PM

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.

#12 Flukey

Flukey
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 29 September 2006 - 06:05 PM

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.


Yes, precisely.

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

#13 counterbeing

counterbeing
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 29 September 2006 - 07:43 PM

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

#14 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 29 September 2006 - 07:54 PM

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

That's the best way
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users