Inserting Data in Multiple Tables

How to insert data into more than 1 table in MySQL at a time? When I first started coding PHP scripts for myself, without researching further, I simply assumed that I had to INSERT data into multiple tables by inserting part of the data into 1 table, query that table to get that last inserted data's id and then do another INSERT on the next table.

IF this is how you do it too, trust me, there are a lot of us out there... Fortunately for us, we now have this article and one less mystery to solve .

You could shake your head in disbelief but even the 'popular and highly rated' book on PHP that I had bought for myself didn't mention this little gem of a function: mysql_insert_id().

Sample database

To help explain the 'better' way to insert data into 2 MySQL tables using mysql_insert_id(), here's a sample database with 2 tables and some data:

TABLE : users
=====================
 id | name   | gender
---------------------
 1  | John   | m
 2  | Mary   | f
 3  | Smith  | m
 4  | George | m

TABLE : website
===========================
 fid | url             | id
---------------------------
 1   | www.desilva.biz | 2
 2   | gidhelp.com     | 4

Examining the tables above closely, you'll quickly figure out that only Mary and George have submitted websites by the id number referenced in the WEBSITE table.

mysql_insert_id()

Let's assume that we had just received a new registration for a user named Jim. Fortunately for our article, this new user has a website to submit too! Now using mysql_insert_id(), here's how you would usually insert all the submitted information into both tables in one script.

<?php
// sample new data to insert into our db
// -------------------------------------
$name = 'Jim';
$gender = 'm';
$site = 'www.google.com';
// -------------------------------------

// Inserting the data into users
// -----------------------------
$sql = "INSERT INTO users VALUES (NULL,'$name','$gender')";
$result = mysql_query( $sql,$conn );

#  if the user submitted a website
if( isset($site) )
{
  #  get the user id  
  $user_id = mysql_insert_id( $conn );

  #  and insert the website details
  $sql = "INSERT INTO website VALUES (NULL,'$site',$user_id)";
  $result = mysql_query( $sql,$conn );
}
mysql_free_result( $result );
// -----------------------------

?>

I don't like $conn, can I leave it out?

According to the PHP Manual, the link identifier is optional. i.e. you could have either typed:

<?php
  #  get the user id  
  $user_id = mysql_insert_id( $conn );
?>

or

<?php
  #  get the user id  
  $user_id = mysql_insert_id();
?>

and both would work fine...