CODEWITHSUNDEEP
phpmysqljsonapi
Eric Keen
Eric Keen

Reputation: 21

Complex JSON array to MySQL via PHP

I'm aiming to put a selection of info from a JSON result into MySQL via PHP. The specific issue I'm having is with accessing specific info from the JSON result, as it isn't a simple list format. Here is a screenshot of the kind of structure I'm talking about with the information I'm interested in highlighted.:

I sadly don't have enough reputation to post an image so here it is rehosted on imgur:

enter image description here

Under ['streams'] will be a list of 100 streams. From each of those results I want to take the highlighted information and place it into a MySQL table.

I'm fairly comfortable with simpler JSON files and easier applications of json_decode

    $result = json_decode($json);

    foreach ($result as $key => $value)

I'm thinking perhaps I need to use the depth variable? But finding it difficult to find much information on it.

If anyone can give me any help or point me towards a good source of info (As I have struggled to find anything) then it would be much appreciated.

If anymore information would be useful let me know and I will add it.

edit: link to json request: https://api.twitch.tv/kraken/streams?limit=2/

<?php
    //import data from twitch
    $json = json_decode(file_get_contents("https://api.twitch.tv/kraken/streams?limit=100/"), true);

    //create a DB connection
    $con = mysql_connect("CONNECTION INFO :D");
    mysql_connect_db('NOPE', $con);


    $result = json_decode($json);
    foreach ($result as $key => $value) {
        if ($value) {


            mmysql_query("INSERT INTO twitchinfo (viewers, status, display_name, game, delay, name) VALUES ($value->viewers, $value->status,$value->display_name,$value->game,$value->delay,$value->name)")or die(mysql_error());
        }
        // end connection
        mysql_close($con);
    }
    ?>

Upvotes: 0

Views: 1860

Answers (3)

Hussain
Hussain

Reputation: 5177

Opening this url in browser - 

https://api.twitch.tv/kraken/streams?limit=100/

Gives me a JSON. I copied it in -

http://www.jsoneditoronline.org/

and saw that it has streams and _links keys.

As per your issue is concerned, try this -

$result = json_decode($json);
if( isset( $result['streams'] )){
    $streams = $result['streams'];
    foreach($streams as $stream) {
        $viewers = $stream->viewers;
        $status = $stream->channel->status;
        $display_name = $stream->channel->display_name;
        $game = $stream->channel->game;
        $delay = $stream->channel->delay;
        $name = $stream->channel->name;
        $sql = "INSERT INTO twitchinfo (viewers, status, display_name, game, delay, name) VALUES ($viewers, \"$status\", \"$display_name\", \"$game\", $delay, \"$name\")";
        mysql_query($sql)or die(mysql_error());         
    }
}

Upvotes: 2

Leonardo
Leonardo

Reputation: 736

May be you can do this:

$values = array();
$result = json_decode($json);

foreach($result['streams'] as $stream) {
    array_push(
        $values,
        array(
            'viewers'      => $stream['viewers'],
            'status'       => $stream['channel']['status'],
            'display_name' => $stream['channel']['display_name'],
            'game'         => $stream['channel']['game'],
            'delay'        => $stream['channel']['delay'],
            'name'         => $stream['channel']['name'],
        )
    );
}

Or:

foreach($result['streams'] as $stream) {
    $sqlQuery= "INSERT INTO TABLE(viewers, status, display_name, game, delay, name) VALUES ($stream['viewers'], $stream['channel']['status'], $stream['channel']['display_name'], $stream['channel']['game'], $stream['channel']['delay'], $stream['channel']['name']);"
    //dbHandler->executeQuery($sqlQuery);
}

Upvotes: 0

Incognito
Incognito

Reputation: 20765

Your JSON object is basically something like...

[
    links: {
        self: http://example.com,
        next: http://example.com/foo,
    },
    streams: [
            {
                channel: {
                    foo: 'bar'
                },
                one: 'one',
                somethingElse: 'Something else',
                moreStuff: 'more stuff',
            }
    ]
]

When you decode a JSON object you are left with a PHP Object/Array that represents the object.

$x = json_decode('[1,2,3]')

is going to give you the same array as...

$x= array(1,2,3)

If you load up the JSON you've shown and run this:

foreach ($result as $key => $value)

This is the same as accessing $result->links and $result->streams. Each contains more data.

If I wanted to grab the 'foo' element from the channel in my example I would do:

$streams = $result->streams //Get the streams array
$stream = $streams[0]       //Get the first stream object
$channel = $stream->channel //Get the channel object
$foo = $channel->foo        //Get the value 'bar' out of the foo property.

As long as the structure is predictable (and it is), I can iterate over the streams since it's just an array. 

$streams = $result->streams //Get the streams array
foreach ($streams as $stream) {
    $channel = $stream->channel //Get the channel object
    $foo = $channel->foo        //Get the value 'bar' out of the foo property of every stream.
}

Upvotes: 2

Related Questions