Hi all and thanks for your patience,
I'm trying to write a cron job to update currencies rate in our MySQL database. The JSON data that I'm receiving looks like this;
{
"disclaimer": "Exchange rates are provided for informational purposes only, and do not constitute financial advice of any kind. Although every attempt is made to ensure quality, NO guarantees are given whatsoever of accuracy, validity, availability, or fitness for any purpose - please use at your own risk. All usage is subject to your acceptance of the Terms and Conditions of Service, available at: ",
"license": "Data sourced from various providers with public-facing APIs; copyright may apply; resale is prohibited; no warranties given of any kind. Bitcoin data provided by. All usage is subject to your acceptance of the License Agreement available at:",
"timestamp": 1427914861,
"base": "CAD",
"rates": {
"AED": 2.908081,
"AFN": 45.794285,
"ALL": 103.179929,
"AMD": 373.363817,
"ANG": 1.416823,
"AOA": 85.603315,
"ARS": 6.986543,
"AUD": 1.041048,
"AWG": 1.42113,
"AZN": 0.829254,
"BAM": 1.437242,
"BBD": 1.583432,
"BDT": 61.66817,
"BGN": 1.437963,
"BHD": 0.298493,
"BIF": 1246.009421,
"BMD": 0.791716,
"BND": 1.080918,
"BOB": 5.468926,
"BRL": 2.518805,
"BSD": 0.791716,
"BTC": 0.0032649636,
"BTN": 49.501403,
"BWP": 7.855039,
"BYR": 11644.270337,
"BZD": 1.581753,
"CAD": 1,
"CDF": 733.551108,
"CHF": 0.76573,
"CLF": 0.019475,
"CLP": 490.205281,
"CNY": 4.895048,
"COP": 2038.824734,
"CRC": 422.26163,
"CUC": 0.791716,
"CUP": 0.791726,
"CVE": 80.458447,
"CZK": 20.263721,
"DJF": 140.548137,
"DKK": 5.492318,
"DOP": 35.391341,
"DZD": 77.203651
}
}
Since the table already exist, all I want to do is to update the currencies that we have in the table, not all currencies that the JSON file is giving me. We only have 7 currencies in our table (it can change depending on if we accept more currencies or less). This is the code I come up with;
<?php
define("_SITE_SECURED_FILES_",realpath(dirname(__FILE__)."/../../../../")."\Secured_Files");
// Database Credentials
require_once(_SITE_SECURED_FILES_."\Database\credentials.mysql.php");
// Open Exchange Rates Credentials
require_once(_SITE_SECURED_FILES_."\Open_Exchange_Rates\credentials.openexchangerates.php");
// Requested file
// Could also be e.g. 'currencies.json' or 'historical/2011-01-01.json'
$file = 'latest.json';
$base_currency = 'CAD';
// Open CURL session
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://openexchangerates_org/api/{$file}?app_id={$appId}&base={$base_currency}");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
// Get the data:
$json = curl_exec($ch);
curl_close($ch);
// Decode JSON response:
$exchangeRates = json_decode($json);
foreach($exchangeRates['rates'] as $key => $value) {
if($value) {
//how to use json array to insert data in Database
mysqli_query($mysql,"UPDATE currencies SET dblRateCAD = '".$value."' WHERE strCode = '".$exchangeRates['rates']."' LIMIT 1");
}
}
?>
Right now, it doesn't work. I'm getting
PHP Fatal error: Cannot use object of type stdClass as array on line 29: foreach($exchangeRates['rates'] as $key => $value) {
If you want an array just use json_decode($json, true)
and modify your query to "UPDATE currencies SET dblRateCAD = '".$value."' WHERE strCode = '". $key ."'
// Decode JSON response:
$exchangeRates = json_decode($json, true);
foreach($exchangeRates['rates'] as $key => $value) {
if($value) {
//how to use json array to insert data in Database
mysqli_query($mysql,"UPDATE currencies SET dblRateCAD = '".$value."' WHERE strCode = '". $key ."' LIMIT 1");
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With