Storing and reading a JSON file in MySQL with PHP

Storing values from a json output into a database is a common occurence, but storing the whole json call into a column not so much.

Traditionally it is more efficient and effective to store the values individually in their respective columns like name in `name` and email in `email` and give them a date time stamp to allow for separation based on time inserted but for archival reasons you may want to store the whole json file/call in MySQL too.

2 tables one that defines the call and date time this is given an id that is used when fetching the json from the other table.

You just need to find the id that satisfies you date time range and then search the data table for where id = id.

identifiers table:

data table:

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) DEFAULT NULL,
  `json` json,
  UNIQUE KEY `Index 1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `identifiers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(50) DEFAULT '0',
  `date_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The insert:

 
The id (auto increment) and date_time (current_timestamp) are automatically calculated and added, no need to do those manually.
 
fetch_assoc()) {
    $id = $row2["id"];
};
$sql = "INSERT INTO `data` (`json`, `id`) VALUES ('$data', '$id')";
$result = mysqli_query($connect, $sql);
 
Selecting the most recent (highest) id we insert that along with the json data.
 
To then call or retrieve the data just use
 
$sql = "SELECT `json` FROM `data` WHERE `id` = '$your_id';";//gets the data from specified id index
$result2 = mysqli_query($connect, $sql);
while ($row2 = $result2->fetch_assoc()) {
    $json = $row2["json"];
};
$json = substr($json, 1, -1);//removes leading and ending "
$json = json_decode($json, true);
foreach ($json as $val) {
    $name = $val['name'];
    echo "$name
";//echos the names, one per line
}
 
or just return the whole json with:
 
fetch_assoc()) {
    $json = $row2["json"];
};
$json = substr($json, 1, -1);
echo $json;
 
This post was made using the free mock api at: jsonplaceholder.typicode.com