Updating MySQL table from JSON using PHP

Question

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) {

Because json_decode gives you an Object not an Array. php.net/manual/en/function.json-decode.php — developerwjk
– developerwjk, Commented Apr 1, 2015 at 21:38
The JSON you are receiving contains the exchange rates as properties of an object, not as an array. Since the stdClass object doesn't include an iterator you need to extract the rate by reading each property you need. For seven that shouldn't be a problem. — user1864610
– user1864610, Commented Apr 1, 2015 at 21:38

Adrian Cid Almaguer · Accepted Answer · 2015-04-01 21:54:27Z

1

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

answered Apr 1, 2015 at 21:54

Adrian Cid Almaguer

7,79113 gold badges44 silver badges66 bronze badges

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

Comments

Marc B · Accepted Answer · 2015-04-01 21:48:26Z

0

Your JSON is decoding to an object, not an array. And even if it was an array, you'd be using it incorrectly:

foreach($exchangeRates['rates'] as $key => $value) {
         ^^^^^^^^^^^^^^^^^^^^^^---array
    mysqli_query([..snip..]WHERE strCode = '".$exchangeRates['rates']."' LIMIT 1")
                                              ^^^^^^^^^^^^^^^^^^^^^^^--same array

An array used in string context is the literal word Array. e.g.

$foo = array();
echo "This is an $foo"; // outputs 'This is an Array'

And as written, you're vulnerable to sql injection attacks.

answered Apr 1, 2015 at 21:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Tommy Over a year ago

It's a cron job and this file is not accessible from the webpage, is it a big issue? I guess the answer should be that I should learn how to prevent SQL injection, but for the moment, can it be an issue? Thanks.

Collectives™ on Stack Overflow

Updating MySQL table from JSON using PHP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related