How to use get all content from API using cURL

I have an API that has 418 pages with data, i am trying to get all the content in the API and put them in my MySQL database.

Currently i can only put the first page content inside the database. How do i solve this?
Here is my Code.

data.php

<?php

include_once './config/Database.php';

$url = "http://exampledomain.com/api/properties";

$ch = curl_init($url);

$headers = array(
  'Content-type: application/json',
  'API_KEY: 2S7rhsaq9X1cnfkMCPHX64YsWYyfe1he',
);

curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

$resp = curl_exec($ch);

if($e = curl_error($ch)){
    echo $e;
}
else {
    $decoded = json_decode($resp,true);
    // print_r($decoded);
    $query = 
            "INSERT 
              
              INTO 
                  details 
                  (
                    county,
                    country,
                    town,
                    description,
                    displayable_address,
                    image,
                    thumbnail,
                    latitude,
                    longitude,
                    bedrooms,
                    bathrooms,
                    price,
                    -- property_type,
                    type
                  ) 
              VALUES 
                  (
                    :county,
                    :country,
                    :town,
                    :description,
                    :address,
                    :image_full,
                    :image_thumbnail,
                    :latitude,
                    :longitude,
                    :num_bedrooms,
                    :num_bathrooms,
                    :price,
                    -- :property_type.description,
                    :type
                  )
              ";
      //Prepare Statement     
      $stmt = $conn->prepare($query);
     

      //Bind data
      $stmt->bindParam(':county', $county);
      $stmt->bindParam(':country', $country);
      $stmt->bindParam(':town', $town);
      $stmt->bindParam(':description', $description);
      $stmt->bindParam(':address', $displayable_address);
      $stmt->bindParam(':image_full', $image);
      $stmt->bindParam(':image_thumbnail', $thumbnail);
      $stmt->bindParam(':latitude', $latitude);
      $stmt->bindParam(':longitude', $longitude);
      $stmt->bindParam(':num_bedrooms', $bedrooms);
      $stmt->bindParam(':num_bathrooms', $bathrooms);
      $stmt->bindParam(':price', $price);
      // $stmt->bindParam(':property_type.description', $property_type);
      $stmt->bindParam(':type', $type);

    foreach($decoded["data"] as $decode){

      $county = $decode['county'];
      $country = $decode['country'];
      $town = $decode['town'];
      $description = $decode['description'];
      $displayable_address = $decode['address'];
      $image = $decode['image_full'];
      $thumbnail = $decode['image_thumbnail'];
      $latitude = $decode['latitude'];
      $longitude = $decode['longitude'];
      $bedrooms = $decode['num_bedrooms'];
      $bathrooms = $decode['num_bathrooms'];
      $price = $decode['price'];
      // $property_type = $decode['property_type']['description'];
      $type = $decode['type'];
      

      
    $stmt->execute();
    }
}

curl_close($ch);

Database.php

<?php
$servername = "localhost";
$username = "henry";
$password = "love2020";
$dbname = "api-call";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Database Connected successfully and Record Addedd"; 
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

The above code is only inserting the contents on the first page to the database, i want to insert all the contents from page 1 to 418 to the database.

This is the API parameter

http://exampledomain.com/api/properties?page[number]=418
$url = "http://exampledomain.com/api/properties";

Well, first, I see you never finished fixing your “property_type” variable. We solved that in another post…

Now, in your posted code, you show the above line. It does not include the page number. To pass a page number, it would be done this way: (Loosely)
API.PHP

if (isset($_GET["page"]) {
    //  Page number was posted by user, get it
    $page_number = $_GET["page"];
} else {
    //  No page number was sent in URL, set it to first page
    $page_number = 1;
}
... process data for the page number in the variable $page_number ...

The rest of the API would be the same except it would need to get the correct page of data.

Now on the calling section, to retrieve all pages, you would do something loosely like this:

//  Assuming the page numbers are 1 to 418...  ( In real life, they might be 0 to 417 )
for ($page = 1; $page <= 418; $page++) {
    $url = "http://exampledomain.com/api/properties?page=".$page;
    ... Process your curl and decode and database insert for this one page ...
}

There you have the basics of how this would be done. Hope it helps…

@ErnieAlex The code above entered all the pages in the database but i get this error at some point while it was inserting the records Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\api-call\data.php on line 88 PHP Warning: foreach() argument must be of type array|object, null given in C:\xampp\htdocs\api-call\data.php on line 88 The line is from the foreach($decoded["data"] as $decode){ in the question code above

Well, making headway at least…

So, I am guessing that the $decoded array is messed up. My guess is that the API for that bad line is creating an incorrect JSON for one entry. And, then, when it is converted to a PHP array, it messes up the $decode results.

A workaround fix would be to check the $decode to see if it is null or not just inside the foreach.
Something loosely like this:

//  Assuming the page numbers are 1 to 418...  ( In real life, they might be 0 to 417 )
for ($page = 1; $page <= 418; $page++) {
    $url = "http://exampledomain.com/api/properties?page=".$page;
    ... Process your curl and decode and database insert for this one page ...
    foreach($decoded["data"] as $decode) {
        if(isset($decode)) {    //  Check if $decode is set and NOT NULL
            ...  Process the valid $decode variable data ...
        }
    }
}

But, the right way would be to test the API script to see where it is sending out bad data. This could take a lot of work. To test it, you would have to look at the live json file and see what data it creates. And, then, do your conversion to array format and look at the live dump of the array to see where the error is. Once you locate the bad data in both formats, you need to look at your API code to see why it is letting null values thru the code.

Hope that helps…

@ErnieAlex Checking the is a lot of work as it has over 1000 records. But everything you wrote helped me and i really appreciate. About the property_type variable i haven’t figured it out because i do not know how to bind it and insert it into database. when i print_r $property from $property_type = $decode['property_type']['description']; i get list of all the properties but binding and inserting is where i am confused. I really new to this

Well, binding is not an issue. You bind it using the variable you want to call it. It is getting the data into the variable that was your problem. You had an extra dimension of the array. But, I figured that out for you before. In the other post.

Yes, to review all 1000 records is very time-consuming. But, if it is sending NULL data, the API must have a problem in it. Or, it does not have full data for one property and it should be sending something instead of the NULL. Like “not-available” or “N/A” or something so the empty data is not null or empty.
Does that make sense? So, either review all 1000 records or write a routine to locate the back one.
Or, just ignore that one using the code example I showed you… Good luck!

@ErnieAlex yeah you said this You must remember you are using json_decode(…, 1) or json_decode(…,true) which creates an array. The “property_type” is a sub level of “data” and it contains an array of items. Therefore, you must get the correct entry of correct level of sub-arrays. but i have not figured it out yet

I gave you the solution on the other post about this. To access a sub-level, you just add brackets.
Sort of loosely like this:
$property_type = $decoded[“data”][“property_type”][“data”];
I do not remember the solution, but, you just need to use a second or third [ ] as needed to get the data.
Go back to the other post and if you can not figure it out, let me know in that post.

Sponsor our Newsletter | Privacy Policy | Terms of Service