PHP json_decode from API to sql: only imports objects that does not contain empty values

Although there are many topics similar to this, they don’t go to the issue I’m having, so a little clarification was appreciated.

I’m building a database with some data from various API’s, the data coming from those API’s are in JSON format (some are objects, some are arrays).

I’m starting with the objects that have a simpler format, so I’ve coded a little PHP script to fetch the data and insert it into the designated DB table.

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

The JSON data is like this:

[
 {
    "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
  },
.
.
.
]

Notice that some values are null from random places, for ex, in the object above the “spread” and “expired_at” are null, on the next objects it can be others, and on some others all values are present.

On the ECHO from my code and following the above example I get something like this:

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

(Don’t bother with some value changes since they where fetch on different times, it’s just an example)

Notice that the values that were null are now converted to “”.

What happens when checking the table on DB itself is that from the expected near 500 objects I only have 60, those 60 are the ones that are complete on the JSON file, they have no NULLS.

Every object that has a null on it is not getting into the DB.

I have tried to code something that puts back the “Null” instead of “” in the query statement but if I try that on the phpmyadim just to check it gives me errors since the missing fields in question are either float or timestamp and the DB does not accept the “Null” string or the empty “”.

The table is created to accept NULLS on those fields, the problem is that PHP sends the query with “” so I can’t manage to send all objects.

Any ideas? Thanks

(I know I can import as JSON, but I really want to have the regular table with all rows)

Answer

For example;

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

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

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

Update:

addslashes also converts NULL values to empty strings

Note that SQL injection is possible ! Use prepared statements instead!

<?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);
    */
}