Page 1 of 1

[Resolved] Getting latest id

Posted: Wed Jun 06, 2007 4:57 am
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?

Re: Getting latest id

Posted: Wed Jun 06, 2007 5:00 am
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.

Re: Getting latest id

Posted: Wed Jun 06, 2007 5:07 am
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... :)

Posted: Wed Jun 06, 2007 5:12 am
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.

Posted: Wed Jun 06, 2007 5:15 am
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 ??

Posted: Wed Jun 06, 2007 5:17 am
by volka
Yes. Since the file is uploaded you're using move_uploaded_file anyway. Rename it then and there.

Posted: Wed Jun 06, 2007 5:18 am
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.

Posted: Wed Jun 06, 2007 5:20 am
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?

Posted: Wed Jun 06, 2007 5:22 am
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

Posted: Wed Jun 06, 2007 5:29 am
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.

Posted: Wed Jun 06, 2007 5:43 am
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.

Posted: Wed Jun 06, 2007 5:44 am
by volka
The way it should be.

Posted: Wed Jun 06, 2007 5:48 am
by kaisellgren
volka wrote:The way it should be.
Thanks, problem resolved. Thread can be closed, thank you.