所以我有一个json文件如何在mysql中插入它？

Question

I'm looking to make a php script to update the mysql every hour from a json file.

the api is that

http://backpack.tf/api/IGetMarketPrices/v1/?key=51f7eb704bd7b8231900000c&appid=730&format=json

How i can copy this things from json and put them in the mysql?

I mean something like that

"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
},

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);

//}
?>

Answer 1

EDIT: 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.

1. Use MySQLi - mysql functions are deprecated in PHP now
2. EDIT: cost = value * 0.01.
3. Fastest way to load a bunch of data into MySQL is with LOAD DATA INFILE
4. I haven't tested the database import but I've used this method many times and it works quite well.
5. You may need to escape $name $cost $row with mysqli_real_escape_string() or $conn->real_escape_string() first. I didn't check all your data.

Here's the 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]
");
}

// 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 '
'";

//run your mysql query

//delete temporary file
unlink($filename);