insert 2 or more tables at once using sql query

Question

sumandy 0 Newbie Poster

13 Years Ago

Create table Cameras
	(CameraID varchar (35),
	 CheckOutDate varchar(10),
	 ReturnDate varchar(10),
	 primary key(CameraID),
	 foreign key(CameraId) references History(CameraID));


	Create table Borrowers
	(PsuID int not null,
	 BorrowerName varchar(30),
	 Email varchar(50),
	 Telephone varchar(12),
	 ReasonUse varchar(200),
	 primary key(PsuID),
	 foreign key(PsuID) references History(PsuID));




	Create table History
	(PsuID int not null,
	 CameraID varchar (35),
	 BorrowedFrequency int,
	 primary key(PsuID, CameraID),
	 foreign key(PsuID) references Borrowers(PsuID),
	 foreign key(CameraID) references Cameras(CameraID));


This is my table

I want to insert Cameras and Borrowers at the same time

<?php
$CameraID	= $_POST['cameraid'];
$CheckOutDate	= $_POST['checkoutdate'];
$ReturnDate	= $_POST['returndate'];
$PsuID = $_POST['psuid'];
$BorrowerName = $_POST['borrowername'];
$Email	= $_POST['email'];
$Telephone = $_POST['telephone'];
$ReasonUse	= $_POST['reasonuse'];


//Connect To Database
$hostname="localhost";
$username="your_dbusername";
$password="your_dbpassword";
$dbname="your_dbusername";

mysql_connect($hostname,$username, $password) or die ("ERROR: Unable to connect to database!");
mysql_select_db($dbname);






#Add new entry
	
$sql = "insert into Borrowers() values ()";
$result = mysql_query($sql, $conn) or die(mysql_error());

$sql = "insert into Cameras() values ()";
$result = mysql_query($sql, $conn) or die(mysql_error());

Hope I can get some helps,

Thanks

php sql

2 Contributors
5 Replies
124 Views
12 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by sumandy

smantscheff 265 Veteran Poster

13 Years Ago

Change

insert into Borrowers() values ()

to

insert into Borrowers values (NULL,'$BorrowerName','$Email','$Telephone','$ReasonUse')

Do likewise with the Camera table insert.

smantscheff 265 Veteran Poster

13 Years Ago

Insert does not need a where clause because you do not insert at a specific location other than the table. You just insert a row with some values.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sumandy 0 Newbie Poster · Answer 1 · 2011-02-08T23:59:25+00:00

Thanks, I would put the attribute in, but I was wondering if that is legitimate

Let's say I want all three tables to be involved

$sql = "insert into Borrowers(contents here) values ($contents here)";
$result = mysql_query($sql, $conn) or die(mysql_error());

$sql1 = "insert into Cameras(contents here) values ($contents here)";
$result1 = mysql_query($sql, $conn) or die(mysql_error());

$sql2 = "insert into History(contents here) values ($contents here)";
$result2 = mysql_query($sql, $conn) or die(mysql_error());

Is this right?
Do I need any Where Clause code?

Thanks a lot

sumandy 0 Newbie Poster · Answer 2 · 2011-02-09T05:16:45+00:00

This is my HTML code:

this is my php code

But it's not working still
it has errors like:

Notice: Undefined index: cameraid in /websites/RFID/checkout.php on line 4

Notice: Undefined index: checkoutdate in /websites/RFID/checkout.php on line 5

Notice: Undefined index: returndate in /websites/RFID/checkout.php on line 6

Notice: Undefined index: psuid in /websites/RFID/checkout.php on line 7

Notice: Undefined index: borrowername in /websites/RFID/checkout.php on line 8

Notice: Undefined index: email in /websites/RFID/checkout.php on line 9

Notice: Undefined index: telephone in /websites/RFID/checkout.php on line 10

Notice: Undefined index: reasonuse in /websites//RFID/checkout.php on line 11
... etc
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /websites/RFID/checkout.php on line 31

What else can I do?

Thanks a Lot!

sumandy 0 Newbie Poster · Answer 3 · 2011-02-09T05:18:52+00:00

