Get values from jSon array and do sql insert

Question

Here is my array;

    $json = '
{
    "1":{
        "Device_ID":"a9a3346be4375a92",
        "Date":"2012-05-31",
        "Time":"15:22:59",
        "Latitude":"51.4972912",
        "Longitude":"0.1108178"
    },
    "2":{
        "Device_ID":"a9a3346be4375a92",
        "Date":"2012-05-31",
        "Time":"15:52:07",
        "Latitude":"51.4988357",
        "Longitude":"0.1222859"
    }
}';

It's decoded like this: $array = json_decode($json);

I am trying to now insert into a database all the $items in the above array; so i would like to store in a single record all the data within 1, and in a second record all the values within 2.

The idea is i will never know the size of the array so it could have 100 $items.

How would this be done?

You should use json_decode($json, true) to turn it into an associative array and then loop through the $array with a foreach pulling out the values you want and running your sql accordingly — Jon Rubins
– Jon Rubins, Commented May 31, 2012 at 19:50

user1403947 · Accepted Answer · 2012-05-31 20:03:31Z

4

Try something like this:

$item_array = json_decode($json, true);
$insert_query = "INSERT INTO items (column1,column3) VALUES ";
$i = 0;
$array_count = count($item_array);

foreach($item_array as $item) {
 $end = ($i == $array_count) ? ';' : ',';
 $insert_query .= "('".$item['date']."','".$item['device_id']."')" . $end;
 $i ++;
}

mysql_query($insert_query);

The resulting query will look something like:

INSERT INTO items (column1,column3) VALUES ('asdf',123),('asdfe',1234);

answered May 31, 2012 at 20:03

user1403947

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

4 Comments

Jon Rubins Over a year ago

It depends what SQL he's using though right? I don't think that INSERT syntax works for every SQL

Xavier Over a year ago

Thanks for this above it seems to have gotten me to a reasonable state however it doesn't seem to be posting i'll try and figure it out, it's mysql btw.

Christopher Over a year ago

when I use your code, it works perfectly fine, but it doesn't add the semi colon, it adds a comma, but then I just remove the comma and add a semicolon

EleetGeek Over a year ago

To add a semicolon modify: $end = ($i == $array_count-1) ? ';' : ',';

Collectives™ on Stack Overflow

Get values from jSon array and do sql insert

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related