API syncing to local database: updating n records at a time?

I’m working on a web app to sync an inventory that’s available via third-party API.

All records (about 10 thousand) have to be brought over into the local database (only one time), and then all records that have changed in the last 24 hours have to be updated daily (about 500).

The API will only return a maximum of 250 records per call.

How do I loop through these records, 250 at a time, to add/update them into the local database?

What format does the API return them in? I did something similar and they came back in JSON format, so it’s just a case of decoding the JSON and looping through each individual object. Your API might return them in XML, in which case I’d expect it’s similar.

It’s a bit of a wide-ranging question, and not really anything to do with PHP specifically.

Thanks for the input. The data is returned in JSON.

I’m really more concerned with what I need to do to process the syncing in chunks of 250 (the maximum number of records I can get with the third-party API at once).

Presumably you have to specify something like a page number when you retrieve each block of 250 records? Or do you request all, and get multiple responses, each containing 250 records, or request all and old get the first 250?

If they’re coming in ordered by something like the SKU, and your API allows it, each request could be for all products where the SKU is greater than the last previous SKU. Pseudo-code:

lastSKU=""
rows = getproducts (where SKU > lastSKU)  // returns first 250 products
while count(rows) > 0
  parse rows into your database
  lastSKU = the last SKU in "rows"
  rows = getproducts(where SKU > lastSKU)  // returns next 250 products
end while

If you have to retrieve by page number, same sort of idea - retrieve page 1, increment the page number and get the next one, until you get fewer than 250 records, that’s probably the last page.

Here’s one way you can loop through the records, 250 at a time, to add/update them into the local database:

First, you need to retrieve the total number of records available in the API. You can do this by making a call to the API and checking the value of the “total” or “count” field in the response.

Next, you can use a loop to retrieve the records in chunks of 250. You can do this by making API calls and incrementing the “offset” or “page” parameter each time. For example, to retrieve the first 250 records, you can set the offset to 0. To retrieve the next 250 records, you can set the offset to 250, and so on.

$offset = 0;
$limit = 250;
while ($offset < $total_records) {
    $response = make_api_call($offset, $limit);
    // Add/update the records in the local database
    $offset += $limit;
}

Inside the loop, you can parse the response and add/update the records in the local database. For adding/updating you can use the ORM( Object-relational mapping) or query builder of your choice.

After the first time data fetch, you can use the same logic to fetch data from the API on a daily basis, but this time you need to filter the data based on the last update date.

You can also add a sleep time between each API call to avoid rate limiting from the API

It’s important to note that depending on the rate of change for the records, it’s may be better to use a different approach, like using webhooks to get notifications when records change. This would allow you to only update the records that have changed, rather than fetching all of the records and comparing them to see which ones have changed.

Another possible solution is to use a pagination technique to retrieve the records in chunks of 250. Here’s an example of how you might implement this:

  1. First, determine the total number of pages required to retrieve all records. You can do this by dividing the total number of records by the number of records per page (250).
  2. Start a loop to iterate through the pages. On each iteration, make an API call to retrieve the records for that page.
  3. For each set of records retrieved, loop through them and check if the record already exists in the local database. If it does, update the record. If it doesn’t, add the record to the local database.
  4. Repeat steps 2 and 3 until all records have been retrieved and added/updated in the local database.

Here’s an example of how the code might look in PHP:

<?php

// Number of records per API call
$records_per_page = 250;

// Total number of records
$total_records = 10000;

// Calculate total number of pages
$total_pages = ceil($total_records / $records_per_page);

// Loop through the pages
for ($i = 1; $i <= $total_pages; $i++) {
    // Make API call to retrieve records for this page
    $records = get_records_from_api($i, $records_per_page);

    // Loop through the records
    foreach ($records as $record) {
        // Check if the record already exists in the local database
        $existing_record = get_record_from_database($record['id']);
        if ($existing_record) {
            // Update the record in the local database
            update_record_in_database($record);
        } else {
            // Add the record to the local database
            add_record_to_database($record);
        }
    }
}

// Function to make an API call to retrieve records
function get_records_from_api($page, $per_page) {
    // Code to make API call and return records
}

// Function to get a record from the local database
function get_record_from_database($id) {
    // Code to query the local database and return the record
}

// Function to update a record in the local database
function update_record_in_database($record) {
    // Code to update the record in the local database
}

// Function to add a record to the local database
function add_record_to_database($record) {
    // Code to add the record to the local database
}

You’ll need to replace the get_records_from_api(), get_record_from_database(), update_record_in_database(), and add_record_to_database() functions with your own code that communicates with your API and local database.

It’s important to note that this is just one way to solve the problem, and you may need to adjust the code depending on the specifics of your API and local database.