insert values into multiple tables

yamy · Jun 19, 2007

I am searching for codes examples that show how to use PHP with MySql to send form data into more than one table.

i can send form data into one table only,
no matter how i try, no data will go into the second table.

the sequence that works for data into a single table is:
1. set variables - $var = $_POST['var'];
2. check to see if form submitted - if (isset($_POST['submit'])); {
3. open the db connection - require_once=('/path/file.php');
4. specifiy insert table (col) -insert into tablename (column)
5. specify values (value) - values ('value')
6. check query success - $result=mysql_query($query)
7. echo confirmation -if ($result) echo "good"; else echo "bad"; }

i've tried various ways to add in the second table data with no success.
i cannot find any forum threads on this, except the one I already started.

All help welcome.

thanks
amy

yamy · Jun 19, 2007

ps - does it make sense that for every table i should have a separate form with its own submit button?
maybe that is the trouble with my script - there is only one form with many fields, that ultimately are headed for one of five different tables in the db.

thanks

jet042 · Jun 19, 2007

Your logic flow looks correct. I do not know of a way to insert into multiple tables with a single insert statement and looking at the MySQL documentation, I don't think there is one. What I would suggest is creating multiple inserts (one for each table) and then executing them in order. Basically, repeat steps 4 - 7 for each table.

yamy · Jun 19, 2007

thanks Jet, but I have tried exactly what you suggest with no success.
now i have a completely broken script that does nothing.

this is the last iteration i have tried, with no success:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>add_webForm</title>
</head>

<body> click on the link to add another record: 
<a href="en_webForm_061907.php">LINK</a>
<?php

$companyName = $_POST['companyName'];
$firstName = $_POST['firstName'];
require_once("./en/includes/mysql_connect_pb.php");

