使用PHP从MySql检索最后一个插入ID [英] Retrieving the last insert id from MySql using PHP

问题描述

我在MySql数据库中有两个表，一个是sales_order，另一个是sales_order_details，其中包含sales_order表中顺序的详细信息.显然，从sales_order到sales_order_details存在一对多的关系.

I have two tables in MySql database, one is sales_order and the other is sales_order_details which contains the details of the order in the sales_order table. Obviously, there is a one to many relationship from sales_order to sales_order_details.

当客户下订单时，第一个条目进入sales_order表，并基于sales_order表的auto_increment id，相应的条目进入sales_order_details表.

When a customer places an order, the first entry is made into the sales_order table and based on the auto_increment id of the sales_order table, the corresponding entry is made into the sales_order_details table.

我正在使用last_insert_id() MySql函数从sales_order表中检索相应的order_id，类似这样.

I'm using the last_insert_id() MySql function to retrieve the corresponding order_id from the sales_order table, Something like this.

insert into sales_order_details(order_id, prod_id, prod_price)values(last_insert_id(), 5, 1500);

正在工作. last_insert_id()函数从相应表中检索最后插入的ID，这对特定连接是唯一的(据我所知).

It's working. The last_insert_id() function retrieves the last inserted id from the respective table which is unique to a particular connection (as far as I know).

现在，我需要由last_insert_id()函数检索并插入的最新order_id，以将其作为发票号发送到支付系统.

Now, I need the same order_id which is retrieved and inserted most recently by the last_insert_id() function to send it as an invoice number to a payment system.

我可以尝试使用PHP函数mysql_insert_id()，但是我不确定它是否按指定方式工作.始终保证检索始终与特定订单相关联的特定ID的正确方法是，检索最后一个插入ID的正确方法是什么?

I can try using the PHP function mysql_insert_id() but I'm not sure whether it works as specified. What is the correct way to retrieve the last insert id which always guarantees to retrieve the specific id which is always associated with a particular order?

推荐答案

您可以通过 2种方式解决此问题:

There are 2 ways which you could solve this:

在sales_order中插入条目之后:

After inserting the entry in the sales_order:

1. 获取last_insert_id并将其存储在php变量中，然后将此值注入相关的查询中.

1. Get the last_insert_id and store it in a php variable, then inject this value into the relevant queries.

将last_insert_id存储在MySql 用户中定义的变量，并在查询中使用该变量而不是last_insert_id.

Store the last_insert_id in a MySql user-defined variable and use the variable in the query instead of last_insert_id.

选项2的示例代码

SET @last_order_id = last_insert_id();

insert into sales_order_details (order_id, prod_id, prod_price)
values (@last_order_id, 5, 1500);`

insert into sales_invoice (order_id, invoice_id)
values (@last_order_id, 1);`

这篇关于使用PHP从MySql检索最后一个插入ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！