PHP/MariaDB On Duplicate Key Update with nested JSON array

So this one is fun. I’m integrating a 3rd party POS into my custom inventory management software I made for this client some time ago. My system is a basic PHP/HTML/JS background. I have webhooks from the 3rd party system sending my site JSON information about product info and stock details. The issue I have is that when we update a product in the 3rd party system, I need to set up my prepared INSERT Statement to UPDATE ON DUPLICATE KEY. The KEY that needs to be checked is the 3rd column in the database, and is the only Unique Key in the table. The Primary Key is another ID, not provided by the 3rd party system. I can’t seem to “build” this ON DUPLICATE KEY UPDATE function correctly for the life of me.

Here’s some code to make sense of things: Product Information comes through in hook.php which has the following:

HOOK.php
<?php 
header('Content-Type: application/json'); 
$request3 = file_get_contents('php://input'); 
req_dump1 = print_r( $request3, true ); 
fp = file_put_contents( 'req.json', $req_dump1 ); 
?>

This gives me the following JSON:

{
  "Id": 8919892,
  "Name": "Final",
  "Description": "Final",
  "CostPrice": 0.95000,
  "SalePrice": 756.00000,
  "CategoryId": 278333,
  "Barcode": "2020202020",
   "BrandId": 151102,
  "SupplierId": 44815,
  "OrderCode": "LKJ-8376594",
   "ProductType": 0,
  "TareWeight": null,
  "ArticleCode": "44-jhaqerkhu",
   "Supplier": {
    "Id": 44815,
    "Name": "House",
    "Description": "House",
   },
  }

Then the rest of the stock information comes from Stock Detail (why they[3rd party] have it separated IDK, I didn’t build it) which hits hook4.php:

<?php

header('Content-Type: application/json');
$request = file_get_contents('php://input');

$request1=str_replace('[', '', $request);

$request2=str_replace(']', '', $request1);

$req_dump = print_r( $request2, true );

$fp = file_put_contents( 'req4.json', $req_dump );


include('ai.php');
?>

This gives me the following JSON

REQ4.json
{
  "Id": 2553369,
  "ProductId": 8919892,
  "MinStock": 300,
    "ProductStockBatches": {
    "Id": 3656516,
    "StockId": 2553369,
    "CreatedDate": "2021-08-08T03:41:24.153",
    "CurrentStock": 225,
    "CurrentVolume": 0,
    "CostPrice": 0.95000,
      }
}

So all of that is fine and dandy, the issue is in ai.php:

<?php
$servername = "localhost";
$username = "terblah";
$password = "blah";
$dbname = "blah";
try
{
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("INSERT INTO blah (name, prod_id, cost, price, brand, vendor_sku, upc, code, on_hand, par,  upd_date) VALUES (:name, :prod_id, :cost, :price, :brand, :vendor_sku, :upc, :code, :currentstock, :parstock,  :upd_date) ON DUPLICATE KEY UPDATE prod_id = :prod_id");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':prod_id', $prod_id);
    $stmt->bindParam(':cost', $cost);
    $stmt->bindParam(':price', $price);
    $stmt->bindParam(':brand', $brand);
    $stmt->bindParam(':vendor_sku', $ven_sku);
    $stmt->bindParam(':upc', $upc);
    $stmt->bindParam(':code', $code);
    $stmt->bindParam(':currentstock', $on_hand);
    $stmt->bindParam(':parstock', $par);
    $stmt->bindParam(':upd_date', $upd);
    $js = file_get_contents('req.json');
    $js0 = file_get_contents('req4.json');
    $dt = json_decode($js, true);
    $dt0 = json_decode($js0, true);
    $name = $dt['Name'];
    $prod_id = $dt0['ProductId'];
    $cost = $dt['CostPrice'];
    $price = $dt['SalePrice'];
    $brand = $dt['Supplier']['Name'];
    $ven_sku = $dt['OrderCode'];
    $code = $dt['ArticleCode'];
    $upc = $dt['Barcode'];
    $on_hand = $dt0['ProductStockBatches']['CurrentStock'];
    $par = $dt0['MinStock'];
    $upd = $dt0['ProductStockBatches']['CreatedDate'];
       
    $stmt->execute();
}

catch(PDOException $e)
{
    echo "Error: " . $e->getMessage();

}
$conn = null;
?>

The issue is the prod_id = :prod_id.
I can’t define it as $prod_id, i get the following that way:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

Leaving it as is tells me:

Error: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'prod_id' cannot be null

Which I understand is because I’m asking it to search for a variable that isn’t defined yet. When i move the definitions I get binding errors because the statement to be prepared isn’t called yet, but I need the defined key first….so I’m wondering if I’m just missing a trick somewhere.

I haven’t tried it yet, but I had the thought of calling req4.json 2x in the ai.php, first simply just to define the variable to be searched in the duplicate key situation. But how do I call a ‘$’ variable in the middle of a SQL statement? I know how to call a defined variable, but how do I pull that definition from JSON and then use it as a variable in the SQL statement?

Can I

$ts = file_get_contents('req4.json');
$js1 = json_decode($ts, true);
$var = $js1['ProductId'];

then in my ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE (prod_id = ?)

and at the end

$stmt->execute($var);

I tried that and I got

Error: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Which leaves me here. Am I even asking this question correctly? Can somebody who has a brain that isn’t currently not working please give me an hand?

Answer

You need to assign to all the other columns in ON DUPLICATE KEY UPDATE. The prod_id column is the one that the duplication is checked from.

You can use VALUES(colname) to get the value that would have been inserted.

$stmt = $conn->prepare("
    INSERT INTO blah (name, prod_id, cost, price, brand, vendor_sku, upc, code, on_hand, par,  upd_date) 
        VALUES (:name, :prod_id, :cost, :price, :brand, :vendor_sku, :upc, :code, :currentstock, :parstock,  :upd_date) 
    ON DUPLICATE KEY UPDATE 
        name = VALUES(name), cost = VALUES(cost), price = VALUES(price), brand = VALUES(brand), vendor = VALUES(vendor)_sku = VALUES(sku), upc = VALUES(upc), code = VALUES(code), on = VALUES(on)_hand = VALUES(hand), par = VALUES(par),  upd = VALUES(upd)_date = VALUES(date)");

Advertisement

Answer