CODEWITHSUNDEEP
phpmysqljson
DVCITIS
DVCITIS

Reputation: 1037

Load data from JSON file to MYSQL database

I am trying to store two fields from the JSON data found here in a mysql database. First I create PHP arrays for the data I want using: 

$o = file_get_contents("vixData.json");
$o = json_decode($o);
$date = []; 
$close = []; 
$set = $o->dataset->data;
foreach($set as $pos)
{
array_push($date,$pos[0]);
array_push($close,$pos[4]);
}

Works fine. Now I am trying to adapt This question on inserting multiple rows via a php array. First I implode my arrays:

$date = implode(",", $date);
$close = implode(",", $close);

Then try to insert to the db using:

  $sql = "INSERT INTO vix (date,close) VALUES (".$date.",".$close.")";

    if (mysqli_multi_query($dbc, $sql)) {
        echo "VIX Load Successful";
    } else {
        echo "VIX Load Error";
    }

I'm seeing my load error. There is no database connection issue because $dbc is used earlier in my script. Trying to debug; is my SQL INSERT statement valid? Can anyone see the issue?

Kind regards,

Upvotes: 1

Views: 2234

Answers (3)

Crackers
Crackers

Reputation: 1

Remove the double quotes and concatinator wrapping your Values.

$sql = "INSERT INTO vix (date,close) VALUES ('$date','$close')";

Upvotes: 0

devpro
devpro

Reputation: 16117

There are two issues in your code.

  1. As mentioned other mate you need to use quotes for Date string.
  2. Second you can not use mysqli_multi_query() as like that.

Modified Code:

You can use multiple INSERT Statement as like that:

$o = file_get_contents("vixData.json");
$o = json_decode($o);
$date = []; 
$close = []; 
$set = $o->dataset->data;
foreach($set as $pos)
{
    array_push($date,$pos[0]);
    array_push($close,$pos[4]);
}

$sql = "";
foreach ($date as $key => $value) {
    $sql .= "INSERT INTO vix (date,close) VALUES ('".$value."','".$close[$key]."'); ";
}

if (mysqli_multi_query($dbc, $sql)) {
    echo "VIX Load Successful";
} else {
    echo "VIX Load Error";
}

Upvotes: 1

Marinus
Marinus

Reputation: 551

Just looking at it quickly, it seems your values are not wrapped in quotes in your SQL. Try this:

$sql = "INSERT INTO vix (date,close) VALUES ('".$date."','".$close."')";

Removing the concat operator (.) will result in the same.

Upvotes: 2

Related Questions