如何在PHP中使用预准备语句在多个表中插入数据?
我有两个表是billing_info, shipping_info
。 billing_id是主键,billing_id是shipping_info中的外键。如何在PHP中使用预准备语句在多个表中插入数据?
我有billing_info地址表单。用户将填写结算信息,填写信息后会出现一个复选框,称为shipping address is different
。如果任何用户选中该复选框,则它会询问运输信息。根据数据填写运输信息后,结算信息将存储在billing_info表中,shipping_info将存储在运输信息表中。
如果用户未选中复选框而不是只有帐单信息将存储在帐单信息表中。
<input type="checkbox" name="shipping_status" value="1">
我能够插入到数据库中的数据只针对billing_info如果用户不选中复选框,但不能插入数据检查后的复选框。 查询可能有问题。我试过的查询是
1) START TRANSACTION;
INSERT INTO table1 (column name)VALUES (values);
INSERT INTO table2 (column name) VALUES (values);
COMMIT;
2) BEGIN;
INSERT INTO table1 (column name)VALUES (values);
INSERT INTO table2 (column name) VALUES (values);
COMMIT;
但仍无法插入数据。 你能帮忙吗?
这是存储int用户信息的正确方法吗?
if (isset($_POST['submit'])){
$b_firstname=$conn->real_escape_string(trim($_POST['b_firstname']));
$b_lastname=$conn->real_escape_string(trim($_POST['b_lastname']));
$b_address =$conn->real_escape_string(trim($_POST['b_address']));
$b_country=$conn->real_escape_string(trim($_POST['b_country']));
$b_state=$conn->real_escape_string(trim($_POST['b_state']));
$b_city=$conn->real_escape_string(trim($_POST['b_city']));
$b_pincode=$conn->real_escape_string(trim($_POST['b_pincode']));
$b_mobileno=$conn->real_escape_string(trim($_POST['b_mobileno']));
//$login_user_id=$conn->real_escape_string(trim($_POST['login_user_id']));
//echo $shipping_status=$conn->real_escape_string(trim($_POST['shipping_status']));
/*Shipping address*/
$s_firstname=$conn->real_escape_string(trim($_POST['s_firstname']));
$s_lastname=$conn->real_escape_string(trim($_POST['s_lastname']));
$s_address =$conn->real_escape_string(trim($_POST['s_address']));
$s_country=$conn->real_escape_string(trim($_POST['s_country']));
$s_state=$conn->real_escape_string(trim($_POST['s_state']));
$s_city=$conn->real_escape_string(trim($_POST['s_city']));
$s_pincode=$conn->real_escape_string(trim($_POST['s_pincode']));
$s_mobileno=$conn->real_escape_string(trim($_POST['s_mobileno']));
if (empty($conn->real_escape_string(trim($_POST['shipping_status'])))) {
$shipping_status=0;
}
else{
$shipping_status=1;
}
if ($shipping_status==0) {
echo $sql="INSERT INTO billing_info (b_firstname, b_lastname, b_address, b_country, b_state, b_city, b_pincode, b_mobileno,login_user_id,shipping_status, b_date_of_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
// print_r($sql);
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssiiiis", $b_firstname, $b_lastname, $b_address, $b_country, $b_state, $b_city, $b_pincode, $b_mobileno,$login_user_id=1,$shipping_status, $date_of_added);
}
else{
// prepare and bind
echo $sql="INSERT INTO billing_info (b_firstname, b_lastname, b_address, b_country, b_state, b_city, b_pincode, b_mobileno,login_user_id,shipping_status, b_date_of_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?);INSERT INTO shipping_info (s_firstname, s_lastname, s_address, s_country, s_state, s_city, s_pincode, s_mobileno,b_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssiiiisssssssiii", $b_firstname, $b_lastname, $b_address, $b_country, $b_state, $b_city, $b_pincode, $b_mobileno,$login_user_id=1,$shipping_status, $date_of_added,$s_firstname, $s_lastname, $s_address, $s_country, $s_state, $s_city, $s_pincode, $s_mobileno,LAST_INSERT_ID());
}
$stmt->execute();
$stmt->close();
}
//redirect code
//header('Location: contactus.php');
$conn->close();
}
bind_params
后,必须要插入计费信息,以便进行计费信息,如果语句不应该在插入查询每次插入此
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();
if (empty($conn->real_escape_string(trim($_POST['shipping_status'])))) {
$shipping_status=0;
$sql="INSERT INTO billing_info (b_firstname, b_lastname, b_address, b_country, b_state, b_city, b_pincode, b_mobileno,login_user_id,shipping_status, b_date_of_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
// print_r($sql);
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssiiiis", $b_firstname, $b_lastname, $b_address, $b_country, $b_state, $b_city, $b_pincode, $b_mobileno,$login_user_id=1,$shipping_status, $date_of_added);
}
else{
$shipping_status=1;
$sql="INSERT INTO billing_info (b_firstname, b_lastname, b_address, b_country, b_state, b_city, b_pincode, b_mobileno,login_user_id,shipping_status, b_date_of_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?);INSERT INTO shipping_info (s_firstname, s_lastname, s_address, s_country, s_state, s_city, s_pincode, s_mobileno,b_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssiiiisssssssiii", $b_firstname, $b_lastname, $b_address, $b_country, $b_state, $b_city, $b_pincode, $b_mobileno,$login_user_id=1,$shipping_status, $date_of_added,$s_firstname, $s_lastname, $s_address, $s_country, $s_state, $s_city, $s_pincode, $s_mobileno,LAST_INSERT_ID());
}
默认情况下,。
$sql="INSERT INTO billing_info (b_firstname, b_lastname, b_address, b_country, b_state, b_city, b_pincode, b_mobileno,login_user_id,shipping_status, b_date_of_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
// print_r($sql);
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssiiiis", $b_firstname, $b_lastname, $b_address, $b_country, $b_state, $b_city, $b_pincode, $b_mobileno,$login_user_id=1,$shipping_status, $date_of_added);
$stmt->execute();
$stmt->close();
航运状况选择
if ($shipping_status==1) {
$sql="INSERT INTO shipping_info (b_firstname, b_lastname, b_address, b_country, b_state, b_city, b_pincode, b_mobileno,login_user_id,shipping_status, b_date_of_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
// print_r($sql);
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssiiiis", $b_firstname, $b_lastname, $b_address, $b_country, $b_state, $b_city, $b_pincode, $b_mobileno,$login_user_id=1,$shipping_status, $date_of_added);
$stmt->execute();
$stmt->close();
}
你的逻辑解决了我的80%问题。现在我必须添加订单详情,那么我应该在查询中添加它们的位置? –
如果它在另一个表中,则可以在查询之外插入它。 –
你的意思是说我必须使用$ stmt->的execute();在if-else的情况下? –
把你的变量放在if ... else语句之后,然后执行它... –
我已经添加了我的代码,但仍然是同样的问题。只有部分工作,但不能插入其他部分 –