我正在开发一个集成shopify的PHP应用程序。基本上,产品信息必须在"商店"和应用程序之间同步。在产品内部我们有变体(类似于子产品)。Shopify过去常常发送带有json数据的webhook来报告这些变化。每次我更改/添加/删除一个变体时,shopify都会发送一个"产品更新"webhook,它只会更改json内容。这是一个例子:
{
...
"variants": [{
"id": 279656846,
"position": 1,
"price": "80.00",
"product_id": 123022448,
"sku": "1000",
"option1": "30 cm",
"inventory_quantity": 10
},
{
"id": 291321287,
"position": 2,
"price": "15.00",
"product_id": 123022448,
"sku": "1003",
"option1": "15 cm",
"inventory_quantity": 23
}],
...
}
如果我创建一个新的变体,它会向我发送一个包含当前状态的"产品更新",并在json中包含新的变体。同样,如果我删除,它只发送给我一个包含当前状态的"产品更新",但没有json中删除的变体。
我创建了以下代码,可以正确地处理更改/添加情况:foreach ($jsonArr['variants'] as $rows) {
$variant = $rows['option1'];
$sku = $rows['sku'];
$salesPrice = $rows['price'];
$stockQty = $rows['inventory_quantity'];
$idVar = $rows['id'];
$dupchk = mysql_query("SELECT * FROM `variants` WHERE `idVar`='$idVar'",$con) or die (mysql_error());
$num_rows = mysql_num_rows($dupchk);
if ($num_rows > 0) {
$sql = "UPDATE `variants` SET `variant`='$variant',`sku`='$sku',`salesPrice`='$salesPrice',`stockQty`='$stockQty' WHERE `idVar`='$idVar'";
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
}
else {
$sql = "INSERT INTO `variants`(`idVariant`, `idProduct`, `variant`, `sku`, `salesPrice`, `stockQty`, `comments`, `idVar`) VALUES ('','$idProduct','$variant','$sku','$salesPrice','$stockQty','','$idVar')";
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
}
}
问题是这段代码不处理delete变量的情况。我试着这样做,但直到现在只创建一个"大混乱"的代码,不能工作。如果你有什么明智的处理方法,请告诉我。
我使用了以下代码:
//Variable to count variant update or create
$var_count = 0;
foreach ($jsonArr['variants'] as $rows) {
$variant = $rows['option1'];
$sku = $rows['sku'];
$salesPrice = $rows['price'];
$stockQty = $rows['inventory_quantity'];
$idVar = $rows['id'];
$dupchk = mysql_query("SELECT * FROM `variants` WHERE `idVar`='$idVar'",$con) or die (mysql_error());
$num_rows = mysql_num_rows($dupchk);
if ($num_rows > 0) {
$sql = "UPDATE `variants` SET `variant`='$variant',`sku`='$sku',`salesPrice`='$salesPrice',`stockQty`='$stockQty' WHERE `idVar`='$idVar'";
$var_count++;
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
}
else {
$sql = "INSERT INTO `variants`(`idVariant`, `idProduct`, `variant`, `sku`, `salesPrice`, `stockQty`, `comments`, `idVar`) VALUES ('','$idProduct','$variant','$sku','$salesPrice','$stockQty','','$idVar')";
$var_count++;
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
}
}
//Start checking to erase variant if needed
$result = mysql_query("SELECT `idVar` FROM products NATURAL JOIN variants WHERE `idShopify`='$idShopify'",$con);
$num_rows = mysql_num_rows($result);
if ($num_rows>$var_count) {
while($row = mysql_fetch_array($result))
{
$clear = 0;
foreach ($jsonArr['variants'] as $rows) {
if ($rows['id']==$row['idVar']) {
$clear++;
}
}
if ($clear==0) {
$idVar = $row['idVar'];
$sql = "DELETE FROM `variants` WHERE `idVar`='$idVar'";
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}
}
}
}
这是不优雅的,但工作。请随意提出代码改进建议。