Categories
Learning Journal

Inserting Data in Multiple Tables

How to insert records of data into more than 1 database 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 just inserted data’s record ID and then do another INSERT on the next table and so on.

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 correct way to insert related data into 2 MySQL┬átables using mysql_insert_id(), here’s a sample database with 2 tables and some related 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   | www.gidforums.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 Larry. Fortunately for our article, Larry has a web site too! Now using mysql_insert_id(), here’s how you would usually insert all the submitted information into both tables in one script.

// sample new data to insert into our db
// -------------------------------------
$name = 'Larry';
$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:

 // get the user id
 $user_id = mysql_insert_id( $conn );

or

 // get the user id
 $user_id = mysql_insert_id();

and both would work fine.

3 replies on “Inserting Data in Multiple Tables”

I tried your code to use for my web site but it does not work,could you tell me what is wrong with it?
My version of php is 5.4 so I had to change it a little bit,it connects and inserts the user but does not update the website table.Thank you

<?php

$dbcnx = @mysql_connect("localhost", "root", "mypassword") or die(mysql_error());
echo "Connected to MySQL";
mysql_select_db('test', $dbcnx);
if (! @mysql_select_db('test') ) {
die( 'Unable to locate the hotel database at this time.');

}

// sample new data to insert into our db
// -------------------------------------
$name = 'Carlo';
$gender = 'm';
$site = 'www.whatever.com';
// -------------------------------------

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

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

// and insert the website details
$sql = "INSERT INTO website VALUES (NULL, '$site', $user_id)";
$result = mysql_query($sql, $dbcnx);
}
echo " Data inserted"
?>

I don’t know what to tell you, Carlo, because the code does seem to be correct. I also checked the official manual and the function is only deprecated from PHP v. 5.5 onwards. That’s not a reason for it to not work all of a sudden, even with PHP 5.4.

Are you using a debugger? It will be useful to know what $result is after the first SQL was processed.

Alternatively, you may want to edit that line into something like this:
$result = mysql_query($sql, $dbcnx) or die( "The first query failed!" );

Lastly, it is useful to know the schema of the MySQL tables you created for this exercise. I mean, have you set the `id` column in the `users` table to be AUTO_INCREMENT?

Hi thanks for your reply .I have set the id to auto_increment. Also to me seems allright, I get a message data inserted but when I check it only the user data has entered and nothing in the website table. I tried to change the connection as follows and I get this error:

Parse error: syntax error, unexpected ‘$name’ (T_VARIABLE), expecting ‘,’ or ‘;’ in C:\inetpub\wwwroot\inserting2.php on line 24

Leave a Reply

Your email address will not be published. Required fields are marked *