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
Something like insert_id_upcoming();

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.