易趣 Platform Notifications建议定期轮询 GetOrders确保收到每个订单的 API。

在我的例子中，我设置了平台通知来解析接收到的 XML 文件并使用 PHP 将其插入到 MySQL 数据库中。

现在我正在寻找，按照建议，使用 GetOrders 进行“双重传递”，这基本上应该为每一行(或订单)提供重复项。

我的结构相当简单。但是我有一个 OrderLineItemID 的 UNIQUE INDEX，据我所知，它是每个 eBay 订单的唯一标识符。

有没有比我目前正在做的更好的方法来做到这一点？

//retrieve and escape variables for insertion//

$sql = "INSERT INTO eBayOrders (OrderLineItemID, SalesRecordNumber, BuyerUserID, BuyerEmail, Title, SKU, Quantity, TransactionPrice)
VALUES ('".$orderlineitemid."', '".$recordnumber."', '".$buyeruserid."', '".$buyeremail."', '".$title."', '".$sku."', '".$qty."', '".$transactionprice."')";
}

if ($connect->query($sql) === TRUE) {
         echo "New Record Created Successfully";
} else {
         echo "Error: " . $sql . "<br />" . $connect->error;
      $connect->close();
      die();
}

由于我的UNIQUE ON OrderLineItemID，当有重复订单进来时，查询会报错，关闭连接，然后退出脚本。

我考虑过先检查(可能使用 SELECT 语句)该行是否存在，然后尝试插入，但我正在执行最多 100 个订单的 foreach 循环使用 GetOrders API 来运行我的 SQL 查询，似乎允许它出错可能是一个更快的选择，但我厌倦了这是否会导致问题。

总而言之，我不熟悉 MySQL“双重传递”的最佳实践。有人对执行此操作的最佳方法有任何见解吗？

编辑:这是我的整个 foreach 循环:

foreach ($orders as $order) {
                $i++;
                        $buyeruserid2 = $order->BuyerUserID;
                            $buyeruserid = mysqli_real_escape_string($connect, $buyeruserid2);
                     // $extendedorderid = $order->TransactionArray->Transaction->ExtendedOrderID;
                        $buyeremail2 = $order->TransactionArray->Transaction->Buyer->Email;
                            $buyeremail = mysqli_real_escape_string($connect, $buyeremail2);
                        $salesrecordnumber2 = $order->TransactionArray->Transaction->ShippingDetails->SellingManagerSalesRecordNumber;
                            $salesrecordnumber = mysqli_real_escape_string($connect, $salesrecordnumber2);
                        $orderlineitemid2 = $order->TransactionArray->Transaction->OrderLineItemID;
                            $orderlineitemid = mysqli_real_escape_string($connect, $orderlineitemid2);
                        $title2 = $order->TransactionArray->Transaction->Item->Title;
                            $title = mysqli_real_escape_string($connect, $title2);
                        $sku2 = $order->TransactionArray->Transaction->Item->SKU;
                            $sku = mysqli_real_escape_string($connect, $sku2);
                        $quantitypurchased2 = $order->TransactionArray->Transaction->QuantityPurchased;
                            $quantitypurchased = mysqli_real_escape_string($connect, $quantitypurchased2);
                        $transactionprice2 = $order->TransactionArray->Transaction->TransactionPrice;
                            $transactionprice = mysqli_real_escape_string($connect, $transactionprice2);

            echo $i;
            echo "\n";
            echo "BuyerUserID: " . $buyeruserid . "\n";
            echo "extendedorderid: " . $quantitypurchased . "\n";
            echo "BuyerEmail: " . $buyeremail . "\n";
            echo "SellingManagerSalesRecordNumber: " . $salesrecordnumber . "\n";
            echo "OrderLineItemID: " . $orderlineitemid . "\n";
        // echo "ExtendedOrderID: " . $transaction->ExtendedOrderID . "\n";
            echo "Title: " . $title . "\n";
            echo "SKU: " . $sku . "\n";
            echo "QuantityPurchased: " . $quantitypurchased . "\n";
            echo "TransactionPrice: " . $transactionprice . "\n";
            echo "\n";


$sql = "INSERT INTO eBayOrders (OrderLineItemID, SalesRecordNumber, BuyerUserID, BuyerEmail, Title, SKU, Quantity, TransactionPrice)
VALUES ('".$orderlineitemid."', '".$recordnumber."', '".$buyeruserid."', '".$buyeremail."', '".$title."', '".$sku."', '".$qty."', '".$transactionprice."')";

if ($connect->query($sql) === TRUE) {
  echo "New Record Created Successfully";
} else {
  echo "Error: " . $sql . "<br />" . $connect->error;
  $connect->close();
  die();
}


}

最佳答案

为了避免因唯一键约束导致 INSERT 失败时出现错误，我们可以在 INSERT 语句上使用 IGNORE 选项。

INSERT IGNORE INTO eBayOrders ...

If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.

但这也会影响除重复键异常以外的错误情况。

作为另一种选择，我们可以使用 INSERT ... ON DUPLICATE KEY ...

此处提供文档:

引用:https://dev.mysql.com/doc/refman/5.7/en/insert.html

关于php - 使用 PHP 将行双重传递到 MySQL 数据库中，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42837547/