Double Passing Rows into MySQL Databases using PHP

Question

eBay Platform Notifications recommends periodic polling of the GetOrders API to ensure each and every order is received.

In my case, I have Platform Notifications set-up to parse the XML file received and insert it into a MySQL database using PHP.

Now I am looking to, as recommended, "double pass" using GetOrders, which should essentially give me duplicates for each and every single row (or order).

My structure is rather straightforward. But I have a UNIQUE INDEX for OrderLineItemID which, to my understanding, is the unique identifier for each eBay Order.

Is there a better way to do this than I am currently doing?

//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();
}

Because of my UNIQUE ON OrderLineItemID, when a duplicate order comes in, the query will result in an error, close the connection, and then exit the script.

I've thought about first checking to see (maybe using a SELECT statement) if the row exists, and then trying an insert, but I'm doing a foreach loop of up to 100 orders using the GetOrders API to run my SQL queries, and it seems like just allowing it to fall to error might be a quicker option, but I'm weary on if this can cause issues down the line.

In all, I'm not familiar with best practices for MySQL "double passes". Anyone have any insight on the best way to conduct this?

edit: here is my entire foreach loop:

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();
}


}

Answer 1

To avoid an error when an INSERT fails due to a unique key constraint, we can use the IGNORE option on the INSERT statement.

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.

But this also affects error conditions other than duplicate key exceptions.

As another option, we can use INSERT ... ON DUPLICATE KEY ...

Documentation available here:

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