PHP will not parse full JSON file for all results

Question

The php script will only parse maybe 1 to 2 results but not the full file. The JSON file has about 200 results in it.

Here is the php file

$url = 'http://ironcentral.org/carnivore/api/nation_data/iron_nations';
$content = file_get_contents($url);
$json = json_decode($content, true);
$con = mysqli_connect("localhost", "user", "pass", "iron");
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

foreach($json as $item) {
    $sql = "INSERT INTO nations (nationid, ruler, nation, gov, religion, tech, infra, land, mode, resource1, resource2, strength, defcon, soldiers, tanks, cruise, nukes, slots) VALUES ('$item[nationid]','$item[ruler]','$item[nation]','$item[gov]','$item[religion]','$item[tech]','$item[infra]','$item[land]','$item[mode]','$item[resource1]','$item[resource2]','$item[strength]','$item[defcon]','$item[soldiers]','$item[tanks]','$item[cruise]','$item[nukes]','$item[slots]')";

}
mysqli_query($con, $sql) or die(mysqli_error($con));
mysqli_close($con);

John Conde · Accepted Answer · 2015-10-04T21:50:58.243

Your call to mysqli_query() is outside of your loop so it is only run nce after the loop is finished executing. Move it to inside the loop:

foreach($json as $item) {
    $sql = "INSERT INTO nations (nationid, ruler, nation, gov, religion, tech, infra, land, mode, resource1, resource2, strength, defcon, soldiers, tanks, cruise, nukes, slots) VALUES ('$item[nationid]','$item[ruler]','$item[nation]','$item[gov]','$item[religion]','$item[tech]','$item[infra]','$item[land]','$item[mode]','$item[resource1]','$item[resource2]','$item[strength]','$item[defcon]','$item[soldiers]','$item[tanks]','$item[cruise]','$item[nukes]','$item[slots]')";
    mysqli_query($con, $sql) or die(mysqli_error($con));
}

You can also make it just one query and execute it all in one statemnt:

$sql = "INSERT INTO nations (nationid, ruler, nation, gov, religion, tech, infra, land, mode, resource1, resource2, strength, defcon, soldiers, tanks, cruise, nukes, slots) VALUES ";
foreach($json as $item) {
    $sql .= "('$item[nationid]','$item[ruler]','$item[nation]','$item[gov]','$item[religion]','$item[tech]','$item[infra]','$item[land]','$item[mode]','$item[resource1]','$item[resource2]','$item[strength]','$item[defcon]','$item[soldiers]','$item[tanks]','$item[cruise]','$item[nukes]','$item[slots]'), ";
}
$sql = rtrim($sql, ',');
mysqli_query($con, $sql) or die(mysqli_error($con));

or

$sql = "INSERT INTO nations (nationid, ruler, nation, gov, religion, tech, infra, land, mode, resource1, resource2, strength, defcon, soldiers, tanks, cruise, nukes, slots) VALUES ";
$inserts = [];
foreach($json as $item) {
    $inserts[] = "('$item[nationid]','$item[ruler]','$item[nation]','$item[gov]','$item[religion]','$item[tech]','$item[infra]','$item[land]','$item[mode]','$item[resource1]','$item[resource2]','$item[strength]','$item[defcon]','$item[soldiers]','$item[tanks]','$item[cruise]','$item[nukes]','$item[slots]') ";
}
$sql .= implode(',', $inserts);
mysqli_query($con, $sql) or die(mysqli_error($con));

When doing Option 3 [Sun Oct 04 17:42:34.694326 2015] [:error] [pid 4468] [client 69.120.197.63:46043] PHP Parse error: syntax error, unexpected '$inserts' (T_VARIABLE) in /var/www/html/login/nations.php on line 16 Option 1 works but then I get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'i Faith','17,121.50','8,999.99','1,100.001','War Mode','Wheat','Iron','123,530.4' at line 1 — Ryahn, Oct 04 '15 at 21:42
My bad. I left an errant comma in there. I obviously can't test this code so things like that sometimes sneaks through. — John Conde, Oct 04 '15 at 21:44
Option 3 doesnt pass through except through `Parse error: syntax error, unexpected '$inserts' (T_VARIABLE) in /var/www/html/login/nations.php on line 16` — Ryahn, Oct 04 '15 at 21:50
Dude, you gotta be able to do some of your own debugging here. It was just a missing comma in `implode()`. — John Conde, Oct 04 '15 at 21:51

score 0 · Answer 2 · answered Oct 04 '15 at 22:53

bind_param is another option for escaping. This answer assumes a lot of things: 1. All the columns in the .json are in the order you need to insert. (must be true or you'll have to do some stuff to get this to work) 2. All the columns are string (sorta easy fix. The s in "ssssssssssssssssss" all correspond to a column you want to insert. Replace any s with i if that column is an int)

$insertColumns = array("nationid", "ruler", "nation", "gov", "religion", "tech", "infra", "land", "mode", "resource1", "resource2", "strength", "defcon", "soldiers", "tanks", "cruise", "nukes", "slots"); 
$sql = "INSERT INTO skill (" . implode(",",$insertColumns) . ") VALUES (" . implode(",",array_fill(0,count($insertColumns),"?")) . ")";

if($stmt = mysqli_prepare($con,$sql)) {
    foreach($json  as $item) {
        call_user_func_array("mysqli_stmt_bind_param", refValues(array_merge(array($stmt, "ssssssssssssssssss"),array_values($item))));

        mysqli_stmt_execute($stmt);
    }
}

// shameless borrowing http://stackoverflow.com/a/16120923/5051310
function refValues($arr) {
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}
?>

PHP will not parse full JSON file for all results

2 Answers2