if (isset($_POST['submit'])) {

mysql_query("insert into company (companyName)
VALUES('companyName')");
mysql_query("insert into contact (firstName)
VALUES('firstName')");

$result=mysql_query($query);
if ($result) echo "added one record successfully.";
else echo "error";

}
echo " Thank you - $firstName $lastName - for the information. ";
echo "here is what you sent to the database - company Name - $companyName; first name - $firstName; last name - $lastName";

?>

</body>
</html>

thanks for any suggestions!

yamy

aardvark92 · Jun 19, 2007

Try adding this line:

Code:

echo mysql_errno($link) . ": " . mysql_error($link) . "\n";

after each mysql_query, and see if it gives you an error message.

aardvark92 · Jun 19, 2007

Sorry. That should be

Code:

echo mysql_errno() . ": " . mysql_error() . "\n";

yamy · Jun 19, 2007

you rock, aardvark!
that error message display was just the trick to show me the solution.

contact table pk was duplicating. i made it an autoincrement and then added the value NULL to the insert query.

voila!

thanks to all you teks for your tips - you are great.

Cheerio!
amy

vacunita · Jun 19, 2007

Try this:

Code:

<form action=testdb.php method="POST">
<input type=text name="firstname">
<input type=text name="lastname">
<input type=text name="companyname">
<input type=text name="companyphone">
<input type=submit name=submit>
</form>

Code:

<?
if(isset($_POST['submit'])){ [green]//this checks if the form has been submitted.[/green]

   $firstname=$_POST['firstname'];
   $lastname=$_POST['lastname'];
   $companyname=$_POST['companyname'];
   $companyphone=$_POST['companyphone'];

[green]//create queries to insert into DB.[/green]

$conn=mysql_connect("servername","user","password") or die(mysql_error());
$db=mysql_select_db("dbname",$conn) or die(mysql_error());
 
  $queryfortable1="INSERT INTO tablename1 VALUES('$firstname','$lastname')";
   $queryfortable2="INSERT INTO tablename2 VALUES('$companyname','$companyphone')";

[green]//Execute queries for both tables:[/green]

   $resulttable1=mysql_query($queryfortable1) or die(mysql_error());
   $resulttable2=mysql_query($queryfortable2) or die(mysql_error());

[green]//check that both results are o.k. If both variables are true, the submission has been completed successfully. [/green]

    if($resulttable1 && $resulttable2){
       echo "submission successful";
      }

    else{
         echo "There has been an error...";
        }

mysql_close($conn); [green]not usually necessary, but just for tidiness, we close the connection[/green]
}
else{
     echo "Form has not been submitted";
}

?>

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

jet042 · Jun 19, 2007

You are mis-using the mysql_query() function and have other errors in your syntax. Try this instead for your main if block

Code:

if (isset($_POST['submit'])) {
 mysql_query("insert into company (companyName) VALUES('" . mysql_real_escape_string($companyName) . "')") or die(mysql_error());
 mysql_query("insert into contact (firstName)
VALUES('" . mysql_real_escape_string($firstName) . "')") or die(mysql_error());

 echo "Inserts successful";
}

Your second if (the nested one) was syntactically incorrect and should have been throwing errors anyway and you really need to escape all user-supplied data before getting it anywhere near your database.

yamy · Jun 19, 2007

thanks for all the additional tips. I agree about cleaning up user data before it gets to the db and will take that on soon.
the mysql_query(insert.....) syntax i found online and it is working but i'll work on getting up to date with all the code.

?? the mysql_connect.php script contains the 'do it or die' command. Do i need to add it again to this page, or is it sufficient as is?

meanwhile -

using the mysql_insert_id function, i can retrieve the company table Primary Key. re-using the same variable for the insert into contact table, I end up with the value '0' in the company_pkid column. (default is NULL)

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>add_webForm</title>
</head>

<body> click on the link to add another record: 
<a href="en_webForm_061907.php">LINK</a>
<?php

$companyName = $_POST['companyName'];
$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
$contactPhone = $_POST['phone'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$contact_email = $_POST['email'];
$country = $_POST['country'];

require_once("./en/includes/mysql_connect_pb.php");

if (isset($_POST['submit'])) {

mysql_query("insert into company (companyName_pkid,companyName,address1,address2,city,zip,state,country)
VALUES(NULL,'$companyName','$address1','$address2','$city','$zip','$state','$country')");
$companyName_pkid = mysql_insert_id();
echo "your co pkid is :$companyName_pkid";

$result=mysql_query($query);
if ($result) echo "added one record successfully. ";

mysql_query("insert into contact (firstName,lastName,contact_pkid,contactPhone,contact_email,company_pkid)
VALUES('$firstName','$lastName',NULL,'$contactPhone','$contact_email','$company_pkid')");

//echo mysql_errno() . ": " . mysql_error() . "\n";
$result=mysql_query($query);
if ($result) echo "added one record successfully. ";

}
echo " Thank you - $firstName $lastName - for the information. ";
echo "here is what you sent to the database - company Name - $companyName; first name - $firstName; last name - $lastName; address 1 - $address1; address 2 - $address2; city - $city; zip - $zip; state - $state; country - $country; phone - $contactPhone; email - $contact_email";

?>

</body>
</html>

thanks

jet042 · Jun 20, 2007

Because each MySQL command used could fail and return an error, I will usually use the "or die()" structure after every call. For one thing, the rest of your script usually will not work unless the command fired correctly. Secondly, it really helps in tracking down pesky errors (like duplicating a pk). When you move the script from test to production you will want to replace the mysql_error() function with something like "Query failed" or "Could not connect" to lower your attack surface area (there's no sense in telling an attacker that your database has tables named... and they have columns named...).

Also, since you are enumerating the columns that you are trying to insert data into, you don't need to list contact_pkid or companyName_pkid in your INSERT statement if they auto-increment. MySQL will handle setting their values. It should work just as well (or better) if you change that query to read:

SQL:

insert into company (companyName,address1,address2,city,zip,state,country)
VALUES('$companyName','$address1','$address2','$city','$zip','$state','$country')"

The same goes for the INSERT into the contact table, but you do (apparently) need to retrieve the companyName_pkid value for the second query to maintain referential integrity between your tables. You are doing that with the mysql_insert_id() function correctly.

The only other thing that I see is that you are still calling mysql_query() more than you need to. I know that in other DB's you have to prepare a query with one function and then execute it with another, but MySQL does not require that. As soon as you call mysql_query() PHP sends that command to the database. I would suggest removing the lines like:

Code:

$result=mysql_query($query); [red]<-for one thing, you never define this variable[/red]
if ($result) echo "added one record successfully.<br /><br />";

Instead, add "or die(mysql_error())" after your previous calls to mysql_query().

vacunita · Jun 20, 2007

Whats with All those calls to mysql_query, call it once per query thats it. if you already called it with the query, then thats it. If you are calling mysql_query, always assign it to a value, $result or whatever you want so you can check the result.

If you are already doing this:

Code:

mysql_query("insert into company (companyName_pkid,companyName,address1,address2,city,zip,state,country)
VALUES(NULL,'$companyName','$address1','$address2','$city','$zip','$state','$country')");
$companyName_pkid = mysql_insert_id();
echo "your co pkid is :$companyName_pkid";

just add the $result to it.

Code:

[red]$result=[/red]mysql_query("insert into company (companyName_pkid,companyName,address1,address2,city,zip,state,country)
VALUES(NULL,'$companyName','$address1','$address2','$city','$zip','$state','$country')");
$companyName_pkid = mysql_insert_id();
echo "your co pkid is :$companyName_pkid";

then remove th additional call to mysql query, and go straight to check the $result variable.

The "or die" should go after each call to mysql_query, so if there is any error, you know about it.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

insert values into multiple tables

yamy

Technical User

yamy

Technical User

jet042

MIS

yamy

Technical User

aardvark92

Programmer

aardvark92

Programmer

yamy

Technical User

vacunita

Programmer

jet042

MIS

yamy

Technical User

jet042

MIS

vacunita

Programmer

Similar threads

Part and Inventory Search

Sponsor