Complex JSON array to MySQL via PHP

Question

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);
    }
    ?>

Can you state in 10 words or less what you're trying to do? I've read your post three times, and I get that you're confused... but I'm confused with what you want to do. — Incognito
– Incognito, Commented Nov 7, 2013 at 13:39
I will add my entire code to the post and maybe that will help. But the main thing I'm looking for is how to I access information embedded a few layers within the JSON file. I want info from JSON -> Streams -> 0, then within 0 -> Channel. Then to repeat with 0-100. Hope that helps and will add my current code right now. — Eric Keen
– Eric Keen, Commented Nov 7, 2013 at 13:45
I'm pretty much purely interested => I'm interested and more context instead, please. — Virus721
– Virus721, Commented Nov 7, 2013 at 13:50

Incognito · Accepted Answer · 2013-11-07 13:54:53Z

2

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.
}

answered Nov 7, 2013 at 13:54

Incognito

20.8k15 gold badges82 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Keen Over a year ago

Exactly the information I was looking for, thanks a lot, much appreciated.

Hussain · Accepted Answer · 2013-11-07 14:14:29Z

2

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());         
    }
}

edited Nov 7, 2013 at 14:14

answered Nov 7, 2013 at 13:57

Hussain

5,2176 gold badges48 silver badges72 bronze badges

1 Comment

Eric Keen Over a year ago

worked well thanks, only thing I had to change was the '$stream->channel->display_name' to '$stream'['channel']['display_name']'. Might be due to my version of netbeans but oh well thanks again.

Leonardo · Accepted Answer · 2013-11-07 14:01:57Z

0

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);
}

edited Nov 7, 2013 at 14:01

answered Nov 7, 2013 at 13:48

Leonardo

7364 silver badges11 bronze badges

2 Comments

Eric Keen Over a year ago

Will test a few things with that now, problem is for each loop I want something from ['x'] (0 in this example) and then something from ['channel'] if that makes sense. I added my current code to my question maybe that will help.

Eric Keen Over a year ago

Looking through everything now and testing a few things, looks nice though bare with me!

Collectives™ on Stack Overflow

Complex JSON array to MySQL via PHP

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related