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!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query - add values to multiple tables

Status
Not open for further replies.
yamy

yamy

Technical User
Nov 22, 2002
54
US
my db has multiple tables with an online form that captures data to go into one or more of these tables.

i have total success with a single table db - from user entered data to mysql db record - perfecct.

now i am testing the new multi-table db using just two of the tables, and i know the variables are set correctly because i use an 'echo' statement to print them on a confirmation page.

yet, there are no records in the db.

i've got the varibles written first as:

$fieldname1 = $_POST['fieldname1'];

then, I do the;

if (isset($_POST['submit'])) {
mysql_connect("mysql.ips.com","dbuser","dbpassword");
mysql_select_db("dbname");

then, i've got the insert query written in this sequence:

$query="insert into tablename1 (field1, field2, field3)";
$query.="VALUES ('$field1','$field2','$field3');
$query="insert into tablename2 (field4, field5, field6)";
$query.="VALUES ('$field4','$field5','$field6')";

---------------------

-----------------
note that the db has been set up with all the necessary tables (12) but i am testing it in steps.

The previously mentioned 'successful' test was with a test db that had only one table.

So I think there is some clever little mysql something that has to play here, and hoping there is a clever somebody here that can steer me in the right direction!

Many thanks!

yamy



 
Sort by date Sort by votes
I guess we can assume that your code looks like this
Code: 
...
$query="insert into tablename1 (field1, field2, field3)";
$query.="VALUES ('$field1','$field2','$field3');

[COLOR=red]mysql_query( $query );[/color]
...
$query="insert into tablename2 (field4, field5, field6)";
$query.="VALUES ('$field4','$field5','$field6')";

[COLOR=red]mysql_query( $query );[/color]
...
 
Upvote 0 Downvote
well, no, it does NOT look like that!

your red-letter insertions are what i don't have.
are they to separate the insert calls?

are the ... required also, or just shorthand for 'more like this'?

thanks
 
Upvote 0 Downvote
The
Code: 
mysql_query($query);
statement actually sends the query to the database. Without that, $query is just a php variable with some value that happens to look like an SQL query.
 
Upvote 0 Downvote
My elipses ( ... ) mean whatever other code you have in your script, etc.

Take a look at the PHP Manual to learn how to use MySQL with PHP.

This particular PHP function is at

From there you should be able to follow links to the whole set of functions used with MySQL.

The function I posted executes the query. Which is essential of course. It seems like the one-table script would have needed a call to this function also. Did your one-table scritp work without that?
 
Upvote 0 Downvote
Hi Rac2,
ok i think you might have nailed the problem on the head.
my working model uses:

$result=mysql_query($query)
followed by
if ($result) echo "some stuff";
else "echo some other stuff";

so, to follow your point, it seems I need to have my if ($result).... OR, your $mysql_query

and I think that each INSERT INTO table1 command must resolve before INSERT INTO table2.

do you agree?

thanks
Yamy
 
Upvote 0 Downvote
Probably. Just remember, computers do one thing at a time in the order in which you give them programmed intstructions. They neither know nor care what you intend to do.

So yes take of business on table 1. Then move on to table 2.

 
Upvote 0 Downvote
I can get an entry into the first table.(company)

i managed to get one entry into the second table (contact), one time - and then it stopped working when i itried to add a second field in the second table,

must be another of those dreaded 'typos'.

a well, this code does not print the echo statements following the closing else }

all help welcomed.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<html xmlns="<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>add_webForm</title>
</head>

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

$companyName = $_POST['companyName'];


if (isset($_POST['submit'])) {
require("./en/includes/mysql_connect_pb.php");
$query="insert into company (companyName)";
$query.=" VALUES('$companyName')";

}
mysql_query($query);

$firstName = $_POST['firstName'];

if (isset($_POST['firstName'])) {
require("./en/includes/mysql_connect_pb.php");
$query="insert into contact (firstName)";
$query.=" VALUES('$firstName')";

mysql_query($query);
} else {
echo " Thank you - $firstName $lastName - for the information. ";
echo "here is what you sent to the database -<br />company Name - $CompanyName; first name - $firstName; last name - $lastName";
}

?>

</body>
</html>

regards,
yamy
 
Upvote 0 Downvote
I can get the closing echo statements to print with this revision, but still cannot get entries into the second table


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<html xmlns="<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>add_webForm</title>
</head>

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

$companyName = $_POST['companyName'];


if (isset($_POST['submit'])) {
require("./en/includes/mysql_connect_pb.php");
$query="insert into company (companyName)";
$query.=" VALUES('$companyName')";


$result=mysql_query($query);
if ($result) echo "added one record successfully.";
else echo "error";
}
?>
<?php

$firstName = $_POST['firstName'];

if (isset($_POST['firstName'])) {
require("./en/includes/mysql_connect_pb.php");
$query="insert into contact (firstName)";
$query.=" 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 -<br />company Name - $CompanyName; first name - $firstName; last name - $lastName";


?>

</body>
</html>
 
Upvote 0 Downvote
:) this is realy the wrong place, you better switch to php forum

but to your problem
a) you may be getting error when requiring the same include file twice while output of error messages is supressed and you do not know the reason of not inserting data into db - use it in one general place (at the begining of doc?) or use require_once() function
b) you could better debug your code while using the get method first
c) for debuging purposes you can add an else part to the if(s) where you are checking for input variable(s)

...

<joke>z) get some php book</joke>
 
Upvote 0 Downvote
thanks, i will move to the PHP forum as suggested.
and,
i have lots of books, i wish i could find just one freaking example of this in a book or on line somewhere.
it's not for lack of trying, trust me.

byby
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
Need to compare two tables and list the differences
Replies
3
Views
201
EdLentz
EdLentz
lupidol
  • Locked
  • Question
mysql cannot add rows with &quot;UNION&quot; 1
Replies
5
Views
230
Chris Miller
Chris Miller
EdLentz
  • Locked
  • Question
How do I use a keyword in a query??
Replies
6
Views
257
EdLentz
EdLentz
BobThornton
  • Locked
  • Question
Drop/Truncate tables based on a stored procedures value 1
Replies
6
Views
270
BobThornton
BobThornton
AbidingDude
  • Locked
  • Question
One Constraint for Multiple Columns 1
Replies
2
Views
162
AbidingDude
AbidingDude

Part and Inventory Search

Sponsor

Back
Top