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

Question

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

我下面的代码目前只会创建 3 列(id、名称和股票代码)并将数据插入到 id 和 name 中。
它不会超过第一组信息。
其次，我在 '$qi .= "'"处收到错误。 mysqli_real_escape_string($conn, $value) 。 “'，”；'警告:mysqli_real_escape_string() 期望参数 2 为字符串，给定数组，我不知道如何修复。

原始代码

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

我通过多种方式调整了代码。我终于将第二个数组中的所有数据插入到“代码”列中，但我不希望这样。我已经获得了几乎添加剩余列名称的代码，但它随后给了我一个错误重复列名称'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 数据库中。

您应该为“tickers 数组 $j_obj['tickers'][0] 中的第一项生成“创建表”查询，然后为该数组的每个元素创建“插入”查询，但是您仍然有一些不是简单字符串的值，因此您需要对其进行转换。