I have a JSON object of around 1000 items, each with 20 keys, retrieved from a public API. From WordPress PHP code (i.e. plugin or code snippet), what is the best way to get that into MySQL?

The code below works. However, it’s fugly because not all the keys are always present. For example, if there were no “notes” for an item, then the “notes” key is omitted from the JSON, rather than just having a blank value as one would expect.

Here is an abbreviated example: If I issue this Sql command “INSERT INTO bookings (adults, children, notes) VALUES $item->adults, $item->children, $item->notes” but one record has no “notes” key, it throws an exception because I tried to access $item->notes which does not exist.

The only workaround I could find was to check for the existence of each key, storing it all to temporary local variables before issuing the Sql INSERT.

Surely there must be a better way – avoiding manually checking each key????

function or_load_bookings() {
  global $wpdb;

  $client = new Client([
    // Base URI is used with relative requests
    'base_uri' => 'https://api.somesite.com/',
    'auth' => ['user', 'pass'],
    'headers' => ['Content-Type' => 'application/json']
  ]);
  
  try {
    $from_date = '2018-01-01';
    $to_date = '2023-01-10';
    $property_ids="133945,265690,338679";

    $response = $client->request('GET', 'bookings', [
      'query' => [ 'limit' => '100', 
      'include_charges' => 'True', 
      'property_ids' => $property_ids,
      'from' => $from_date, 
      'to' => $to_date ]
    ]);

    // format response as JSON
    $json = json_decode($response->getBody());
    
    // drop existing table
    $sqlresult = $wpdb->query( $wpdb->prepare('DROP TABLE IF EXISTS bookings'));

    // recreate table
    $sqlresult = $wpdb->query( $wpdb->prepare('CREATE TABLE bookings (adults int, arrival varchar(255), booked_utc varchar(255), check_in varchar(255), check_out varchar(255), children int, currency_code varchar(255), departure varchar(255), form_key varchar(255), guest_id bigint, id bigint, infants int, is_block varchar(255), listing_site varchar(255), notes varchar(255), pets int, platform_reservation_number varchar(255), property_id bigint, `status` varchar(255), thread_ids varchar(255), total_amount decimal(10,2), total_host_fees decimal(10,2), total_paid decimal(10,2), `type` varchar(255))'));

    // some keys may not exist for all records, so check them and save to temp local variable to avoid errors
    foreach($json->items as $item) {
      if (!isset($item->adults)) { $adults = ''; } else { $adults = $item->adults; };
      if (!isset($item->arrival)) { $arrival = ''; } else { $arrival = $item->arrival; };
      if (!isset($item->booked_utc)) { $booked_utc = ''; } else { $booked_utc = $item->booked_utc; };
      if (!isset($item->check_in)) { $check_in = ''; } else { $check_in = $item->check_in; };
      if (!isset($item->check_out)) { $check_out = ''; } else { $check_out = $item->check_out; };
      if (!isset($item->children)) { $children = ''; } else { $children = $item->children; };
      if (!isset($item->currency_code)) { $currency_code = ''; } else { $currency_code = $item->currency_code; };
      if (!isset($item->departure)) { $departure = ''; } else { $departure = $item->departure; };
      if (!isset($item->form_key)) { $form_key = ''; } else { $form_key = $item->form_key; };
      if (!isset($item->guest_id)) { $guest_id = ''; } else { $guest_id = $item->guest_id; };
      if (!isset($item->id)) { $id = ''; } else { $id = $item->id; };
      if (!isset($item->infants)) { $infants = ''; } else { $infants = $item->infants; };
      if (!isset($item->is_block)) { $is_block = '0'; } else { if ($item->is_block) {$is_block=1;} else {$is_block=0;} ; };
      if (!isset($item->listing_site)) { $listing_site = ''; } else { $listing_site = $item->listing_site; };
      if (!isset($item->notes)) { $notes = ''; } else { $notes = $item->notes; };
      if (!isset($item->pets)) { $pets = ''; } else { $pets = $item->pets; };
      if (!isset($item->platform_reservation_number)) { $platform_reservation_number = ''; } else { $platform_reservation_number = $item->platform_reservation_number; };
      if (!isset($item->property_id)) { $property_id = ''; } else { $property_id = $item->property_id; };
      if (!isset($item->status)) { $status = ''; } else { $status = $item->status; };
      if (!isset($item->thread_ids)) { $thread_ids = ''; } else { $thread_ids = $item->thread_ids; };
      if (!isset($item->total_amount)) { $total_amount = ''; } else { $total_amount = $item->total_amount; };
      if (!isset($item->total_host_fees)) { $total_host_fees = ''; } else { $total_host_fees = $item->total_host_fees; };
      if (!isset($item->total_paid)) { $total_paid = ''; } else { $total_paid = $item->total_paid; };
      if (!isset($item->type)) { $type = ''; } else { $type = $item->type; };

      // insert the data into MySql
      $sqlresult = $wpdb->query( $wpdb->prepare('INSERT INTO bookings (adults, arrival, booked_utc, check_in, check_out, children, currency_code, departure, form_key, guest_id, id, infants, is_block, listing_site, notes, pets, platform_reservation_number, property_id, `status`, thread_ids, total_amount, total_host_fees, total_paid, `type`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',$adults, $arrival, $booked_utc, $check_in, $check_out, $children, $currency_code, $departure, $form_key, $guest_id, $id, $infants, $is_block, $listing_site, $notes, $pets, $platform_reservation_number, $property_id, $status, $thread_ids, $total_amount, $total_host_fees, $total_paid, $type));
    }

    // count how many were inserted
    $insertcount = $wpdb->get_var( 'SELECT COUNT(*) FROM bookings');
    return 'Loaded ' . $insertcount . ' bookings';
    

  } catch (ClientException $e) {
    echo 'Request: '.Psr7Message::toString($e->getRequest()) . '<br>';
    echo 'Response: '.Psr7Message::toString($e->getResponse()) . '<br>';
  }


  }

I looked for an elegant, efficient way to do this. Decided to save each item to temp var as a temporary workaround