how to read API data and insert it into MYSQL with php

Question

So I am trying to get conversion rates from an API and I get the following data:

https://api.exchangeratesapi.io/history?start_at=2017-01-01&end_at=2018-09-01&symbols=EUR&base=GBP

How can I loop over the data and insert date & conversion rate into MYSQL DB.

Any help is greatly appreciated.

I am currently standing here:

$host="localhost";
$user="conversion";
$pass="password";
$db="areporting";
$connect= new mysqli($host,$user,$pass,$db) or die("ERROR:could not connect 
to the database!!!");


$string = file_get_contents("https://api.exchangeratesapi.io/history?start_at=2017-01-01&end_at=2018-09-01&symbols=EUR&base=GBP");
$json = json_decode($string, true);


var_dump($json);

here a screenshot of the Data I get:

you can do it by decoding the data into an array, looping over the array and then constructing an INSERT statement with a clause for each row of the array, and then executing that statement. What have you tried so far? — ADyson
– ADyson, Commented Jan 8, 2019 at 12:37
Im unsure how to loop this data I get... normaly I would do it with a forach loop, but I cant seem to loop it correctly — Michael
– Michael, Commented Jan 8, 2019 at 12:41
what problem happens, exactly? Did you decode the JSON into an array first? What does the JSON even look like? — ADyson
– ADyson, Commented Jan 8, 2019 at 12:55

HamzaNig · Accepted Answer · 2019-01-08 14:14:12Z

4

You can use foreach on json result :

  <?php
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
        $string = file_get_contents("https://api.exchangeratesapi.io/history?start_at=2017-01-01&end_at=2018-09-01&symbols=EUR&base=GBP");
        $json = json_decode($string, true);


        foreach($json['rates'] as $date =>$conversion){
            $sql = "INSERT INTO Mytable (id, date, conversion)
                    VALUES ( '$date', ".$conversion['EUR'].")";

            if ($conn->query($sql) === TRUE) {
                echo "New record created successfully"."<br>";
            } else {
                echo "Error: " . $sql . "<br>" . $conn->error."<br>";
            }

        }
        $conn->close();
        ?>

edited Jan 8, 2019 at 14:14

answered Jan 8, 2019 at 12:42

HamzaNig

1,0271 gold badge10 silver badges33 bronze badges

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

9 Comments

Michael Over a year ago

the foreach loop you posted doesnt work. I get the logic, but the Data I am not managing to loop through

HamzaNig Over a year ago

try now i updated my code i was having mistake on $date->EUR now it became conversion->EUR

ADyson Over a year ago

1. this could be done by constructing a single INSERT statement containing multiple clauses (one for each row required to insert), which would be a lot faster on the SQL side than running INSERT dozens of times. 2. Using parameterised queries would be much better solution (for avoiding syntax errors and also SQL injection attacks - we should not be trusting data coming from a remote source, even if we think we know what will be in it, we can't guarantee it won't ever be compromised)

Michael Over a year ago

@ADyson you mean to save it in an array and then implode the data into one query?

ADyson Over a year ago

@Michael "I just don;'t know how to loop through the Array on $json['rates']"... you loop by using foreach($json['rates'] as $date =>$conversion){ exactly as per this answer. This syntax even nicely gives you the key ($date) and the value ($conversion) for each entry in the array. I suggest you study the PHP loop syntax in the manual if you're not sure how to make a loop...

|

Michael · Accepted Answer · 2019-01-10 09:49:40Z

2

Thanks for all the Tips, here I have my working solution:

foreach($json['rates'] as $date => $conversion){

$timestamp = strtotime($date);
$sql = "INSERT INTO m_fx_rate_temp
(`base`, `counter`, `fxRate`, `date`) VALUES ('gbp', 'eur', ".$conversion['EUR'].", Date_format(FROM_UNIXTIME($timestamp), '%Y-%m-%d'))";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully"."<br>";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error."<br>";
}

}

answered Jan 10, 2019 at 9:49

Michael

1,0178 silver badges18 bronze badges

Collectives™ on Stack Overflow

how to read API data and insert it into MYSQL with php

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related