同时向父表和子表插入数据 [英] insert data to parent and child table at the same time

问题描述

我这里有三张桌子

 customer table
-----------------------------
|customer_id | customer_Name |
-----------------------------
product table
-----------------------------
| product_id | event_Name    |
-----------------------------
reservation table
----------------------------------------------------------------------
|reserve_id | customer_id | product_id | date_usage | motif | venue |
----------------------------------------------------------------------

问题是我想将数据与父表同时插入保留表.我将如何使用 PHP 做到这一点?我在网上搜索过关于使用存储过程的信息，但我不知道如何使用过程.我将如何使用 php 对此进行编码?提前致谢.

The problem is I want to insert data to reservation table at the same time with the parent tables. how will I do this using PHP ? I’ve searched the web and I’ve red about using stored procedures but I don't know how to use procedures. how will I code this using php? thanks in advance.

PHP 代码:

$price2 = $_POST['price1'];
$pack2 = $_POST['pack1'];
$name = $_POST['textinput11'];
$motif = $_POST['textinput22'];
$venue = $_POST['textinput44'];
$event2 = $_POST['events1'];
$currentdate = date('Y/m/d');


$reserve = mysql_query("INSERT INTO reservation_info(Event_Motif,Event_Venue,Date_Subtmitted,Date_of_Usage) VALUES('$motif','$venue','$currentdate','$date2')");
$customer = mysql_query("INSERT INTO customer(CustomerName) VALUES('$name')");
$product  = mysql_query("INSERT INTO product_t(Event_Type,Price) VALUES('$event2','$price2')");


if($customer & $product & reserve){
    echo "You have successfully reserved";
}

推荐答案

问题在于插入语句的顺序.首先，您尝试向保留表(即子表)中插入一行，但您还没有 cudtomer_id.

The problem is with the order of the insert statements. First, you try to insert a row into the reservations table, which is the child table, but you do not have a cudtomer_id yet.

先将交易放在一边，先插入到客户表中以创建您的客户.根据您的代码，我相信 customer_id 是一个自动增量字段.因此，在插入客户表后调用 mysql_insert_id() 函数检索新创建的客户 ID.

Setting transactions aside, insert first into the customers table to create your customer. Based on your code I belive customer_id is an auto increment field. So, after inserting into the customer table call mysql_insert_id() function to retrieve the newly created customer id.

然后对产品表重复相同的操作.

Then repeat the same with the products table.

最后，执行对预订表的插入，但这次使用您检索到的 2 个 ID 来明确地在查询中提供客户和产品 ID.

Finally, execute the insert into the reservations table, but this time use the 2 ids you retrieved to explicitky provide customer and product ids in the query.

注意1:不要使用mysql扩展，它已被弃用.改用 mysqli 或 PDO.

Note1: do not use mysql extension, it is deprecated. Use mysqli or PDO instead.

注2:可以将上面的inserts封装成一个事务.在生产代码中，我会鼓励你这样做，如果只是为了学习，那么现在不需要打扰.

Note2: you can encapsulate the above inserts into a transaction. In a production code I would encourage you to do so, if it's only for learning, then no need to bother just yet.

这篇关于同时向父表和子表插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！