elofox elofox - 4 months ago 12
MySQL Question

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

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?

Answer

Just use a foreach loop to build your quey, 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 insert values

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

//add a new query for each record that you need to insert
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);
Comments