Load data from JSON file to MYSQL database

Question

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,

remove . operator beside variable in query – Mayank Vadiya Jan 18 '16 at 05:34 — Mayank Vadiya, Jan 18 '16 at 05:34
should work with or without the . operator right? – DVCITIS Jan 18 '16 at 05:36 — DVCITIS, Jan 18 '16 at 05:36

score 2 · Answer 1 · answered Jan 18 '16 at 05:36

2

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.

answered Jan 18 '16 at 05:36

Marinus

521
2
15

Awesome! It's worth noting that if $date or $close have an apostrophe, your query will probably break too. Make sure to escape your input. – Marinus Jan 18 '16 at 05:57
@Marinus: you missed the multi query.. but nice answer... earn my vote. – devpro Jan 18 '16 at 06:00

score 1 · Accepted Answer · answered Jan 18 '16 at 05:55

There are two issues in your code.

As mentioned other mate you need to use quotes for Date string.
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";
}

score 0 · Answer 3 · answered Jan 18 '16 at 05:55

0

Remove the double quotes and concatinator wrapping your Values.

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

answered Jan 18 '16 at 05:55

Crackers

1
1

Load data from JSON file to MYSQL database

3 Answers3