所以我有一个json文件,如何将其插入mysql中? [英] So i have a json file how i can insert it in mysql?
本文介绍了所以我有一个json文件,如何将其插入mysql中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找一个php脚本,以每小时从json文件更新mysql.
I'm looking to make a php script to update the mysql every hour from a json file.
api是
我如何从json复制这些内容并将其放入mysql中?
How i can copy this things from json and put them in the mysql?
我的意思是这样
"AK-47 | Aquamarine Revenge (Battle-Scarred)": {
"last_updated": 1436569230,
"quantity": 34,
"value": 2268
},
"AK-47 | Aquamarine Revenge (Factory New)": {
"last_updated": 1436569230,
"quantity": 21,
"value": 9386
},
"AK-47 | Aquamarine Revenge (Field-Tested)": {
"last_updated": 1436569230,
"quantity": 55,
"value": 4968
},
"AK-47 | Aquamarine Revenge (Minimal Wear)": {
"last_updated": 1436569230,
"quantity": 40,
"value": 6018
},
"AK-47 | Aquamarine Revenge (Well-Worn)": {
"last_updated": 1436569230,
"quantity": 40,
"value": 3597
},
"AK-47 | Black Laminate (Battle-Scarred)": {
"last_updated": 1436569230,
"quantity": 50,
"value": 345
},
"AK-47 | Black Laminate (Factory New)": {
"last_updated": 1436569230,
"quantity": 8,
"value": 8593
},
"AK-47 | Black Laminate (Field-Tested)": {
"last_updated": 1436569230,
"quantity": 141,
"value": 308
},
我希望这样进入mysql
I want this to go in the mysql like that
这是我到目前为止所得到的:
This is what I've got so far:
<?php
$json = file_get_contents('http://backpack.tf/api/IGetMarketPrices/v1/?key=51f7eb704bd7b8231900000c&appid=730&format=json');
$obj = json_decode($json,true);
//Database Connection
require_once 'db.php';
/* insert data into DB */
foreach($obj as $item) {
mysql_query("INSERT INTO `cyberst_CSGO`.`items` ( cost, lastupdate)
VALUES ('".$item['value']."'', '".$item['last_updated']."')");
}
//database connection close
mysql_close($con);
//}
?>
推荐答案
这段代码生成一个由制表符分隔的临时文件,并使用LOAD DATA IN FILE
将该文件插入数据库,然后删除该临时文件.
This code generates a temporary tab-delimited file and uses LOAD DATA IN FILE
to insert that file into the database then deletes the temporary file.
- 使用 MySQLi -现已在PHP中弃用mysql函数
cost = value * 0.01
.- 将大量数据加载到MySQL的最快方法是使用
LOAD DATA INFILE
- 我还没有测试数据库的导入,但是我已经使用了很多次这种方法,并且效果很好.
- 您可能需要先使用
mysqli_real_escape_string()
或$conn->real_escape_string()
转义$name $cost $row
.我没有检查您所有的数据.
- Use MySQLi - mysql functions are deprecated in PHP now
-
cost = value * 0.01
. - Fastest way to load a bunch of data into MySQL is with
LOAD DATA INFILE
- I haven't tested the database import but I've used this method many times and it works quite well.
- You may need to escape
$name $cost $row
withmysqli_real_escape_string()
or$conn->real_escape_string()
first. I didn't check all your data.
这是PHP:
$json = file_get_contents('http://backpack.tf/api/IGetMarketPrices/v1/?key=51f7eb704bd7b8231900000c&appid=730&format=json');
$obj = json_decode($json,true);
//open a temporary file to hold data
$path = str_replace('\\','/',realpath(dirname(__FILE__)));
$filename = time().'tmp.csv';
$filename = $path.'/'.$filename;
$tmpfile = fopen($filename,'w+');
//data to loop through and write to temporary file
$loopme = $obj['response']['items'];
foreach($loopme as $name=>$row) {
//i'm guessing this is what cost is
$cost = $row['value'] * 0.01;
//write to our tmp file
fwrite($tmpfile,"$name\t$cost\t$row[last_updated]\n");
}
// this is the fastest way to load large amounts of data into MySQL
$sql = "LOAD DATA INFILE '$filename'
INTO TABLE `cyberst_CSGO`.`items`
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'";
//run your mysql query
//delete temporary file
unlink($filename);
这篇关于所以我有一个json文件,如何将其插入mysql中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文