The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> PHP Development
|
PHP-DB - Inserting data through the php form
Discuss Inserting data through the php form in the PHP Development forum on Dev Shed.
Inserting data through the php form PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
December 19th, 2012, 01:08 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 13
Time spent in forums: 4 h 27 m 9 sec
Reputation Power: 0
|
|
|
PHP-DB - Inserting data through the php form
I have created a form in php. In mySqli database, I have-
Customer Table: customerNo, name, phoneNo, email, address, city;
TableOrder Table: orderNo, time, customerNo, orderType.
This is my query in php form to insert into customer table:
$query_str="INSERT INTO Customer (lName, fName, phoneNo, Email, address, city) VALUES ('$_POST[lName]','$_POST[fName]','$_POST[phoneNo]','$_POST[email]','$_POST[address]','$_POST[city]')";
And query to insert into TableOrder Table ( *orderNo is auto increment*, *time is timestamp*, ***customerNo is foreign key of customer table**) :
$query_str="INSERT INTO TableOrder (orderType) VALUES ('$_POST[orderType]')";
But I don't have any idea about how to insert customerNo in TableOrder which is foreign key of Customer Table !!!!!!!
Any Suggestion would be appreciated. Thank you!!
|
December 19th, 2012, 01:35 PM
|
|
|
|
This sounds like a MySQL question rather than a PHP question. Please clarify and if a MySQL question please move this to the MySQL forum (click the red triangle in the upper right). If a PHP question, show your code (using [ PHP ] tags, see the sticky at the top of this forum) and what error you are getting or what is not happening.
__________________
There are 10 kinds of people in the world. Those that understand binary and those that don't.
|
December 19th, 2012, 05:28 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 13
Time spent in forums: 4 h 27 m 9 sec
Reputation Power: 0
|
|
Here is the code for my form:
Code:
<form name="myForm" action="process.php" onsubmit="return validateForm()" method="post">
<div id="accordion">
<h3>Customer Infomation</h3>
<div>
<p>
First Name: <input type="text" name="fName" size="10" />
Last Name: <input type="text" name="lName" size="10" />
</p>
<p>
Phone No: <input type="text" name="phoneNo" size="10" id="phone"/>
</p>
<p>
Email: <input type="text" name="email" size="10" id="email"/>
</p>
<p>
Address: <input type="text" name="address" size="25" id="address"/>
</p>
<p>
City: <input type="text" name="city" size="10" id="city"/>
</p>
<p>
Order Type:<select name="orderType">
<option value="PU">Pick Up</option>
<option value="DI">Dine In</option>
<option value="DV">Delivery</option>
</select>
</p>
</div>
<input type="submit" value="Submit Order" />
</form>
Problem is:
I am getting Null in customerNo in TableOrder Table. (customerNo need to be like- 1, 2, 3... matching the customerNo in Customer Table which is auto increment.)
I don't know where its fits in term of php or mySQL. I would consider 50/50.
|
December 19th, 2012, 05:30 PM
|
|
|
|
MySQL Insert ID
The short answer to "how to insert customerNo in TableOrder which is foreign key of Customer Table?" is "Same way as any other data."
E.g. something like, insert into TableOrder () values( NULL, now(), 1, '$_POST[orderType]' );
I think the question you are really trying to ask is "I have a php file which gets data for two dependent tables. After I insert into the first, I need the key which is generated so I can use that as data for an insert into the second. But how do I get that key?"
The answer is some sort of variation on mysqli_insert_id (). (Maybe you are using the PDO interface, or another interface, but this should get you looking at the right part of the manual.)
|
December 19th, 2012, 05:36 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 13
Time spent in forums: 4 h 27 m 9 sec
Reputation Power: 0
|
|
|
Exactly, that was my problem.
But, I tried like you described, it does not work. I could not find anything like I have in the link you have provided.
|
December 19th, 2012, 06:36 PM
|
|
|
|
Again post your PHP code. We are not clairvoyant.
|
December 19th, 2012, 07:01 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 13
Time spent in forums: 4 h 27 m 9 sec
Reputation Power: 0
|
|
#######This is php code to handle form:#########
PHP Code:
/*********************************
* //Insert into Customer Table:
****************************/
$query_str="INSERT INTO Customer (customerNo, lName, fName, phoneNo, email, address, city) VALUES (1,'$_POST[lName]','$_POST[fName]','$_POST[phoneNo]','$_POST[email]','$_POST[address]','$_POST[city]')";
echo "<p><b>This will show all the customers in associative array:</b></p>";
$query_str= "SELECT lName, fName, phoneNo, email, address, city FROM Customer";
if ($result = $conn->query($query_str)) {
//fetch associative array
while ($row = $result->fetch_assoc()) {
printf ("%s %s %s %s %s %s \n", $row["lName"], $row["fName"], $row["phoneNo"], $row["email"], $row["address"], $row["city"]);
printf ("<br/>");
}
}
echo "<br/>";
echo $query_str . "<br/>";
if ( $conn->query($query_str)) {
echo "Your Information was sent to Customer Table. <br/>";
/*********************************
* //Insert into OrderTable Table:
****************************/
$query_str="INSERT INTO ATableOrder (orderNo, time, customerNo, orderType) VALUES (NULL, now(), 1, '$_POST[orderType]')";
echo "<br/>";
echo $query_str . "<br/>";
if ( $conn->query($query_str)) {
echo "Your order was recorded in TableOrder Table.<br/>";
}
else {
echo "Table creation failed: (" . $conn->errno . ") " . $conn->error;
}
/*********************************
* //Insert in to OrderLineItem Table;
****************************/
$query_str="INSERT INTO OrderLineItem (pizzaNo, subNo, sandNo) VALUES ('$_POST[pizzaNo]','$_POST[subNo]','$_POST[sandNo]')";
echo "<br/>";
echo $query_str . "<br/>";
if ( $conn->query($query_str)) {
echo "Your order was sent to OrderLineItem Table. <br/>";
}
else {
echo "Table creation failed: (" . $conn->errno . ") " . $conn->error;
}
//$result = $conn->query($query_str);
//echo $result->
}
else {
echo "Table creation failed: (" . $conn->errno . ") " . $conn->error;
}
#######AND this the query for my database:########
CREATE TABLE Customer
(customerNo INT (5) NOT NULL AUTO_INCREMENT,
fName VARCHAR (10) NOT NULL,
lName VARCHAR (10) NOT NULL,
phoneNo CHAR (10) NOT NULL,
email CHAR (30),
address VARCHAR (20) NOT NULL,
city VARCHAR (10) NOT NULL,
PRIMARY KEY (customerNo));
CREATE TABLE TableOrder
(orderNo INT (5) NOT NULL,
date DATE NOT NULL,
time TIME NOT NULL,
customerNo INT (5),
orderType CHAR (2) NOT NULL
CHECK (orderType IN ('PU', 'DI', 'DV')),
PRIMARY KEY (orderNo),
FOREIGN KEY (customerNo) REFERENCES Customer(customerNo) ON DELETE SET NULL ON UPDATE CASCADE);
|
December 19th, 2012, 09:57 PM
|
|
|
It isn't clear to me if you are using mysqli or PDO or something else. I see you using ->errno which I think suggests you are using mysqli which means the link I provided ( http://php.net/manual/en/mysqli.insert-id.php ) is exactly what you need. It even has a fairly detailed example.
Basically, after your
you need to add something like
PHP Code:
$myCustID = $conn->insert_id
Then you can pass $myCustID to the insert for Orders.
If it "doesn't work," start debugging. It is the PHP functionality you are looking for.
|
December 20th, 2012, 04:37 AM
|
|
|
|
Also, again, enclose your code in [ PHP ] tags (edit the original post rather than re-posting it all). As I suggested earlier, read the sticky at the top of this forum.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
