Page 1 of 1

Problem with mysql_insert_id()

Posted: Fri Nov 20, 2009 12:09 am
by smazmatshah
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.......

Posted: Fri Nov 20, 2009 3:10 pm
by Jonah Bron
I'm not sure if this is the best solution, but you will need to save the value of mysql_insert_id() to a small text file, with fopen and fwrite.

BTW, this is the general discussion forum. If you want an answer to a code question, post in the PHP-Code forum.

Welcome to the forum.

Re: Problem with mysql_insert_id()

Posted: Fri Nov 20, 2009 6:38 pm
by josh
Use the MAX() function

Re: Problem with mysql_insert_id()

Posted: Sat Nov 21, 2009 4:01 am
by iankent
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....
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.

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 :)

Re: Problem with mysql_insert_id()

Posted: Sun Nov 22, 2009 9:24 am
by Bill H
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.

Re: Problem with mysql_insert_id()

Posted: Sun Nov 22, 2009 9:30 am
by iankent
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.
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.

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.

Re: Problem with mysql_insert_id()

Posted: Sun Nov 22, 2009 9:34 am
by Bill H
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.
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.

Still, calling it immediately after the INSERT would be the best thing.