Cfrtjryk

I'm working with a REST API, first time doing this and I have a working code but it's long and just looks messy to me. I just know there's a better and faster way to do it.

try {

$LOCATIONS = new PicqerFinancialsExactItemWarehouse($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! <BR>";

    } else{

      echo ("Try again! <BR>" . mysqli_error($conn));

    }

  } else {

    echo ("Already in database! <BR>");

  }

}

} catch (Exception $e) {

echo get_class($e) . ' : ' . $e->getMessage();

}

This is what the code looks like, but this is a short version. There are also instances where I need to get something like 30 variables out of the JSON file, and upload those to a database.

Another example from the same code:

try {

$CRM = new PicqerFinancialsExactAccount($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! <BR>";

   } else{

     echo ("Try Again! <BR>" . mysqli_error($conn));

   }

 } else {

   echo ("Already in database! <BR>");

 }

 }

  } catch (Exception $e) {

  echo get_class($e) . ' : ' . $e->getMessage();

}

PDO has a great feature for you, it can accept an array with parameters for execute(). It means you won't have to extract separate variables anymore.

So just use PDO instead of mysqli and your code will become just two lines

$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);

}

Notice that I am using quite a few tricks here:

• an object is converted to an array for PDO


• prepare is called only once which makes your queries executed faster (you can read on that from my article I linked above)


• assuming ID is a primary key, there is no need to run a select query. Just add a keyword IGNORE to the INSERT statement


• your error handling code is overkill. if you leave an exception alone, it will tell you exactly the same information you are echoing manually. So get rid of try/catch as well


• of course such output as Worked! or Try again after each query execution is not informative ans should be removed as well