从 JSON 数据自动生成 MySQL 表和列

Question

我正在尝试读取从API检索到的JSON。 我想获取该数据并自动创建存储接收信息所需的表和列。 我不想静态地制作列，因为我自己运行的每个硬币可能都有或多或少的信息。

我下面的代码目前只会创建 3 列(id、name 和股票代码(并将数据插入 id 和 name.
它不会通过第一个信息数组.
其次，我在'$qi .= "'" . mysqli_real_escape_string($conn, $value) . "',";'我不知道如何解决Warning: mysqli_real_escape_string() expects parameter 2 to be string, array given。

原始代码

$apiurl = "https://api.coingecko.com/api/v3/coins/infocoin/tickers";
$json = file_get_contents($apiurl);
JSON_to_table($json);
function JSON_to_table($json, $tblName = "New_JSON_table_"){
$conn = mysqli_connect($GLOBALS["db"]["host"], $GLOBALS["db"]["user"], $GLOBALS["db"]["pass"], $GLOBALS["db"]["name"]);
$j_obj = json_decode($json, true);
//$j_obj2 = $j_obj["tickers"];
//var_dump($j_obj);
print_r ($j_obj);
if(!mysqli_num_rows( mysqli_query($conn,"SHOW TABLES LIKE '" . $tblName . "'"))){ 
$cq = "CREATE TABLE ". $tblName ." (
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,";
foreach($j_obj as $j_arr_key => $value){
$cq .= $j_arr_key . " VARCHAR(256),";

}
$cq = substr_replace($cq,"",-1);
$cq .= ")";
mysqli_query($conn,$cq) or die(mysqli_error($conn));
}
$qi = "REPLACE INTO $tblName (";
reset($j_obj);
foreach($j_obj as $j_arr_key => $value){
$qi .= $j_arr_key . ",";
}
$qi = substr_replace($qi,"",-1);
$qi .= ") VALUES (";
next($j_obj);
foreach($j_obj as $j_arr_key => $value){
$qi .= "'" . mysqli_real_escape_string($conn, $value) . "',";
$qi .= "'" .$value . "',";
}
$qi = substr_replace($qi,"",-1);
$qi .= ")";
$result = mysqli_query($conn,$qi) or die(mysqli_error($conn));
return true;

给出重复列名错误的代码

$apiurl = "https://api.coingecko.com/api/v3/coins/infocoin/tickers";
$json = file_get_contents($apiurl);
JSON_to_table($json);
function JSON_to_table($json, $tblName = "New_JSON_table_"){
$conn = mysqli_connect($GLOBALS["db"]["host"], $GLOBALS["db"]["user"], $GLOBALS["db"]["pass"], $GLOBALS["db"]["name"]);
$j_obj = json_decode($json, true);
//$j_obj2 = $j_obj["tickers"];
//var_dump($j_obj);
print_r ($j_obj);
if(!mysqli_num_rows( mysqli_query($conn,"SHOW TABLES LIKE '" . $tblName . "'"))){ 
$cq = "CREATE TABLE ". $tblName ." (
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,";
foreach($j_obj as $j_arr_key => $value){
$cq .= $j_arr_key . " VARCHAR(256),";

}
next($j_obj);
foreach($j_obj as $j_arr_key => $value){
$cq .= $j_arr_key . " VARCHAR(256),";

}
$cq = substr_replace($cq,"",-1);
$cq .= ")";
mysqli_query($conn,$cq) or die(mysqli_error($conn));
}
$qi = "REPLACE INTO $tblName (";
reset($j_obj);
foreach($j_obj as $j_arr_key => $value){
$qi .= $j_arr_key . ",";
}
$qi = substr_replace($qi,"",-1);
$qi .= ") VALUES (";
next($j_obj);
foreach($j_obj as $j_arr_key => $value){
$qi .= "'" . mysqli_real_escape_string($conn, $value) . "',";
$qi .= "'" .$value . "',";
}
$qi = substr_replace($qi,"",-1);
$qi .= ")";
$result = mysqli_query($conn,$qi) or die(mysqli_error($conn));
return true;

我以多种方式调整了代码。 我终于让它将第二个数组中的所有数据插入到"代码"列中，但我不希望这样。 我已经得到了几乎添加剩余列名称的代码，但它随后给了我一个错误Duplicate column name 'name'

从下面的评论中编辑的代码

$apiurl = "https://api.coingecko.com/api/v3/coins/infocoin/tickers";
$json = file_get_contents($apiurl);

JSON_to_table($json);

function JSON_to_table($json, $tblName = "New_JSON_table_"){
$conn = mysqli_connect($GLOBALS["db"]["host"], $GLOBALS["db"]["user"], $GLOBALS["db"]["pass"], $GLOBALS["db"]["name"]);
$j_obj = json_decode($json, true);
//$j_obj2 = $j_obj["tickers"];
//var_dump($j_obj);
print_r ($j_obj);
if(!mysqli_num_rows( mysqli_query($conn,"SHOW TABLES LIKE '" . $tblName . "'"))){ 
$cq = "CREATE TABLE ". $tblName ." (
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,";
foreach($j_obj["tickers"][0] as $j_arr_key => $value){
$cq .= $j_arr_key . " VARCHAR(256),";

}
$cq = substr_replace($cq,"",-1);
$cq .= ")";
mysqli_query($conn,$cq) or die(mysqli_error($conn));
}

$qi = "REPLACE INTO $tblName (";
reset($j_obj["tickers"][0]);
foreach($j_obj["tickers"][0] as $j_arr_key => $value){
$qi .= $j_arr_key . ",";
}
$qi = substr_replace($qi,"",-1);
$qi .= ") VALUES (";
next($j_obj);
foreach($j_obj["tickers"][0] as $j_arr_key => $value){
$qi .= "'" . mysqli_real_escape_string($conn, $value) . "',";
$qi .= "'" .$value . "',";
}
$qi = substr_replace($qi,"",-1);
$qi .= ")";
$result = mysqli_query($conn,$qi) or die(mysqli_error($conn));

return true;

更新代码中的错误

Warning: mysqli_real_escape_string() expects parameter 2 to be string, array given in /var/www/html/exp/coingecko_testmarket.php on line 86

Notice: Array to string conversion in /var/www/html/exp/coingecko_testmarket.php on line 87

Warning: mysqli_real_escape_string() expects parameter 2 to be string, array given in /var/www/html/exp/coingecko_testmarket.php on line 86

Notice: Array to string conversion in /var/www/html/exp/coingecko_testmarket.php on line 87

Warning: mysqli_real_escape_string() expects parameter 2 to be string, array given in /var/www/html/exp/coingecko_testmarket.php on line 86

Notice: Array to string conversion in /var/www/html/exp/coingecko_testmarket.php on line 87
Column count doesn't match value count at row 1

Answer 1

问题是，此 json 中的数据存储为多级结构(包含另一个对象的对象(，您无法将其按原样存储在 SQL 数据库中。

您应该为"股票代码数组"的第一个项目生成"创建表"查询$j_obj['tickers'][0]然后为该数组的每个元素创建"插入"查询，但您仍然有不是简单字符串的值，因此您需要转换它。