php - PHP json_decode 从 API 到 sql：只导入不包含空值的对象

Question

尽管有很多与此类似的主题，但它们并没有解决我遇到的问题，因此感谢您进行一些澄清。

我正在使用来自各种 API 的一些数据构建一个数据库，来自这些 API 的数据采用 JSON 格式（有些是对象，有些是数组）。

我从具有更简单格式的对象开始，所以我编写了一个小的 PHP 脚本来获取数据并将其插入到指定的数据库表中。

/*CONECT TO DB - TESTING ON LOCAL MACHINE*/ 
$connect = mysqli_connect("localhost", "root", "", "test"); //Connect PHP to MySQL Database
$query = '';

/*CLEAR/TRUNCATE TABLE TO REFRESH ALL CONTENTS FROM API*/
$ins_qry = "TRUNCATE TABLE Derivatives";
mysqli_query($connect,$ins_qry);

$url = "https://api.xxxxxxxx";
$content = file_get_contents($url);
$jsonData = json_decode($content, true);

foreach ( $jsonData as $id=>$row ) {
    $insertPairs = array();
    foreach ($row as $key=>$val) {
      $insertPairs[addslashes($key)] = addslashes($val);
    }
    $insertKeys = '`' . implode('`,`', array_keys($insertPairs)) . '`';
    $insertVals = '"' . implode('","', array_values($insertPairs)) . '"';   

    /*JUST FOR CHECKING RESULTS ON PAGE*/
    echo "INSERT INTO Derivatives ({$insertKeys}) VALUES ({$insertVals});" . "\n";

/*THE QUERY*/
$query = "INSERT INTO Derivatives ({$insertKeys}) VALUES ({$insertVals});" . "\n";
if (mysqli_multi_query($connect, $query)) ;

}

mysqli_close($connect);

JSON数据是这样的：

[
 {
    "market": "OKEx (Futures)",
    "symbol": "BTC-USD-SWAP",
    "coin_id": "bitcoin",
    "index_id": "OkexSwap-BTC",
    "price": "9520.5",
    "price_percentage_change_24h": 4.801690829232852,
    "contract_type": "perpetual",
    "index": 9526.418,
    "basis": 0.06216060080878105,
    "spread": null,
    "funding_rate": 0.011057,
    "open_interest": 116495100,
    "volume_24h": 466882900,
    "last_traded_at": 1592306177,
    "expired_at": null
  },
.
.
.
]

请注意，某些值在随机位置为空，例如，在“spread”和“expired_at”上方的对象中为空，在下一个对象上它可以是其他对象，而在其他一些对象上所有值都存在。

在我的代码中的 ECHO 上并按照上面的示例，我得到如下信息：

INSERT INTO Derivatives 
    (`market`,`symbol`,`coin_id`,`index_id`,`price`,
    `price_percentage_change_24h`,`contract_type`,
    `index`,`basis`,`spread`,`funding_rate`,
    `open_interest`,`volume_24h`,`last_traded_at`,
    `expired_at`) 
VALUES ("OKEx (Futures)","BTC-USD-SWAP","bitcoin","OkexSwap-BTC","9504.2",
    "0.86279170955863","perpetual","9506.228","0.021337934807759",
    "","-0.004262","111364600","411804300","1592331559","");

（不要为一些价值变化而烦恼，因为它们在不同时间获取，这只是一个例子）

请注意，为 null 的值现在转换为 ""。

检查数据库本身的表时发生的情况是，在预期的近 500 个对象中，我只有 60 个，这 60 个是 JSON 文件中完整的对象，它们没有 NULL。

每个具有 null 的对象都不会进入数据库。

我试图编写一些在查询语句中放回“Null”而不是“”的代码，但是如果我在 phpmyadim 上尝试这样做只是为了检查它会给我错误，因为有问题的缺失字段是浮点数或时间戳和数据库不接受“Null”字符串或空的“”。

创建该表以在这些字段上接受 NULLS，问题是 PHP 使用“”发送查询，因此我无法发送所有对象。

有任何想法吗？谢谢

（我知道我可以导入为 JSON，但我真的很想拥有所有行的常规表）

标签： phpmysqljsonmariadb

Answer 1

例如;

<?php
$arr = ['a', '', null];

$values = join(', ', array_map(function ($value) {
    return $value === null ? 'NULL' : "'$value'";
}, $arr));

var_dump($values); // string(13) "'a', '', NULL"

更新：

addslashes还将NULL值转换为空字符串

请注意，SQL 注入是可能的！改用准备好的语句！

<?php

$json = '[{
    "market": "OKEx (Futures)",
    "symbol": "BTC-USD-SWAP",
    "coin_id": "bitcoin",
    "index_id": "OkexSwap-BTC",
    "price": "9520.5",
    "price_percentage_change_24h": 4.801690829232852,
    "contract_type": "perpetual",
    "index": 9526.418,
    "basis": 0.06216060080878105,
    "spread": null,
    "funding_rate": 0.011057,
    "open_interest": 116495100,
    "volume_24h": 466882900,
    "last_traded_at": 1592306177,
    "expired_at": null
}]';

$jsonData = json_decode($json, true);

foreach ($jsonData as $id => $row) {

    $insertKeys = join(', ', array_map(function ($value) {
        return $value === null ? 'NULL' : "`".addslashes($value)."`";
    }, array_keys($row)));

    $insertVals = join(', ', array_map(function ($value) {
        return $value === null ? 'NULL' : "'".addslashes($value)."'";
    }, array_values($row)));


    echo "INSERT INTO Derivatives ({$insertKeys}) VALUES ({$insertVals});" . "\n";

    /* 
    INSERT INTO Derivatives 
        (`market`, `symbol`, `coin_id`, `index_id`, `price`, `price_percentage_change_24h`, `contract_type`, `index`, `basis`, `spread`, `funding_rate`, `open_interest`, `volume_24h`, `last_traded_at`, `expired_at`) 
    VALUES 
        ('OKEx (Futures)', 'BTC-USD-SWAP', 'bitcoin', 'OkexSwap-BTC', '9520.5', '4.8016908292329', 'perpetual', '9526.418', '0.062160600808781', NULL, '0.011057', '116495100', '466882900', '1592306177', NULL);
    */
}