从REST (JSON)获取数据，提取变量并使用MySQL将这些变量上传到数据库

Question

我正在使用REST，第一次这样做，我有一个工作代码，但它很长，只是看上去很混乱。我只知道有更好更快的方法。

try {
$LOCATIONS = new \Picqer\Financials\Exact\ItemWarehouse($connection);
$LOCATIONS_GET = $LOCATIONS->get();
foreach($LOCATIONS_GET as $LOCATIONS){
   $locationID = $LOCATIONS->ID;
   $locationDefaultStorageLocationCode = $LOCATIONS->DefaultStorageLocationCode;
   $locationDefaultStorageLocatoinDescription = $LOCATIONS->DefaultStorageLocationDescription;
   $locationWarehouseCode = $LOCATIONS->WarehouseCode;
   $locationDefaultStorageLocation = $LOCATIONS->DefaultStorageLocation;
   $locationLocatieType = 0; //Locatie type

  $LOCATIONS_CHECK = $conn->query("SELECT ID FROM data_exact_locations WHERE ID='$locationID' LIMIT 1");
  if($LOCATIONS_CHECK->num_rows == 0){
    $LOCATIONS_SQL = "INSERT INTO data_exact_locations (ID, Code, Omschrijving, Magazijn, Standaardlocatie, Locatie_type)
    VALUES ('$locationID','$locationDefaultStorageLocationCode','$locationDefaultStorageLocatoinDescription', '$locationWarehouseCode', '$locationDefaultStorageLocation')";
    if (mysqli_query($conn, $LOCATIONS_SQL)){
      echo "Worked! 
";
    } else{
      echo ("Try again! 
" . mysqli_error($conn));
    }
  } else {
    echo ("Already in database! 
");
  }
}
} catch (\Exception $e) {
echo get_class($e) . ' : ' . $e->getMessage();
}

这是代码的样子，但这是一个简短的版本。在某些情况下，我需要从JSON文件中获取大约30个变量，并将这些变量上传到数据库中。

来自同一代码的另一个示例：

try {
$CRM = new \Picqer\Financials\Exact\Account($connection);
$CRM_GET = $CRM->filter("IsSupplier eq true");
foreach($CRM_GET as $CRM){
     $crmID = $CRM->ID;
     $crmCode = $CRM->Code;
     $crmSearchCode =$CRM->SearchCode;
     $crmName = $CRM->Name;
     $crmAddressLine1 = $CRM->AddressLine1;
     $crmAddressline2 = $CRM->AddressLine2;
     $crmAddressline3 = $CRM->AddressLine3;
     $crmVatNumber = $CRM->VATNumber;
     $crmCountry = $CRM->Country;
     $crmCity = $CRM->City;
     $crmPostcode = $CRM->Postcode;
     $crmState = $CRM->State;
     $crmRemarks = $CRM->Remarks;

 $CRM_CHECK = $conn->query("SELECT ID FROM data_exact_crm WHERE ID='$crmID' LIMIT 1");
 if($CRM_CHECK->num_rows == 0){
   $CRM_SQL = "INSERT INTO data_exact_crm (ID, Code, SearchCode, Name, AddressLine1, AddressLine2, AddressLine3, VATNumber, CountryDescription, City, PostCode, StateDescription, Remarks)
   VALUES ('$crmID','$crmCode','$crmSearchCode','$crmName','$crmAddressLine1','$crmAddressline2','$crmAddressline3','$crmVatNumber','$crmCountry','$crmCity','$crmPostcode','$crmState','$crmRemarks')";
   if (mysqli_query($conn, $CRM_SQL)){
     echo "Worked! 
";
   } else{
     echo ("Try Again! 
" . mysqli_error($conn));
   }
 } else {
   echo ("Already in database! 
");
 }
 }
  } catch (\Exception $e) {
  echo get_class($e) . ' : ' . $e->getMessage();
}

Answer 1

它可以接受带有参数的数组。execute()为您提供了一个很好的特性。这意味着您不再需要提取单独的变量了。

所以，只需使用PDO而不是mysqli，您的代码就会变成两行。

$CRM_GET = $CRM->filter("IsSupplier eq true");
$stmt = "INSERT IGNORE INTO data_exact_crm (ID, Code, SearchCode, Name, AddressLine1, AddressLine2, AddressLine3, VATNumber, CountryDescription, City, PostCode, StateDescription, Remarks)
   VALUES (:ID,:Code, :SearchCode,:Name,:AddressLine1,:Addressline2,:Addressline3,:VatNumber,:Country,:City,:Postcode,:State,:Remarks)";
foreach($CRM_GET as $CRM) {
    $stmt->execute((array)$CRM);
}

请注意，我在这里使用了很多技巧：

• 对象被转换为PDO的数组。
• 准备只被调用一次，这使得查询执行得更快(您可以从我上面链接的文章中读到)
• 假设ID是主键，则不需要运行select查询。只需将关键字忽略添加到INSERT语句
• 您的错误处理代码太过分了。如果不处理异常，it将告诉您手动回显的完全相同的信息&。因此，也要摆脱尝试/捕捉
• 当然，这样的输出是有效的！或者在每次查询执行不提供信息后再试一次，也应该删除ans。