Store json content from API to mysql Database

Question

Trying to store from an API some json data to MySQL.

I am using this simple php code

<?php 

    require 'config.php';

    $url = "https://www.widgety.co.uk/api/operators.json?app_id<key>&token<token>";
    $string = file_get_contents('https://www.widgety.co.uk/api/operators.json?app_id<key>&token=<token>');
    $arr = json_decode($string, true);

    foreach($arr as $item){
        $title          = $item['operator']['title'];
        $id             = $item['operator']['id'];
        $profile_image  = $item['operator']['profile_image_href'];
        $cover_image    = $item['operator']['cover_image_href'];
        $href           = $item['operator']['href'];
        $html_href      = $item['operator']['html_href'];
        $unique_text    = $item['operator']['unique_text'];
        $reasons_to_book = $item['operator']['reasons_to book'];
        $members        = $item['operator']['members_club'];
        $brochures      = $item['operator']['brochures'];
        $ships          = $item['operator']['ships'];
        $video_url      = $item['operator']['video_url'];

        $query("INSERT INTO operator (title, id, profile_image) VALUES('$title', '$id', '$profile_image')");

        $dataatodb = mysqli_query($con, $query);

        if(!$dataatodb){
            die("Error" . mysqli_error($con));
        }

    }


    ?>

But I get this error

 Warning: file_get_contents(app_id<key>&token<token>): failed to open stream: HTTP request failed! HTTP/1.1 401 Unauthorized in C:\xampp\htdocs\mypage\dataload.php on line 6

 Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\mypage\dataload.php on line 9

What am I doing wrong?

PS On URL i have the right app_id and token

Your code is vulnerable to SQL injections. Please learn to use [prepared statements](https://www.youtube.com/watch?v=nLinqtCfhKY). — tereško, Jun 20 '17 at 10:39
use CURL instead of `file_get_contents` most of the production environment doesn't allow `file_get_contents` for security reasons. — Dileep Kumar, Jun 20 '17 at 10:46
Have you tried the generated URL in a brand new browser session to see if the generated URL is working? Have you checked that your PHP installation is [configured to allow urls to be opened](http://us2.php.net/manual/en/filesystem.configuration.php#ini.allow-url-fopen)? — Rowland Shaw, Jun 20 '17 at 10:46
Possible duplicate of [PHP file\_get\_contents() returns "failed to open stream: HTTP request failed!"](https://stackoverflow.com/questions/697472/php-file-get-contents-returns-failed-to-open-stream-http-request-failed) — Rowland Shaw, Jun 20 '17 at 10:46
@RowlandShaw is not diplicate i have error on foreach and mine is with sql — Maria, Jun 20 '17 at 10:55
You have a warning telling you it couldn't open the data. That is why you cannot iterate over it. — Rowland Shaw, Jun 20 '17 at 11:18

score 2 · Answer 1 · answered Jun 20 '17 at 11:10

/* create a connection */
$mysqli = new mysqli("localhost", "root", null, "yourDatabase");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* let's say we're grabbing this from an HTTP GET or HTTP POST variable called jsonGiven... */
$jsonString = $_REQUEST['jsonGiven'];
/* but for the sake of an example let's just set the string here */
$jsonString = '{"name":"jack","school":"colorado state","city":"NJ","id":null}
';

/* use json_decode to create an array from json */
$jsonArray = json_decode($jsonString, true);

/* create a prepared statement */
if ($stmt = $mysqli->prepare('INSERT INTO testdemo (name, school, city, id) VALUES (?,?,?,?)')) {

    /* bind parameters for markers */
    $stmt->bind_param("ssss", $jsonArray['name'], $jsonArray['school'], $jsonArray['city'], $jsonArray['id']);

    /* execute query */
    $stmt->execute();

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

score 1 · Accepted Answer · answered Jun 20 '17 at 10:47

1

You are treating your json``response as anarray` while it is an object.

foreach ($jsonObject as $item) {
    $title = $item->title;
}

Take notice of the -> instead of ['']

For further debugging use var_dump() to check if your variable contains an actual object or if your api request has failed.

$apiURL = 'url';
$response = file_get_contents($apiUrl);
$jsonResponse = json_decode($response);
var_dump($jsonResponse);

answered Jun 20 '17 at 10:47

Peter

8,776
6
62
95

Didnt solve it but var_dump help me cause i get a NULL response so my request fail – Maria Jun 20 '17 at 11:11

Store json content from API to mysql Database

2 Answers2