how to import json file from https web page into a mariadb or mysql database?-CodePudding

I am trying to import a file into a mariadb (mysql), database. A proof of concept file is in .json format on the web at this location.

earthquake.php

<?php
$database = false;
try {

    $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING, PDO::ATTR_EMULATE_PREPARES => true );
    $conn = new PDO('mysql:host=127.0.0.1;dbname=test;port=3306;charset=utf8','root','root', $options);


} catch (PDOException $e) {

    // Echo custom message. Echo error code gives you some info.
    echo '[{"error":"Database connection can not be estabilished. Please try again later.  Error code: ' . $e->getCode() . '"}]';
    exit;
}

$url = "https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_week.geojson";
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_URL, $url);
$result = curl_exec($ch);
$features = json_decode($result)->features;
foreach ($features as $feature) {
    echo 'Mag:  '.$feature->properties->mag.', Place:  '.$feature->properties->place.', '.gmdate("Y-m-d H:i:s", $feature->properties->time/1000).PHP_EOL;
    $query = 'INSERT INTO features (mag, place, time) VALUES (?, ?, ?)';
    $params = [$feature->properties->mag, $feature->properties->place, gmdate("Y-m-d H:i:s", $feature->properties->time/1000)];
    $stmt = $conn->prepare($query) or die ('["status":{"error":"Prepare Statement Failure","query":"' .$query . '"}]');
    $stmt->execute($params) or die('[{"error":"' . $stmt->errorInfo()[2]  . '","query":"' .$query . '","params":' .json_encode($params) . '}]');

}

?>

create a local DB called features with mag, place and time columns. If you have php on your system just run it from the CLI, php earthquake.php

e.g. Insert into mysql from Bash script

I use Laravel a bit, and would probably actually build my own model and use Eloquent and a little UI to handle that, but using a script seems like an option.