Feeding data to database with PHP fails

Question

I have a PHP script supposed to insert data to a database on a web server. I got around 360 records. If I run the script only inserting one or two columns everything is OK. If i run the script feeding 10 columns or more, the script stop at 20-50 records. Why is this, and what can I do to make the script insert all records and all columns?

This is the first script, working perfectly:

connect_error) { //Mislykket DV-tilkobling
    die("Kunne ikke koble til database.");
  }



$kommune_api_url = "https://register.geonorge.no/api/sosi-kodelister/kommunenummer.json?";
$kommune_api_response = file_get_contents($kommune_api_url);
$kommune_api_data = json_decode($kommune_api_response,true);

$kommune_antall = $kommune_api_data['ContainedItemsResult']['Count'];

// LEGGER TIL KOMMUNENUMMER I DB.
for ($kom = 0; $kom <= $kommune_antall; $kom++) {
    $kommunenummer = $kommune_api_data['containeditems'][$kom]['codevalue'];
    $kommune_navn = $kommune_api_data['containeditems'][$kom]['description'];

    // Legger til kommunenummer i kommunenummer
    $sql = "INSERT INTO kommuneinfo (kommunenummer, kommunenavn)
    VALUES ($kommunenummer, '$kommune_navn')";

    if (mysqli_query($tilkobling, $sql)) {
    echo "";
    } else {
    echo "Error! Kommunenummer" . $kommunenummer . " / " . $kommune_navn . " ble ikke oppdatert.
" . mysqli_error($conn);
    }
    }

?>

When reading from another API and inserting much more data this is the script, which fails after 20-50 insertions:

connect_error) { //Mislykket DV-tilkobling
    die("Kunne ikke koble til database.");
  }



$kommune_api_url = "https://register.geonorge.no/api/sosi-kodelister/kommunenummer.json?";
$kommune_api_response = file_get_contents($kommune_api_url);
$kommune_api_data = json_decode($kommune_api_response,true);

$kommune_antall = $kommune_api_data['ContainedItemsResult']['Count'];

// LEGGER TIL KOMMUNENUMMER I DB.
for ($kom = 0; $kom <= 360; $kom++) {
    $kommune = $kommune_api_data['containeditems'][$kom]['codevalue'];
    $kommune_navn = $kommune_api_data['containeditems'][$kom]['description'];
    
    if ($kommune != "2211") {
        echo $kommune_navn . "
";
        echo $kom . "
";
    
        $url = "https://data.ssb.no/api/v0/no/table/06913/";
    
        $curl = curl_init($url);
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_POST, true);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    
        $headers = array(
        "Accept: application/json",
        "Content-Type: application/json",
        );
        curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
    
        $data = <<" . mysqli_error($conn);
        }
        } 
    }

echo "
Ferdig";



?>

user_3986005 · Answer

I ran your code.

First I got rid of all the database-related stuff, because I don't think the problem is the database. Then I added curl_error(), like this:

$resp = curl_exec($curl);
echo curl_error($curl);
curl_close($curl);

because I suspect that curl is the real problem. And indeed I get a nice error back after 61 calls:

{"error":"429 - Too many requests in too short timeframe. Please try again later."}.

That's because you try to make 360 requests in a loop. The API doesn't like that.

I've a bit further away from Norway, so my calls are done a bit slower and it takes a bit longer to reach the limit than it did with you. This hints at an easy and lazy way to solve this problem. You could add a long enough usleep() inside the loop to slow it down just enough so you don't hit the limit.

A better way to solve this problem would be if it was possible to make less calls to get the same data. I don't know if this is possible, but my guess is that it is.

Feeding data to database with PHP fails

1 Answers