Skip to content

Instantly share code, notes, and snippets.

@m3g4p0p
Last active June 20, 2016 22:52
Show Gist options
  • Save m3g4p0p/2fb7f6c9dd40c8317b2199471a59d3ea to your computer and use it in GitHub Desktop.
Save m3g4p0p/2fb7f6c9dd40c8317b2199471a59d3ea to your computer and use it in GitHub Desktop.
A simple script to iterate over an associative array (from a JSON file), create an SQL schema and populate it with the data
<?php
$idMap = [];
function json2sql($current, $parent, $json) {
global $idMap;
$table = "";
$insert = "";
$children = "";
// If it's an associative array, create a table
// and insert the input
if (array_keys($json) !== range(0, count($json) - 1)) {
if (isset($idMap[$current])) {
$idMap[$current]++;
} else {
$idMap[$current] = 0;
}
$table = "CREATE TABLE IF NOT EXISTS `" . $current . "` (\n"
. "\t`ID_" . $current . "` INT(11) NOT NULL PRIMARY KEY";
$insert = "INSERT INTO `" . $current . "` VALUES(\n\t" . $idMap[$current];
if ($parent) {
$table .= ",\n\t`FK_" . $parent . "` INT(11)";
$insert .= ",\n\t" . $idMap[$parent];
}
foreach ($json as $key => $value) {
if (is_array($value)) {
$children .= json2sql($key, $current, $value);
} else {
// Type conditions going here
$table .= ",\n\t`" . $key . "` VARCHAR(255)";
$insert .= ",\n\t'" . $value . "'";
}
}
$table .= "\n);\n\n";
$insert .= "\n);\n\n";
}
// Otherwise, loop through the elements
else {
foreach ($json as $element) {
$children .= json2sql($current, $parent, $element);
}
}
return $table . $insert . $children;
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment