I have a form which takes both the user details and an image uploaded by them. I want to write the data to a user table and an image table but i am pretty sure that it cannot be done with just two separate insert statements. Any help would be much appreciated.
3 Answers
You need to insert the user first, then do "SELECT LAST_INSERT_ID()" to retrieve the id of the user. Then you can insert the image in the image table with the newly created user id. In PHP you can actually use mysql_insert_id() to retrieve the new id. If you use mysql with InnoDB, you can also wrap the inserts in a transaction, by issuing BEGIN, followed by the INSERTs, followed by either COMMIT if everything is successfully added, or ROLLBACK in case of failure.
Comments
Why are you pretty sure it can't be done? Have you tried it? You insert into the user table, and then insert into the image table...no problems at all.
The only warning would be that you should wrap the inserts in a transaction so they act as a single insert and succeed/fail together.
Comments
$rec = mysql_query("insert into userdet values("$id","$username",....)"); if($rec) mysql_query("insert into imag values("$id","$imgname",...)"); 