up vote -1 down vote favorite

I am trying to create a PHP script that decodes a JSON array and insert it into a database. So far i've managed to get the script to insert the first row in the array and nothing else.

What would I need to add to this to get the script to insert all the rows in the array?

Here's the array, ignore "listings", I don't need that data yet (It's quite big): json Here's the script:

<?php
$servername = "localhost";
$username = "root";
$password = "";

// Create connection
$con = mysql_connect($servername, $username, $password);

//select db
$selected = mysql_select_db("ed",$con);

$json_obj = file_get_contents("stations.json");

//convert to stdclass object
$arr = json_decode($json_obj,true);

//store the element values into variables

$id = $arr[0]["id"];
$name = $arr[0]["name"];
$system_id = $arr[0]["system_id"];
$max_landing_pad_size = $arr[0]["max_landing_pad_size"];
$distance_to_star = $arr[0]["distance_to_star"];
$faction = $arr[0]["faction"];
$government = $arr[0]["government"];
$allegiance = $arr[0]["allegiance"];
$state = $arr[0]["state"];
$type = $arr[0]["type"];
$has_blackmarket = $arr[0]["has_blackmarket"];
$has_commodities = $arr[0]["has_commodities"];
$has_refuel = $arr[0]["has_refuel"];
$has_repair = $arr[0]["has_repair"];
$has_rearm = $arr[0]["has_rearm"];
$has_outfitting = $arr[0]["has_outfitting"];
$has_shipyard = $arr[0]["has_shipyard"];

//insert values into mysql database
$sql="INSERT INTO stations (station_id, name, system_id, max_landing_pad_size, distance_to_star, faction, government, allegiance, state, type, has_blackmarket, has_commodities, has_refuel, has_repair, has_rearm, has_outfitting, has_shipyard) 
VALUES ('$id', '$name', '$system_id', '$max_landing_pad_size', '$distance_to_star', '$faction', '$government', '$allegiance', '$state', '$type', '$has_blackmarket', '$has_commodities', '$has_refuel', '$has_repair', '$has_rearm', '$has_outfitting', '$has_shipyard')";

if(!mysql_query($sql,$con)) //$con is mysql connection object
{
     die('Error : ' . mysql_error());
}
?>
share
1  
if its going to be really big, you could consider another approach, create a query string first which will enable you to create an insert batch, then run it. or create a file then load data local infile. thats going to be much faster instead of looping rows one by one insertion – Ghost Apr 24 '15 at 14:26
up vote 0 down vote

try this.

$arr = json_decode($json_obj,true);

$sql = 'INSERT INTO stations (`';

$sql.=  implode('`,`', array_keys( $arr[0] ) );

$sql.= '`) values (\'';

$sql.=  implode('\',\'',  $arr[0]  );

$sql.= '\')';
share
    
I get this when it does it: "Notice: Array to string conversion in C:\wamp\www\index.php on line 42" – Thomas Ward Apr 24 '15 at 14:50
    
You need be sure that $arr[0] have all the 'key'=>'values' combinations. – julian Apr 24 '15 at 15:14
up vote 0 down vote

Use foreach 

$arr = json_decode($json_obj,true);//decode object
foreach($arr as $ar){

            $id = $ar["id"];
            $name = $ar["name"];
            $system_id = $ar["system_id"];
            $max_landing_pad_size = $ar["max_landing_pad_size"];
            $distance_to_star = $ar["distance_to_star"];
            $faction = $ar["faction"];
            $government = $ar["government"];
            $allegiance = $ar["allegiance"];
            $state = $ar["state"];
            $type = $ar["type"];
            $has_blackmarket = $ar["has_blackmarket"];
            $has_commodities = $ar["has_commodities"];
            $has_refuel = $ar["has_refuel"];
            $has_repair = $ar["has_repair"];
            $has_rearm = $ar["has_rearm"];
            $has_outfitting = $ar["has_outfitting"];
            $has_shipyard = $ar["has_shipyard"];

            //insert values into mysql database
            $sql="INSERT INTO stations (station_id, name, system_id, max_landing_pad_size, distance_to_star, faction, government, allegiance, state, type, has_blackmarket, has_commodities, has_refuel, has_repair, has_rearm, has_outfitting, has_shipyard) 
            VALUES ('$id', '$name', '$system_id', '$max_landing_pad_size', '$distance_to_star', '$faction', '$government', '$allegiance', '$state', '$type', '$has_blackmarket', '$has_commodities', '$has_refuel', '$has_repair', '$has_rearm', '$has_outfitting', '$has_shipyard')";

            if(!mysql_query($sql,$con)) //$con is mysql connection object
            {
                 die('Error : ' . mysql_error());
            }

}
share
    
PHP seems to throw an error with this: "Notice: Undefined variable: arr in C:\wamp\www\index.php on line 17" – Thomas Ward Apr 24 '15 at 14:34
    
Notice only it will work – Rex Rex Apr 24 '15 at 15:23
up vote 0 down vote

For big amounts of data like in this case, you would want to execute the query just once, or you'll burden your database unnecessarily. For this, you can build your query with all the data being inserted and then execute it, like so:

<?php
$arr = json_decode($json_obj,true);//decode object
$query = "INSERT into stations (station_id, name, system_id, max_landing_pad_size, distance_to_star, faction, government, allegiance, state, type, has_blackmarket, has_commodities, has_refuel, has_repair, has_rearm, has_outfitting, has_shipyard) values ";
foreach($arr as $ar) {    
    $query .= "($ar['id'],$ar['name'],$ar['system_id'],
                $ar['max_landing_pad_size'],$ar['distance_to_star'],$ar['faction'],
                $ar['government'],$ar['allegiance'],$ar['state'],
                $ar['type'],$ar['has_blackmarket'],$ar['has_commodities'],
                $ar['has_refuel'],$ar['has_repair'],$ar['has_rearm'],
                $ar['has_outfitting'],$ar['has_shipyard']),";
}
$query = rtrim(",",$query);
if(!mysql_query($query,$con)) //$con is mysql connection object
{
    die('Error : ' . mysql_error());
}

In case you want to know, your original code doesn't work because you're just grabbing the first row from the json ($arr[0]). You need to loop through the data to get all the rows.

share

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.