I have an application which keeps database of all nearby restaurants. When adding a new entry, there is an option to add a few images of your restaurant.
Now the problem: I'd like to sort the different restaurant images into separated folders, named by restaurant id stored in mysql auto increment ID. The only problem here is, that i dont know that id in advance.
Form example:
text input - title
text input - address
text input - phone
file input - image
file input - image
So, what should I do now?
I. Get the last id, lock the table, create folder named by id, store images inside, store information to mysql database including image paths, unlock table.
or
II. Store all information excluding images paths to mysql database, use PHP mysql_insert_id, create folder named by id, store images inside and store images paths to mysql database.
or
III. Better solution?
This kind of thing is usually done with your option II. Store in the database the main row of information, get the last insert id via mysql_insert_id() or the native MySQL LAST_INSERT_ID() function, then proceed to store rows in any related tables if necessary and create the image directory to store filesystem data.
Assuming the image paths you intend to store in the database will go into a different table with a one-to-many relationship back to the main restaurant table, you'll need to know the last insert id to insert them anyway. Don't worry much about doing it in multiple operations -- that's exactly the reason most RDBMS have a function like LAST_INSERT_ID().
If you are using the autoincrement column. You can right after your insert statement call the last_insert_id() function to retrieve the id of the last inserted record.
See this link for documentation:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Its important that you do this within the same transaction/connection otherwise the value might be erroneous.
I don't see a need to use the restaurant's id to organize the pictures - especially since you get into the timing issues you describe. I'd create another unique id - call it picture_folder_id or something - and use that to name the folder for the pictures. As long as you enforce uniqueness on that id, you won't get any collisions, and you won't have any timing problems or locks.