[Resolved] Getting latest id

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

[Resolved] Getting latest id

Post by kaisellgren »

Hi,

I have a database with id int unsigned not null auto_increment so the id increments automatically by 1 each time i insert something.

My questions is. I have 10 iteams in my db, when I delete the latest one, I have 9 items but the latest id is still 10. So how do I get this latest id?

Now I have used

SELECT id FROM table ORDER BY id DESC;

And then my $latest_id = $result +1;

But if I have deleted the latest row, then my biggest id would be 9 although my latest id is 10.

Help?
Last edited by kaisellgren on Wed Jun 06, 2007 5:47 am, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: Getting latest id

Post by volka »

kaisellgren wrote:My questions is. I have 10 iteams in my db, when I delete the latest one, I have 9 items but the latest id is still 10. So how do I get this latest id?
You don't. Just let it be. That's what the auto in autoincrement stands for. Let mysql handle it and do not fiddle with that value - unless there's a good, a very good reason.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Getting latest id

Post by kaisellgren »

volka wrote:
kaisellgren wrote:My questions is. I have 10 iteams in my db, when I delete the latest one, I have 9 items but the latest id is still 10. So how do I get this latest id?
You don't. Just let it be. That's what the auto in autoincrement stands for. Let mysql handle it and do not fiddle with that value - unless there's a good, a very good reason.
Well I need to know that!

Because I upload a file a zip file.

it will be like this:

1.zip
2.zip
3.zip
...

When I delete the file 10.zip, and I upload new, the newest uploaded file will be also 10.zip ! I need it to be 11.zip. Hopefully you got this... :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

kaisellgren wrote:When I delete the file 10.zip, and I upload new, the newest uploaded file will be also 10.zip !
Not with an autoincrement value.
kaisellgren wrote:I need it to be 11.zip.
That's what autoincrement does. It gets incremented each time you insert a record not matter what records you've deleted, always up by one.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

volka wrote:
kaisellgren wrote:When I delete the file 10.zip, and I upload new, the newest uploaded file will be also 10.zip !
Not with an autoincrement value.
kaisellgren wrote:I need it to be 11.zip.
That's what autoincrement does. It gets incremented each time you insert a record not matter what records you've deleted, always up by one.
Yes but how do I get the upcoming ID value?

Do I first need to insert my file into db and then use mysql_insert_id and then rename my file ??
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Yes. Since the file is uploaded you're using move_uploaded_file anyway. Rename it then and there.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

volka wrote:Yes. Since the file is uploaded you're using move_uploaded_file anyway. Rename it then and there.
lol. I came here to see if there is a more clever solution for this.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What would be more clever? Or the other way round, what's not clever about this solution?
You didn't expect a built-in function insertDbRecordAndRenameFileAccordingly(), did you?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

volka wrote:What would be more clever? Or the other way round, what's not clever about this solution?
You didn't expect a built-in function insertDbRecordAndRenameFileAccordingly(), did you?
No :D

Something like insert_id_upcoming(); :P
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You would have to lock the database table to make this safe since there is always the possibility of a concurrend thread grabbing that id between you first function call and the actual insert operation. It's not worth the efford.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

volka wrote:You would have to lock the database table to make this safe since there is always the possibility of a concurrend thread grabbing that id between you first function call and the actual insert operation. It's not worth the efford.
Well.

I made it now like this:

1) Inserts data to db
2) Grabs mysql_insert_id();
3) Renames the file

And it works well so I am happy now.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

The way it should be.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post by kaisellgren »

volka wrote:The way it should be.
Thanks, problem resolved. Thread can be closed, thank you.
Post Reply