Insert multiple JSON column in MySQL (using API)

Question

I'm trying to store sports data json column using API but I can't get it working properly. Though I can insert the data but it insert only the first column result and keep it duplicating on all the later columns. At default, it shows 50 column upcoming football matches. My problem is only the first line result is duplicating..

Here is the code:

//connect to mysql db
$host = "localhost";
$user = "user";
$pass = "pass";
$db = "dname";
$conn = new mysqli($host, $user, $pass, $db) or die("ERROR:could not connect to 
    the database!!!");

//read the json file contents
$url = 'http://api.example.com/v1/events/upcoming?token=566645-
    sl1uDzY3tVvv9M&league_id=78&sport_id=1';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($ch) or die(curl_error($ch));

if ($data === false) {
    $info = curl_getinfo($ch);
    curl_close($ch);
    die('error occured during curl exec. Additioanl info: ' .
            var_export($info));
}
curl_close($ch);
$obj = json_decode($data, true);

foreach ($obj['results'] as $res) {
    $league = $res['league']['name'];
    $home = $res['home']['name'];
    $away = $res['away']['name'];
    $time = date("d/m h:i", $res['time']);
}
$sql = "INSERT INTO schedule(league, home, away, time)
    VALUES('$league', '$home', '$away', '$time')";
$result = mysqli_query($conn, $sql);
if (!$result) {
    die("ERROR :" . mysqli_error());
} else {
    echo "Records has been successfully inserted";
}
//close the connection
mysqli_close($conn);

The above code just insert the first json result, and it keeps repeating for the next column (result).. Help will be appreciated.

Also you can first print all data to check whether all unique columns are coming. Add echo "<pre>"; print_r($obj['results']); die; before foreach — Amit Gupta
– Amit Gupta, Commented Dec 16, 2017 at 10:05

Gaurav Kumar · Accepted Answer · 2017-12-16 10:10:56Z

1

You are facing the problem because you are executing mysql query outside the foreach loop. You have to execute the query inside the loop.

Do something like

foreach ($obj['results'] as $res) 
{ 
$league = $res['league']['name']; 
$home = $res['home']['name']; 
$away = $res['away']['name']; 
$time = date("d/m h:i", 
$res['time']);

$sql = "INSERT INTO schedule(league, home, away, time) VALUES('$league', '$home', '$away', '$time')";
$result=mysqli_query($conn,$sql); 
if(!$result) 
{ 
die("ERROR :". mysqli_error()); 
} else 
{
echo "Records has been successfully inserted";
 } //close the connection mysqli_close($conn);
}

answered Dec 16, 2017 at 10:10

Gaurav Kumar

4362 silver badges8 bronze badges

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

Comments

Shoaib Quraishi · Accepted Answer · 2017-12-16 09:55:23Z

0

Put your insert query in foreach loop. Because your variable replace with new foreach result. Foreach reassign your veriables.

foreach ($obj['results'] as $res) {
    $league = $res['league']['name'];
    $home = $res['home']['name'];
    $away = $res['away']['name'];
    $time = date("d/m h:i", $res['time']);

    $sql = "INSERT INTO schedule(league, home, away, time)
    VALUES('$league', '$home', '$away', '$time')";
    $result = mysqli_query($conn, $sql);
    if (!$result) {
        die("ERROR :" . mysqli_error());
    } else {
        echo "Records has been successfully inserted";
    }
}

answered Dec 16, 2017 at 9:55

Shoaib Quraishi

2582 silver badges18 bronze badges

2 Comments

Jay Bro Over a year ago

Appreciate for response, but this doesn't stop the loop.. It keeps doubling as the page refreshes.

Shoaib Quraishi Over a year ago

Redirected to parent URL or Reassign your json variable as a blank

Amit Gupta · Accepted Answer · 2017-12-16 10:00:18Z

0

Your insert query must be in foreach like below:

foreach ($obj['results'] as $res){
    $league = $res['league']['name'];
    $home = $res['home']['name'];
    $away = $res['away']['name'];
    $time = date("d/m h:i", $res['time']);

    $sql = "INSERT INTO schedule(league, home, away, time)
    VALUES('$league', '$home', '$away', '$time')";
    $result=mysqli_query($conn,$sql);
    if(!$result){
        die("ERROR :". mysqli_error());
    }else{
        echo "Records has been successfully inserted";
    }
}

Though the above code can resolve your issue but its better if you start using MySqli Prepared Queries to make it more secure.

Follow below link for more details:

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

edited Dec 16, 2017 at 10:00

answered Dec 16, 2017 at 9:54

Amit Gupta

2,8162 gold badges19 silver badges35 bronze badges

4 Comments

Jay Bro Over a year ago

Appreciate for suggestion.. The loop keep repeating as the page refreshes.. Can you point the solution?

Amit Gupta Over a year ago

Have you tried with echo "<pre>"; print_r($obj['results']); die; before foreach

Amit Gupta Over a year ago

First check what data is coming

Amit Gupta Over a year ago

It will surely work if the data is coming in the form of array if you put insert query inside foreach.

Collectives™ on Stack Overflow

Insert multiple JSON column in MySQL (using API)

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related