Sorry I made a mistake before

This is my html code

<html>

<head>
<title>Check Out</title>










<style type="text/css">
.style1 {
	font-weight: bold;
	text-decoration: underline;
}
</style>




</head>

<body background="wall.jpg" text="navy">

<center><img src="wblogo.png"></center>

<br>
<form method="post" action="checkout.php">

<center>
<table width="500" border ="1" cellpadding="4" cellspacing="0" BORDERCOLOR="navy">
<tr>
	<td colspan="2">
		<center style="width: 488px; height: 46px"><b><h1>Check Out </h1> </b></center>
	</td>
</tr>
<tr>
	<td style="height: 36px">
		<b> Camera ID: </b>
	</td>
	<td align="left" style="height: 36px">
		<input name="CameraID" size="50" style="width: 294px; height: 23px">
    </td>
</tr>
<tr>
	<td>
		<b> PSU ID: </b>
	</td>
	<td align="left">
		<input name="PsuID" size="50" style="width: 294px; height: 23px">
    </td>
</tr>
<tr>
	<td>
		<b> Borrower Name (First/Last): </b>
	</td>
	<td align="left">
		<input name="BorrowerName" size="50" style="width: 294px; height: 23px">
	</td>
</tr>


<tr>
	<td>
		<b> Phone: </b>
	</td>
	<td align="left">
		<input name="Telephone" size="50" style="width: 294px; height: 23px">
    </td>
</tr>
<tr>
	<td>
		<b> E-mail: </b>
	</td>
	<td align="left">
		<input name="Email" size="50" style="width: 294px; height: 23px"> 
	 </td>
</tr>
<tr>
	<td>
		<b> Date Borrowed: </b>
	</td>
	<td align="left">
		<input name="CheckOutDate" size="50" style="width: 294px; height: 23px"> 

	</td>
</tr>



<tr>
	<td>
		<b> Date to be Returned: </b>
	</td>
	<td align="left">
		<input name="ReturnDate" size="50" style="width: 294px; height: 23px"> 
    </td>
</tr>
<tr>
	<td class="style1">
		Reason for use: 
	</td>
	<td align="left">		
		<textarea name="ReasonUse" style="width: 295px; height: 246px;"></textarea>
		
		
	</td>
</tr>




<tr>
	<td align="left">
		<input type="reset" value="Clear" style="width: 80px; height: 26px">
	</td>
	<td align="right">
		<input type="submit" value ="Check Out">
	</td>	
</tr>
</table>
</center>

</form>













</body>

</html>

This is my php code

<?php
$CameraID	= $_POST['cameraid'];
$CheckOutDate	= $_POST['checkoutdate'];
$ReturnDate	= $_POST['returndate'];
$PsuID		 = $_POST['psuid'];
$BorrowerName	 = $_POST['borrowername'];
$Email		= $_POST['email'];
$Telephone 	= $_POST['telephone'];
$ReasonUse	= $_POST['reasonuse'];





#Connect To Database
$hostname="localhost";
$username="sxc455";
$password="P0tassium19";
$dbname="sxc455";

mysql_connect($hostname,$username, $password) or die ("ERROR: Unable to connect to database!");
mysql_select_db($dbname);

	
#Add new entry


$sql = "insert into Cameras(CameraID, CheckOutDate, ReturnDate) values ('$CameraID', '$CheckOutDate', '$ReturnDate')";
$result = mysql_query($sql, $conn) or die(mysql_error());

$sql1 = "insert into Borrowers(PsuID, BorrowerName, Email, Telephone, ReasonUse) values ($PsuID, '$BorrowerName', '$Email', '$Telephone', '$ReasonUse')";
$result1 = mysql_query($sql, $conn) or die(mysql_error());




print("
	
	<center>
	<table width='400' height='200' cellpadding='4' cellspacing='0'>
	  <tr align='left'>
	    <td>
	      The Entry <b>"); print($PsuID); print(" </b> was added to the database
	    </td>
	  </tr>
	</table>
	</center>");
	
?>