PHP and League of Legends API - How To Define Variables In Uniquely Named JSON Arrays to Insert into MySQL DB?

Question

I’m calling a League of Legends API and getting the below response (just a piece of it):

{
"data": {
"Thresh": {
  "id": 412,
  "key": "Thresh",
  "name": "Thresh",
  "title": "the Chain Warden"
},
"Aatrox": {
  "id": 266,
  "key": "Aatrox",
  "name": "Aatrox",
  "title": "the Darkin Blade"
},
"Tryndamere": {
  "id": 23,
  "key": "Tryndamere",
  "name": "Tryndamere",
  "title": "the Barbarian King"
}, …

I need to parse the values in each array and define them as variables to insert into MySQL db as a new row. I can do this for one array (for Thresh seen in my php below) but only if I set the name one at a time.

// Call API
$data = file_get_contents($api);

// Convert response to JSON array
$jsonarray = json_decode($data, true);

// Parse array values
$riot_id = $jsonarray['data']['Thresh']['id'];
$riot_key = $jsonarray['data']['Thresh']['key'];
$riot_name = $jsonarray['data']['Thresh']['name'];
$riot_title = $jsonarray['data']['Thresh']['title'];

// SQL to insert
$query = $mysqli->query("INSERT INTO champions (riot_id, riot_key, riot_name, riot_title) VALUES ('$riot_id','$riot_key','$riot_name','$riot_title')");

// Display result
if ($query === TRUE) {
printf("success");
       } else {
printf("failed: " . $query . "<br>" . $mysqli->error);
}

How can I get the values from each array and insert them into my db as a new row without doing this one at a time?

If you're trying to do a bulk insert in mysql with php, I would suggest [this](http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql). — Alexis Côté, Jul 13 '16 at 00:24
_"How can I get the values **from each** array"_ .. that was close: [**foreach**](http://php.net/manual/en/control-structures.foreach.php) — FirstOne, Jul 13 '16 at 00:24

score 1 · Accepted Answer · edited Jul 29 '16 at 07:06

Just use a foreach loop to build your query, then execute them in a single statement. It's much faster than executing once per loop.

I improved on your code by adding SQL injection protection (your code inserts data directly into your SQL without escaping it; very bad for security):

$jsonarray = json_decode($data, true);

$values = []; //holds all value sets to be inserted

//protects against SQL injection
$cleaner_func = function($dirty) use($mysqli){
    return $mysqli->real_escape_string($dirty);
};

//loop through each row to collect its value set
foreach($jsonarray['data'] as $row):
    $clean_row = array_map($cleaner_func,$row); //clean data

    //place clean data in vars
    list($id,$key,$name,$title) = array_values($clean_row);

    //add a new value set to be inserted
    $values[] = "('$id','$key','$name','$title')";
endforeach;

//build and execute the query
$sql = "INSERT INTO champions (riot_id, riot_key, riot_name, riot_title)"
    ." VALUES ".implode(", ",$values);
$mysqli->query($sql);

This may work, but its too complicated, better use a loop with prepared statements — meda, Jul 13 '16 at 02:10
I'm not so sure it's complicated, but if the OP doesn't understand it, I would agree that he/she should go with your solution; I'm a fan of making sure you understand the code you're using. This will perform significantly faster though; not enough to matter with small datasets, but with lots of rows... — BeetleJuice, Jul 13 '16 at 02:27

score 0 · Answer 2 · answered Jul 13 '16 at 00:26

0

It's actually very simple:

foreach($jsonarray['data'] as $key => $val) {
  // your code here, 'Thresh' is now $key
  // and e.g. $val['id'] gives the $riot_id
}

http://php.net/manual/en/control-structures.foreach.php

answered Jul 13 '16 at 00:26

maraca

8,468
3
23
45

meda · Answer 3 · 2016-07-13T02:06:52.927

0

$stmt = $mysqli->prepare(
"INSERT INTO champions (riot_id, riot_key, riot_name, riot_title) VALUES (?, ?, ?, ?)"
);
$stmt->bind_param('ssss', $riot_id,$riot_key,$riot_name,$riot_title);
$jsonarray = json_decode($data, true);

foreach ($jsonarray['data'] as $data) { 
    $riot_id = $data['id'];
    $riot_key = $data['key'];
    $riot_name = $data['name'];
    $riot_title = $data['title'];

    $stmt->execute();
    printf("%d Row inserted.\n", $stmt->affected_rows);
}
$stmt->close();

edited Jul 13 '16 at 02:06

answered Jul 13 '16 at 01:24

meda

45,103
14
92
122

This won't work because `$jsonarray['data'])` is an associative array, but you're trying to access its members with index `$i`. I think it will also be slow because you execute one query per row. – BeetleJuice Jul 13 '16 at 02:03
for each will work too, and no it wont be slow because prepare is called only once, multiple executes are fast – meda Jul 13 '16 at 02:07
(just noting that you fixed the error I raised; too late to edit my previous comment). About speed, you can test it yourself; I have. Try inserting 10,000 rows using one `prepare`, one `bind_param` and 10,000 `execute`. Compare that to inserting 10,000 rows using one `query` or one `multi_query` and you'll see. Your way is faster than 10,000 `query`, but not nearly as fast as one. – BeetleJuice Jul 13 '16 at 02:11

PHP and League of Legends API - How To Define Variables In Uniquely Named JSON Arrays to Insert into MySQL DB?

3 Answers3