Decode Json data Array and insert in to mysql

Question

This question already may asked here but i tried search couldn't find it I have Json data like below

{"CityInfo":[{"CityCode":"5599","Name":"DRUSKININKAI"},{"CityCode":"2003","Name":"KAUNAS"},{"CityCode":"2573","Name":"KLAIPEDA"},{"CityCode":"1692","Name":"VILNIUS"}],"StartTime":"2016-11-05 07:20:34","EndTime":"2016-11-05 07:20:34"}

i tried to extract these using php and inserted to mysql db. but its not work for me as i beginner in coding please help to solve this

Below I tried

$jsondata = file_get_contents($jsonFile);
$data = json_decode($jsondata, true);
echo $data['Name'];
echo $data['CityCode'];
$db->save($data, "tableName"); // i found a php app to insert json to db

Can you post what you've tried so far?

Rwd
– Rwd

2016-11-05 19:28:52 +00:00
Commented Nov 5, 2016 at 19:28 — Rwd
– Rwd, Commented Nov 5, 2016 at 19:28

Kiran Muralee · Accepted Answer · 2016-11-05 21:11:03Z

9

There are some problems in your php code. The code $data = json_decode($jsondata, true); indeed convert your json data into PHP array. But if you need to extract the datas that you need to insert into your table, then you need to do like

$array_data = $data['CityInfo'];

Now this $array_data contains the array of data that needs to be inserted into your tables.You can continue with

$db->save($array_data, "tableName");

or you could manually insert each row using a PHP MySQLi and forEach loop like

$conn = new mysqli($servername, $username, $password, $dbname);

foreach ($array_data as $row) {
    $sql = "INSERT INTO cityinfo (CityCode, Name) VALUES ('" . $row["CityCode"] . "', '" . $row["Name"] . "')";
    $conn->query($sql);
}

Full example

<?php

$jsonFile="CityInfo.json";
$jsondata = file_get_contents($jsonFile);
$data = json_decode($jsondata, true);

$array_data = $data['CityInfo'];

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "yourDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

foreach ($array_data as $row) {
    $sql = "INSERT INTO cityinfo (CityCode, Name) VALUES ('" . $row["CityCode"] . "', '" . $row["Name"] . "')";
    $conn->query($sql);
}

$conn->close();
?>

edited Nov 5, 2016 at 21:11

answered Nov 5, 2016 at 20:56

Kiran Muralee

2,0802 gold badges19 silver badges28 bronze badges

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

3 Comments

Feroza Over a year ago

Nice Explanation Kiran! Thanks

Kiran Muralee Over a year ago

Glad to know :)

soria Over a year ago

How do I change it to SELECT rather than INSERT?

Collectives™ on Stack Overflow

Decode Json data Array and insert in to mysql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related