During an export of my data, I can choose between CSV and JSON format. Viewing the CSV, data, including column names are arranged in the exact same way as I would like to import into MySQL database.
I'm getting JSON from a webpage and it's stored in $data variable.
<html>
<body>
<?php
// Include Parsehub REST api wrapper
require_once __DIR__ . '/vendor/autoload.php';
use Parsehub\Parsehub;
$api_key = "XXX";
$project_token = "XXX";
$parsehub = new Parsehub($api_key);
$data = $parsehub->getLastReadyRunData($project_token);
echo $data;
?>
</body>
</html>
Echo output of it would return JSON: https://pastebin.com/raw/AZt4gvsC
CREATE TABLE:
CREATE TABLE `utakmice_1` (
`utakmica_name` varchar(64) NOT NULL,
`utakmica_url` varchar(256) NOT NULL,
`utakmica_liga` varchar(64) NOT NULL,
`utakmica_liga_url` varchar(256) NOT NULL,
`utakmica_vreme` varchar(64) NOT NULL,
`utakmica_datum` varchar(64) NOT NULL,
`utakmica_kvote_kvota` decimal(10,2) NOT NULL,
`utakmica_kvote_kladionica` varchar(63) NOT NULL,
`utakmica_kvote_kladionica_url` varchar(256) NOT NULL,
`utakmica_kvote_igra` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
In which way could all the json data could be inserted?
CodePudding user response：
Using PHP, you need to:
Convert the JSON data to an associative array using json_decode
$data = json_decode($jsondata, true);
Process that array to get the data you need to insert in the table
$name = $data['utakmica']['name'];
$url = $data['utakmica']['age'];
etc
Insert to MySQL using the Insert query (we're using mysqli here but you could use PDO)
$sql = "INSERT INTO utakmice_1(utakmica_name, utakmica_url)
VALUES('$name', '$url')";
if(!mysqli_query($con, $sql))
{
die('Error : ' . mysql_error());
}
CodePudding user response：
Given the CSV display and the structure in the JSON, when doing a standard recursive iteration over the JSON structure with the modifcation to terminate leafs being objects that no longer contain a property that is an array, it would be possible going over all leaf nodes having their property names prefixed with all parent property names (dropping array indexes) and using all lower depth current objects (from the subiterators) in depth ascending order to create a merged object to obtain such objects with such keys.
The boilerplate of this is RecursiveArrayIterator and RecursiveIteratorIterator.
The important part is that if an object has a property that is an array, it is the only property that is an array and the array is an array of objects of which are all homogeneous. This is the case for your JSON Text, and this is important as both the RecursiveArrayIterator and the RecursiveIteratorIterator need to be modified for such traversal.
The unmodified boilerplate behaviour is also shown in the related Q&A How can I parse a JSON file with PHP?:
$it = new RecursiveIteratorIterator(
new RecursiveArrayIterator($json)
);
foreach ($it as $property => $value) {
...
}
As in your use-case both objects need to be modified and it only works with leaves only iteration (as only then merging the objects makes sense), the whole construct could be extended from RecursiveIteratorIterator, overwriting the constructor taking the $json directly, using the modification of RecursiveArrayIterator.
Speaking of it, it needs to keep the key-prefix (here implemented as a private property), turn objects into non-traversable children (standard behaviour is every non-object and non-array property construes a leaf) and removal of array properties from each node as those should only be available to traverse up to leaf nodes and then when merging across the whole path in depth ascending order, they would be superfluous:
$it = new class ($json) extends \RecursiveIteratorIterator {
public function __construct(object $json)
{
$jsonObjectRecursion = new class($json) extends \RecursiveArrayIterator
{
private ?string $keyPrefix = null;
public function key()
{
return (null !== $this->keyPrefix ? $this->keyPrefix . '_' : '') . parent::key();
}
public function current(): object|array
{
$current = parent::current();
if (is_array($current)) {
return $current;
}
$current = array_filter(get_object_vars($current), static fn ($any) => !is_array($any));
if (null !== $this->keyPrefix) {
$current = array_combine(preg_filter('/^/', "{$this->keyPrefix}_", array_keys($current)), $current);
}
return (object)$current;
}
public function hasChildren(): bool
{
$current = parent::current();
if (is_array($current)) {
return parent::hasChildren();
}
return (is_object($current) && array_filter(get_object_vars($current), 'is_array'));
}
public function getChildren(): self
{
$current = parent::current();
if (is_array($current)) {
$children = parent::getChildren();
$children->keyPrefix = $this->key();
return $children;
}
$probe = array_filter(get_object_vars($current), 'is_array');
$children = new self((object) [key($probe) => current($probe)]);
$children->keyPrefix = $this->keyPrefix;
return $children;
}
};
parent::__construct($jsonObjectRecursion);
}
...
As this excerpt shows, array traversal is preserved and the key-prefix is passed along accordingly. The array handling within current() can perhaps be dropped as arrays only come into action with has/getChildren() invocations.
The $json is just this one big JSON Object decoded into PHPs' stdClass. Despite it has Array in its name, the RecursiveArrayIterator can handle these JSON Objects well without converting them into an array.
Next to the constructor which merely only defines the inner iterator, this RecursiveIteratorITerator only implements one second method, current(), which does the merging:
...
public function current(): object
{
$object = [];
for ($depth = 0; $depth <= $this->getDepth(); $depth ) {
$current = $this->getSubIterator($depth)->current();
if (is_array($current)) {
continue;
}
$object = get_object_vars($current);
}
return (object)$object;
}
};
As $it is now defined, all that needs is to iterate over it and proceed with the values:
foreach ($it as $row) {
echo json_encode($row, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES), "\n";
break;
}
Output (first flat row):
{
"utakmica_name": "Deportivo Kuenka-Un. Katolika Kito",
"utakmica_url": "https://www.fudbal91.com/soccer_clubs/compare/Deportivo_Cuenca_vs_Un._Catolica_Quito/13641",
"utakmica_liga": "Ekvador 1",
"utakmica_liga_url": "https://www.fudbal91.com/competition/Ecuador,_LigaPro_betcris_-_Primera_Etapa/2022",
"utakmica_vreme": "00:00",
"utakmica_datum": "< 22.03.2022. (Utorak) >",
"utakmica_kvote_kvota": "2.75",
"utakmica_kvote_kladionica": "BetOle",
"utakmica_kvote_kladionica_url": "https://example.com/2YjM4Ft",
"utakmica_kvote_igra": "1"
}
To turn $it into an array of objects, use iterator_to_array($it, false). E.g. when you're looking forward to sort the whole list by one attribute without always needing to traverse the whole tree again and again for every operation.
Just as-if you would have used the CSV export that is already flat.
Such and even many more tips can be found in the related Q&A How to extract and access data from JSON with PHP? and many more others on the site.