Problem with mysql_insert_id()

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
smazmatshah
Forum Newbie
Posts: 1
Joined: Thu Nov 19, 2009 11:59 pm

Problem with mysql_insert_id()

Post 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.......
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Problem with mysql_insert_id()

Post by josh »

Use the MAX() function
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Problem with mysql_insert_id()

Post 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 :)
User avatar
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()

Post 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.
Last edited by Bill H on Sun Nov 22, 2009 9:31 am, edited 1 time in total.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: Problem with mysql_insert_id()

Post 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.
User avatar
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()

Post 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.
Post Reply