json数据mysql数据库,将数据从JSON放入MySQL数据库

I have a question about JSON and MySQL Database.

What I want is that I can load the data from a JSON-file into my fields in my database.

My JSON-file looks like this:

{

"wijken": {

"11": {

"id": "kml_1",

"fid": "0",

"wijziging": "Ja",

"nieuwnr": "11",

"naam": "Noordoost",

"wijk": "Kanaaldorpen en -zone",

"wijknr": "11",

"objectid": "1",

"area": "0",

"len": "0"

},

"12": {

"id": "kml_2",

"fid": "1",

"wijziging": "Ja",

"nieuwnr": "12",

"naam": "Noordoost",

"wijk": "Oostakker",

"wijknr": "12",

"objectid": "2",

"area": "0",

"len": "0"

}

}

}

And I have a database with a table "wijken" with fields:

ID / FID / WIJZIGING / NIEUWNR / NAAM / WIJK / WIJKNR / OBJECTID /

AREA / LEN

Now I want that all the data from the json-file comes in that table. (php + javascript)

Can someone help me start? (Or give a tutorial of some good search terms maybe)

Thanks in advance!

解决方案

First you need to load the file from the filesystem

$json_string = file_get_contents('some/path/to/file.json');

Then you can turn the json string into a php array using json_decode

$data = json_decode($json_string, true);

At this point you will be able to access the data to go into the wijken table with $data['wijken'].

In order to insert this data into a mysql database, you will need to use one of the php mysql extensions, either mysqli or PDO.

I will use mysqli for this example:

// first create a connection to your database

$mysqli = new mysqli('localhost', 'user', 'password', 'database_name');

// this insert query defines the table, and columns you want to update

$query = <<

INSERT INTO wijken ('ID', 'FID', 'WIJZIGING', 'NIEUWNR', 'NAAM', 'WIJK', 'WIJKNR', 'OBJECTID', 'AREA', 'LEN')

VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

SQL;

$stmt = $mysqli->prepare($query);

// for each of the 'rows' of data in the json we parsed, we will insert each value

// into it's corresponding column in the database, and we are doing this using prepared

// statements.

foreach ($data['wijken'] as $key => $value) {

$stmt->bind_param(

// the types of the data we are about to insert: s = string, i = int

'sissssiiii',

$value['id'],

$value['fid'],

$value['wijziging'],

$value['nieuwnr'],

$value['naam'],

$value['wijk'],

$value['wijknr'],

$value['objectid'],

$value['area'],

$value['len']

);

$stmt->execute();

}

$stmt->close();

// close the connection to the database

$mysqli->close();