将数据从JSON放入MySQL数据库 [英] Put data from JSON in MySQL Database
问题描述
我有一个关于JSON和MySQL数据库的问题。
我想要的是,我可以将数据从JSON文件加载到我的数据库中的字段。
我的JSON文件如下所示:
{
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
}
}
}
我有一个数据库,其中有一个表wijken,其中包含以下字段:
ID / FID / WIJZIGING / NIEUWNR / NAAM / WIJK / WIJKNR / OBJECTID /
AREA / LEN
现在我想要json文件中的所有数据在该表中。 (php + javascript)
有人可以帮我开始吗?
/ div>
首先需要从文件系统加载文件
$ json_string = file_get_contents('some / path / to / file.json');
然后你可以使用 json_decode
$ data = json_decode($ json_string,true);
此时,您将可以访问数据进入 wijken <
为了将此数据插入到mysql数据库中,请将 $ data ['wijken' ,您将需要使用php mysql扩展之一, mysqli 或< a href =http://php.net/manual/en/ref.pdo-mysql.php =nofollow> PDO 。
我将使用mysqli的示例:
//首先创建一个到数据库的连接
$ mysqli = new mysqli('localhost','user','password','database_name');
//此插入查询定义表和要更新的列
$ query $<<<< SQL
INSERT INTO wijken('ID' FID','WIJZIGING','NIEUWNR','NAAM','WIJK','WIJKNR','OBJECTID','AREA','LEN')
VALUES(?,?,?, ,?,?,?,?,?)
SQL;
$ stmt = $ mysqli-> prepare($ query);
//对于我们解析的json中的每一行数据,我们将每个值
//插入到数据库的相应列中,我们这样做准备
//语句。
foreach($ data ['wijken'] as $ key => $ value){
$ stmt-> bind_param(
//我们要插入的数据类型:s = string,i = int
'sissssiiii',
$ value ['id'],
$ value ['fid'],
$ value ['wijziging' ],
$ value ['wijk'],
$ value ['wijknr'],
$ value ['nieuwnr'],
$ value ['naam'
$ value ['objectid'],
$ value ['area'],
$ value ['len']
);
$ stmt-> execute();
}
$ stmt-> close();
//关闭与数据库的连接
$ mysqli-> close();
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 = <<<SQL
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();
这篇关于将数据从JSON放入MySQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!