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
– Mayank Vadiya

2016-01-18 05:34:14 +00:00
Commented Jan 18, 2016 at 5:34 — Mayank Vadiya
– Mayank Vadiya, Commented Jan 18, 2016 at 5:34
should work with or without the . operator right?

DVCITIS
– DVCITIS

2016-01-18 05:36:16 +00:00
Commented Jan 18, 2016 at 5:36 — DVCITIS
– DVCITIS, Commented Jan 18, 2016 at 5:36

Marinus · Accepted Answer · 2016-01-18 05:36:53Z

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, 2016 at 5:36

Marinus

5592 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marinus Over a year ago

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.

devpro Over a year ago

@Marinus: you missed the multi query.. but nice answer... earn my vote.

devpro · Accepted Answer · 2016-01-18 05:55:03Z

1

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";
}

answered Jan 18, 2016 at 5:55

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Comments

Crackers · Accepted Answer · 2016-01-18 05:55:35Z

0

Remove the double quotes and concatinator wrapping your Values.

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

answered Jan 18, 2016 at 5:55

Crackers

11 bronze badge

Collectives™ on Stack Overflow

Load data from JSON file to MYSQL database

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related