到MySQL的JSON / Array

Question

我从来没有使用过JSON或多维数组，我完全失去了atm.我试图将coinwarz中的api数据写入MySQL..。

我得到的是：

{"Success":true,"Message":"999 api calls remaining.","Data":[{"CoinName":"Tagcoin","CoinTag":"TAG","Algorithm":"Scrypt","Difficulty":48.8082576,"BlockReward":30,"BlockCount":35404,"ProfitRatio":8152.1820299065312,"AvgProfitRatio":5538.592714864395,"Exchange":"Cryptsy","ExchangeRate":0.0015114,"ExchangeVolume":9100.58740501,"IsBlockExplorerOnline":true,"IsExchangeOnline":true,"Message":"Warning: The blockchain for this coin is currently having issues. Waiting on a fix from the coin development team.","BlockTimeInSeconds":240,"HealthStatus":"Unhealthy"},{"CoinName":"Lottocoin","CoinTag":"LOT","Algorithm":"Scrypt","Difficulty":12.15941137,"BlockReward":32896,"BlockCount":136845,"ProfitRatio":6884.8588238209768,"AvgProfitRatio":4448.5624688234475,"Exchange":"Cryptsy","ExchangeRate":2.9E-07,"ExchangeVolume":382804480.23628891,"IsBlockExplorerOnline":true,"IsExchangeOnline":true,"Message":"","BlockTimeInSeconds":30,"HealthStatus":"Healthy"},{"CoinName":"RonPaulcoin","CoinTag":"RPC","Algorithm":"Scrypt","Difficulty":30.33885125,"BlockReward":1,"BlockCount":15421,"ProfitRatio":6189.8644790289609,"AvgProfitRatio":8893.1681194248831,"Exchange":"Cryptsy","ExchangeRate":0.0214,"ExchangeVolume":1719.73666494,"IsBlockExplorerOnline":true,"IsExchangeOnline":true,"Message":"","BlockTimeInSeconds":120,"HealthStatus":"Healthy"},{"CoinName":"Franko","CoinTag":"FRK","Algorithm":"Scrypt","Difficulty":1.00168131,"BlockReward":0.25,"BlockCount":406926,"ProfitRatio":6176.3105781107906,"AvgProfitRatio":2511.7635333807175,"Exchange":"Cryptsy","ExchangeRate":0.00282002,"ExchangeVolume":17011.99409075,"IsBlockExplorerOnline":true,"IsExchangeOnline":true,"Message":"","BlockTimeInSeconds":30,"HealthStatus":"Healthy"},

我解码了($json_array = json_decode($json_source，true);)并得到了以下内容：

Array ( [Success] => 1 
[Message] => 1,000 api calls remaining. 
[Data] => Array ( 

[0] => Array ( [CoinName] => Tagcoin 
[CoinTag] => TAG 
[Algorithm] => Scrypt 
[Difficulty] => 20.62668283 
[BlockReward] => 30 
[BlockCount] => 35339 
[ProfitRatio] => 19310.667769105 
[AvgProfitRatio] => 6361.5043045259 
[Exchange] => Cryptsy 
[ExchangeRate] => 0.001513 
[ExchangeVolume] => 7711.03038846 
[IsBlockExplorerOnline] => 1 
[IsExchangeOnline] => 1 
[Message] => Warning: The blockchain for this coin is currently having issues. Waiting on a fix from the coin development team. 
[BlockTimeInSeconds] => 240 
[HealthStatus] => Unhealthy ) 

[1] => Array ( 
[CoinName] => Dogecoin 
[CoinTag] => DOGE 
[Algorithm] => Scrypt 
[Difficulty] => 1055.45317084 
[BlockReward] => 500000 
[BlockCount] => 76150 
[ProfitRatio] => 7025.6132683628 
[AvgProfitRatio] => 5524.6490817022 
[Exchange] => Vircurex 
[ExchangeRate] => 1.69E-6 
[ExchangeVolume] => 100375791.08525 
[IsBlockExplorerOnline] => 1 
[IsExchangeOnline] => 1 
[Message] => 
[BlockTimeInSeconds] => 60 
[HealthStatus] => Healthy ) 

[2] => Array ( 
[CoinName] => BBQCoin 
[CoinTag] => BQC 
[Algorithm] => Scrypt 
[Difficulty] => 2.04374031 
[BlockReward] => 42 
[BlockCount] => 742772 
[ProfitRatio] => 5980.0470554874 
[AvgProfitRatio] => 2822.26794798 
[Exchange] => Cryptsy 
[ExchangeRate] => 3.316E-5 
[ExchangeVolume] => 81528.3775313 
[IsBlockExplorerOnline] => 1 
[IsExchangeOnline] => 1 
[Message] => 
[BlockTimeInSeconds] => 60 
[HealthStatus] => Healthy ) 

因此，如果我想这样做，请将其保存到mysql中，然后插入

• $json_array
• 。
• 。
• $json_array1
• 。
• 。

以此类推。

有更好的办法吗？

是这样做的：

<?php

$db_host                                = "localhost";
$db_user                                = "test";
$db_pw                                  = "test";
$db_name                                = "test";

$db_connect = mysql_connect($db_host,$db_user,$db_pw); // MYSQL-Connection
mysql_select_db($db_name, $db_connect); // DB-Connection


$url = 'http://127.0.0.1/jsontestdaten.json';
$json_source = file_get_contents($url);

// DUMP JSON
//$data = json_decode($JSON);
//var_dump($data);

$json_array = json_decode($json_source, true);


if(is_array($json_array)){
echo 'i am in array ';
    $sql = "INSERT INTO data (id, coinname, cointag, algorithm, difficulty, blockreward, blockcount, profitratio, avgprofitratio, exchange, exchangerate, exchangevolume, isblockexploreronline, isexchangeonline, message, blocktimeinsecon
        $valuesArr = array();
                foreach($json_array as $row){
                        $coinname               = $row['CoinName'];
                        $cointag                = $row['CoinTag'];
                        $algorithm              = $row['Algorithm'];
                        $difficulty             = $row['Difficulty'];
                        $blockreward            = $row['BlockReward'];
                        $blockcount             = $row['BlockCount'];
                        $profitratio            = $row['ProfitRatio'];
                        $avgprofitratio         = $row['AvgProfitRatio'];
                        $exchange               = $row['Exchange'];
                        $exchangerate           = $row['ExchangeRate'];
                        $exchangevolume         = $row['ExchangeVolume'];
                        $isblockexploreronline  = $row['IsBlockExplorerOnline'];
                        $isexchangeonline       = $row['IsExchangeOnline'];
                        $message                = $row['Message'];
                        $blocktimeinseconds     = $row['BlockTimeInSeconds'];
                        $healthstatus           = $row['HealthStatus'];

$valuesArr[] = "('', '$coinname', '$algorithm','$difficulty','$blockreward','$blockcount','$profitratio','$avgprofitratio','$exchange','$exchangerate','$exchangevolume', '$isblockexploreronline','$message','$blocktimeinseconds','$health
                        }
$sql .= implode(',', $valuesArr);


$update = mysql_query($sql);
}

?>

但是var_dump($sql)；显示：

string(443) "INSERT INTO data (id, coinname, cointag, algorithm, difficulty, blockreward, blockcount, profitratio, avgprofitratio, exchange, exchangerate, exchangevolume, isblockexploreronline, isexchangeonline, message, blocktimeinseconds, healthstatus, datetime) values ('', '', '','','','','','','','','', '','','','', NOW()),('', '9', '9','9','9','9','9','9','9','9','9', '9','9','9','9', NOW()),('', '', '','','','','','','','','', '','','','', NOW())"

我犯了什么错？

Answer 1

你走在正确的轨道上。

foreach($json_array as $apiData){
  //mysql mapping
}

mysql映射在本例中是将不同值(例如：$apiDataCoinName)映射到特定行的方式。

为了获得更大的结果，这可能会导致较长的加载时间。所以也许你应该看看，不要一次做太多的事。大多数API都请您只获取您在任务中真正需要的数据。如果这些是一般的游戏数据库信息，做一个一次性导入脚本，以存储他们。那么不管花多长时间。因为您作为管理员只插入它们一次。