Import JSON in MySQL DB from Import.io

Question

I'm trying to import data from a JSON feed using PHP into a MySQL database.

I have the code below but am not getting anywhere with it.

I keep just getting Connected to Database but nothing is being pulled in from the JSON data.

The JSON data is being created from a feed using import.io.

Any help appreciated

JSON data here

    <?php

$data = file_get_contents('https://query.import.io/store/connector/e18543ae-48d1-47d3-9dc7-c3d55cab2951/_query?_user=363ec2db-fb95-413f-9a20-3fe89acbf061&_apikey=HOXvwSMX4HlmqH123i5HeELV6BwKq%2BFRInTzXc4nfl5VtP0pJyChxMT9AEiu1Ozi0vWZmUB%2BKcSsxHz2ElHNAg%3D%3D&format=JSON&input/webpage/url=http%3A%2F%2Fsports.yahoo.com%2Fgolf%2Fpga%2Fleaderboard');

$array = json_decode($data, true);
$rows = array();   

$index = 0;

foreach($array['results'] as $mydata)
    {
        print_r($mydata);
        echo "<br>";

        foreach($mydata as $key => $value)
            {
                print_r ($key);
                print_r ($value);
                echo $index;
                echo "<br><br>";
                $rows[] = "('" . $value . "')";
            }   
        echo "<br>";
        $index++;
    }

echo "<br><br><br>";
print_r ($rows);

$values = implode(",", $rows);
echo "<br><br><br>";
print_r ($values);

$hostname = 'localhost';                 // write the rest of your query
$username = 'username';
$password = 'password';

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=database", $username, $password);
    echo 'Connected to database<br />'; // echo a message saying we have connected 
    $count = $dbh->exec("INSERT INTO import_io (total, round_1, round_2, round_3, round_4, thru, name/_source, name, today, name/_text, strokes) VALUES ($values)"); 
    echo $count;// echo the number of affected rows
    $dbh = null;// close the database connection
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

?>

You've got some errors on your php code, after you have fetched the data from import.io you're trying to convert an array to a string in the foreach loop. so your result is like this: ('0','Array') ('1','Array')... try to do a print_r($values) to check the values you're trying to insert in the DB — Cristofor
– Cristofor, Commented Jul 15, 2014 at 12:18
foreach($array['results'] as $key => $value) $rows[] = "('" . $key . "', '" . $value . "')"; So this row is not working properly? I'm just wondering how to tackle it as I'm having trouble. Is it that the JSON has more results and I only want a particular part of them. i.e. the results section? — Alan
– Alan, Commented Jul 15, 2014 at 13:43
I've gotten to the point (i think) where I have all the data I need to put in the database into the variable called $values but it still won't populate. Any more help? — Alan
– Alan, Commented Jul 15, 2014 at 20:28
I've noticed that you're trying to insert all your values in one query you should use a query for every row you want to insert. — Cristofor
– Cristofor, Commented Jul 15, 2014 at 20:57
So that's another loop obviously. One problem I can see at the moment with the above results is long variable e.g. ('-'),('6:26 am'),('1:25 am'),('-'),('-'),('-'),('/golf/pga/players/David+Duval/12/scorecard/2014/29'),('http://sports.yahoo.com/golf/pga/players/David+Duval/12/scorecard/2014/29'),('-'),('David Duval'),('-'),('-'),('6:26 am'),('1:25 am'),('-'),('-'),('-'),('/golf/pga/players/David+Howell/565/scorecard/2014/29'),('http://sports.yahoo.com/golf/pga/players/David+Howell/565/scorecard/2014/29'),('-'),('David Howell'),('-'),.. How do I get them formatted correctly for INSERT? — Alan
– Alan, Commented Jul 15, 2014 at 21:01

Cristofor · Accepted Answer · 2014-09-11 22:23:42Z

1

First of here we have to fetch every row and then do another loop to fetch every value contained in that row, in this way we will obtain a 2D Array containing the data to format to put after in the db.

$i = 0;    
foreach($array['results'] as $result){
    foreach ($result as $key => $value)
        $rows[$i][] = "'" . $value . "'";
    $i++;
}

Then, here we format the data in order to fit our query that will be executed for every row fetched before.

try{
    $dbh = new PDO("mysql:host=$hostname;dbname=database", $username, $password);
    foreach ($rows as $row) {
        $row = implode(",",$row); //making a string from an array with each item separated by comma
        $query = "INSERT INTO import_io (total, round_1, round_2, round_3, round_4, thru, name/_source, name, today, name/_text, strokes) VALUES ($row)<br>"; 
        $count = $dbh->exec($query); 
    }
    $dbh = null;// close the database connection
}catch(PDOException $e){
    echo $e->getMessage();
}

edited Sep 11, 2014 at 22:23

answered Jul 15, 2014 at 21:31

Cristofor

2,0972 gold badges16 silver badges23 bronze badges

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

2 Comments

Alan Over a year ago

Thanks for that it's seems to be giving me what I want now but my DB isn't populating. I've print_r ($query); and it's showing me the correct SQL as I can run that in PHPMyAdmin and it works so I'm presuming it's something to do with the connection to the database. I had to change the fields with the / in them to 'name/_source'. Thanks again, I'll try mess around with the connection settings but it's not erroring out. I need it working by Thurs :(.

Alan Over a year ago

I escaped all the fields and removed the <br> at the end of the query and it worked fine. Thanks again for all the help. I also added this line ` $stmt = $dbh->exec("TRUNCATE TABLE import_io"); ` to clear the database for me each time. Hopefully this will get me through the weekends golf!!!

Collectives™ on Stack Overflow

Import JSON in MySQL DB from Import.io

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related