I am working on a site where a user can create a post and upload a couple of images for that post. When the user creates a post, I need to get the ID of the post that is about to be created in order to create a folder (with a corresponding name) for the uploaded images. The table "posts" has autoincrementation on "id". I have tried using the logic pasted in below, but I have a feeling that it's not the way to go.
editData("LOCK TABLES posts READ");
$highest = getRow("SELECT MAX(id)+1 AS id FROM posts");
$_SESSION["id"] = $highest["id"];
editData("UNLOCK TABLES");
I then use the value of $_SESSION["id"] for the creation of the folder for the images, but it doesn't seem completely foolproof.
What I want to achieve is basically to manage the situation where two users starts creating a post at the same time. In this case, I need to be sure that the images that user A has uploaded does not get confused with the ones being uploaded by user B. Any suggestions on this?
I think you should not do it this way. A better option (among several) could be
If you are worried about thread-safety of this, read the following quote from the MySQL manual"
"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."
We are talking about connection based "last", so that means you are not influenced by other inserts. Also, see http://php.net/manual/en/function.mysql-insert-id.php
I would create a temporary directory to upload the files to. You can use PHP rand() as part of the directory name to ensure uniqueness, then get a new ID then rename the directory as part of the upload process.
In the file that handle's the form you should insert the pictures, then get the id of them and create the directory.
Here is how I would approach it:
//create the table (with a boolean value to keep it hidden until everything is done)
$r = mysql_query("INSERT INTO foo VALUES(x, y, z, false)");
//retrieve the auto_increment id
$post = mysql_fetch_array(mysql_query("SELECT id FROM foo WHERE x=x y=y z=z..."), MYSQL_ASSOC);
//create your dir with the id and your image uploads
do_stuff();
//flip the boolean field
mysql_query("UPDATE foo SET show=true WHERE id=$id");
Just return the mysql_insert_id...