Problem with mysql_insert_id()
Moderator: General Moderators
-
smazmatshah
- Forum Newbie
- Posts: 1
- Joined: Thu Nov 19, 2009 11:59 pm
Problem with mysql_insert_id()
Hi...I need some help please.... I have a form with 10 file fields in it for uploading images to the server and saving the names of the file into the database.On the server side i am creating separate folder for each record's images....the name of the folder is (DIR + mysql_insert_id() +1)....i.e for each record ...the folder name of the images is DIR1 , DIR2,DIR3 etc...and all this works just perfect....but if i come tomorrow morning....and for the first instance if i have to upload images to the server...probably it returns the value of mysql_insert_id() as empty or unknown....therefore the file upload returns an error that the directory already exists....i guess mysql_insert_id() returns a value on per session or connection basis...so what should i do so that i can get the last auto incremented id without that problem....or is it something else which is causing this error?only the first attempt at the start of the day to upload those images will return errors...otherwise it doesnt.......
- Jonah Bron
- DevNet Master
- Posts: 2764
- Joined: Thu Mar 15, 2007 6:28 pm
- Location: Redding, California
Re: Problem with mysql_insert_id()
Use the MAX() function
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: Problem with mysql_insert_id()
mysql_insert_id() will always return the auto-incremented value for the last inserted row. guaranteed. the only time it wont is if there is no auto-incremented column (always returns 0), or if there's no connection to the database (returns FALSE). So, if you're not getting the result back that you expect, either the query has failed (0) or there's no connection (FALSE). Any other scenario and that call will work.smazmatshah wrote:probably it returns the value of mysql_insert_id() as empty or unknown....therefore the file upload returns an error that the directory already exists....
Without seeing any code its hard to say exactly how you're going about creating the directories, but the error 'the directory already exists' says it all, you're trying to create a directory that is already there. try echo'ing out the name of the directory you're trying to create so you can compare it against the actual folder structure to see if it already exists or not. the error message should tell you specifically which line and file thats occured in, so that'd be a good place to start!
p.s. welcome to the forum
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Problem with mysql_insert_id()
mysql_insert_id() returns the last insert performed in the selected database regardless of table, so it should be called immediately after the insert is performed.
I'm not sure what happens if a database is selected, an insert performed, another database selected, the original reselected and then mysql_insert_id() executed. Under those circumstances, it might well consider that no insert has been performed in the currently selected database.
I think the "no insert performed" might be even more likely if a database is selected, an insert performed, database connection is closed, connection is reestablished and the original database selected and then mysql_insert_id() executed.
Josh is right, MAX(id) will give you the last id number used.
But you shouldn't need to know the last id, under most circumstances. When inserting with autoincrement use a value of zero for the autoincrement field and the database will insert the correct (next) id number. If you are uploading new images, the first one you use will get the next number in line due to the autoincrement feature and mysql_insert_id() will give you that number for that drir creation.
I'm not sure what happens if a database is selected, an insert performed, another database selected, the original reselected and then mysql_insert_id() executed. Under those circumstances, it might well consider that no insert has been performed in the currently selected database.
I think the "no insert performed" might be even more likely if a database is selected, an insert performed, database connection is closed, connection is reestablished and the original database selected and then mysql_insert_id() executed.
Josh is right, MAX(id) will give you the last id number used.
But you shouldn't need to know the last id, under most circumstances. When inserting with autoincrement use a value of zero for the autoincrement field and the database will insert the correct (next) id number. If you are uploading new images, the first one you use will get the next number in line due to the autoincrement feature and mysql_insert_id() will give you that number for that drir creation.
Last edited by Bill H on Sun Nov 22, 2009 9:31 am, edited 1 time in total.
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: Problem with mysql_insert_id()
I think in this scenario it would be guaranteed, the mysql_insert_id() command is connection specific so a new connection would clear the last insert id.Bill H wrote: I think the "no insert performed" might be even more likely if a database is selected, an insert performed, database connection is closed, connection is reestablished and the original database selected and then mysql_insert_id() executed.
Afaik even if you change databases it should still return the correct insert id because its connection specific not database specific, but I can't say I've ever tried.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Problem with mysql_insert_id()
Yeah, I should have realized that. The only way it would lose the result would be if someone was using temporary connections and reconnecting with each database change.Afaik even if you change databases it should still return the correct insert id because its connection specific not database specific, but I can't say I've ever tried.
Still, calling it immediately after the INSERT would be the best thing.