1

I have an array of some parameters (like a YAML config file) and this need to be written to database finaly with primary ids and so on.

For example this is a part of my array:

$settings = [
    'basic' => [                       // first level category
        'installation_type' => [       // setting parameter with parameter attributes
            'type' => '["single","cluster"]',
            'description' => 'bla blah',
            'readonly' => false,
            'hidden' => false,
            'trigger' => null,
            'default' => 'single'
        ],
        'db_master_host' => [
            'type' => 'ip',
            'description' => 'Database hostname or IP',
            'default' => 'localhost'
        ],
        'db_master_user' => [
            'type' => 'text',
            'description' => 'Database username',
            'default' => 'test'
        ],
        'db_master_pwd' => [
            'type' => 'secret',
            'description' => 'Database user password',
        ],
        'db_master_db' => [
            'type' => 'text',
            'description' => 'Database name',
            'default' => 'test'
        ]
    ],
    'provisioning' => [         // first level category
        'snom' => [             // second level category
            'snom_prov_enabled' => [
                'type' => 'switch',
                'default' => false
            ],
            'snom_m3' => [
                'snom_m3_accounts' => [
                    'type' => 'number',
                    'description' => 'bla blah',
                    'default' => '0'
                ]
            ],
            'snom_dect' => [
                'snom_dect_enabled' => [
                    'type' => 'switch',
                    'description' => 'bla blah',
                    'default' => false
                ]
            ]
        ],
        'yealink' => [
            'yealink_prov_enabled' => [
                'type' => 'switch',
                'default' => false
            ]
        ]
    ],
];

As result I need some arrays like images of database tables (category, setting, value) with self-generated IDs:

$category["basic"] = [id: 1, parent: 0, name: "basic", order: 1]
$setting["installation_type"] = [id: 1, catId: 1, name: "installation_type", type: '["single","cluster"]', desc: 'asd', order: 1]
$value["installation_type"] = [id: 1, setId: 1, default: 'single']
$setting["db_master_host"] = [id: 2, catId: 1, name: "db_master_host", type: 'ip', desc: 'asd', order: 2]
$value["db_master_host"] = [id: 2, setId: 2, name: "db_master_host", default: 'localhost']
...
$category["provisioning"] = [id: 2, parent: 0, name: "provisioning", order: 2]
$category["snom"] = [id: 3, parent: 2, name: "snom", order: 3]
...
Improve this question
2
  • Is this limited to a maximum of two category levels? Why does $value["db_master_host"] include a name entry, but $value["installation_type"] doesn't? What determines which properties of the input data go where, into $setting or $value? Commented May 5 at 9:00
  • No, it has no category level limit. Sorry, I forgot the name attribute. The max. properties it can be used you find on "installation_type" whereby the property "type" and "description" are required inside the source array. The value array can be ignored because it referenced by setting array. Important are the category and setting array. Commented May 5 at 9:22

3 Answers 3

Reset to default
3

You can handle that nested config by walking through it recursively: whenever the current node owns a type key you treat it as a setting, otherwise it’s a category and you dive deeper. Keep two counters one for categories, one for settings and bump the right one each time you create a record so every row gets a predictable primary key. Within each level track an order integer as you iterate, preserving the original sequence for the UI. While you traverse, fill three arrays on the fly: one for categories (id, parent, name, order), one for settings (id, catId, name, type, desc, order), and one for default values (id, setId, default). A compact closure:

$catId = $setId = 0;
$categories = $settingsTbl = $valuesTbl = [];

$walk = function (array $node, int $parent = 0) use (&$walk, &$catId, &$setId,
        &$categories, &$settingsTbl, &$valuesTbl) {

    $order = 1;
    foreach ($node as $key => $val) {
        if (is_array($val) && array_key_exists('type', $val)) {
            $settingsTbl[$key] = [
                'id'    => ++$setId,
                'catId' => $parent,
                'name'  => $key,
                'type'  => $val['type'],
                'desc'  => $val['description'] ?? null,
                'order' => $order,
            ];
            $valuesTbl[$key] = [
                'id'      => $setId,
                'setId'   => $setId,
                'default' => $val['default'] ?? null,
            ];
        } else {
            $categories[$key] = [
                'id'     => ++$catId,
                'parent' => $parent,
                'name'   => $key,
                'order'  => $order,
            ];
            $walk($val, $catId);
        }
        $order++;
    }
};

$walk($settings);   // original config array

run this, then insert the three resulting arrays into your tables inside a single transaction; you’ll end up with clean, flat rows that still reference their original hierarchy through parent and catId.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Wow, that is a very clean and compact code for me. It works wonderful and results finally the optimal output for my database. Thanks very much, Kamyar!
2

I understand exactly what you're trying to achieve — you have a deeply nested associative array (something like a YAML-style configuration), and your goal is to flatten it into structured arrays that results in database tables. These structures include categories (with parent-child relationships) , settings (tied to categories) and values (holding defaults and linked to settings).

I've played around a little bit, so I converted this into something that is ready for database insertion with the self-generated IDs you mentioned and references. The code I provided you below recursively processes categories and nested subcategories and differentiates between categories and settings (containsSettings heuristics). It assigns incremental IDs to categories, settings and your values with preserving order.

I've created a github project for you, so you can test it/download it:
https://github.com/marktaborosi/stackoverflow-79606568

This is the result you get with this:
enter image description here

I know you're not asking for a redesign or for someone to question your approach — but I would absolutely do that if I were you. An OOP version would be more cleaner, feel free to ask if you need that.
As you can see it has a tightly coupled recursion logic.

Here is the code for it (if you just want to paste it):

// This is your pre-defined settings array
$settings = [
    'basic' => [
        'installation_type' => [
            'type' => '["single","cluster"]',
            'description' => 'bla blah',
            'readonly' => false,
            'hidden' => false,
            'trigger' => null,
            'default' => 'single'
        ],
        'db_master_host' => [
            'type' => 'ip',
            'description' => 'Database hostname or IP',
            'default' => 'localhost'
        ],
        'db_master_user' => [
            'type' => 'text',
            'description' => 'Database username',
            'default' => 'test'
        ],
        'db_master_pwd' => [
            'type' => 'secret',
            'description' => 'Database user password',
        ],
        'db_master_db' => [
            'type' => 'text',
            'description' => 'Database name',
            'default' => 'test'
        ]
    ],
    'provisioning' => [
        'snom' => [
            'snom_prov_enabled' => [
                'type' => 'switch',
                'default' => false
            ],
            'snom_m3' => [
                'snom_m3_accounts' => [
                    'type' => 'number',
                    'description' => 'bla blah',
                    'default' => '0'
                ]
            ],
            'snom_dect' => [
                'snom_dect_enabled' => [
                    'type' => 'switch',
                    'description' => 'bla blah',
                    'default' => false
                ]
            ]
        ],
        'yealink' => [
            'yealink_prov_enabled' => [
                'type' => 'switch',
                'default' => false
            ]
        ]
    ]
];

$categories = [];    // array<string, array{id: int, parent: int, name: string, order: int}>
$settingsList = [];  // array<string, array{id: int, catId: int, name: string, type: string|null, desc: string|null, readonly?: bool|null, hidden?: bool|null, trigger?: string|null, order: int}>
$values = [];        // array<string, array{id: int, setId: int, default: mixed}>

$catId = 1;
$setId = 1;
$valId = 1;

$order = 1;

/**
 * Recursively process nested config array into flat category, setting, value arrays.
 */
function processCategory(
    array $array,
    int   $parentId,
    array &$categories,
    array &$settingsList,
    array &$values,
    int   &$catId,
    int   &$setId,
    int   &$valId,
    int   &$order
): void
{
    foreach ($array as $key => $item) {
        if (is_array($item) && isAssoc($item) && containsSetting($item)) {
            $currentCatId = $catId++;
            $categories[$key] = [
                'id' => $currentCatId,
                'parent' => $parentId,
                'name' => $key,
                'order' => $order++,
            ];

            foreach ($item as $settingKey => $settingData) {
                if (is_array($settingData) && isAssoc($settingData) && containsSetting($settingData)) {
                    processCategory([$settingKey => $settingData], $currentCatId, $categories, $settingsList, $values, $catId, $setId, $valId, $order);
                } else {
                    $currentSetId = $setId++;
                    $settingsList[$settingKey] = [
                        'id' => $currentSetId,
                        'catId' => $currentCatId,
                        'name' => $settingKey,
                        'type' => $settingData['type'] ?? null,
                        'desc' => $settingData['description'] ?? null,
                        'readonly' => $settingData['readonly'] ?? null,
                        'hidden' => $settingData['hidden'] ?? null,
                        'trigger' => $settingData['trigger'] ?? null,
                        'order' => $order++,
                    ];

                    $values[$settingKey] = [
                        'id' => $valId++,
                        'setId' => $currentSetId,
                        'default' => $settingData['default'] ?? null,
                    ];
                }
            }
        }
    }
}

/**
 * Check if the array is associative.
 */
function isAssoc(array $arr): bool
{
    return array_keys($arr) !== range(0, count($arr) - 1);
}

/**
 * Determine if an array contains at least one sub-setting (based on 'type' or 'default').
 */
function containsSetting(array $arr): bool
{
    foreach ($arr as $val) {
        if (is_array($val) && (isset($val['type']) || isset($val['default']))) {
            return true;
        }
    }
    return false;
}

// Run your flattening
processCategory($settings, 0, $categories, $settingsList, $values, $catId, $setId, $valId, $order);

// Dumping the results
echo "--- Categories ---\n";
echo "<pre>";
print_r($categories);
echo "--- Settings ---\n";
print_r($settingsList);
echo "--- Values ---\n";
print_r($values);
echo "</pre>";
Improve this answer

Comments

0

Now I have my specific solution for my issue if anyone need for himself:

""public function flattenSettings(array $settings, $parentId = null, &$flat = [], &$idCounter = 1) {
    foreach ($settings as $key => $value) {
       if (is_array($value) && isset($value['type'])) {
          $id = $idCounter++;
          $flat[] = [
             'id' => $id,
             'name' => $key,
             'parent_id' => $parentId,
             'type' => $value['type'],
             'description' => $value['description'] ?? null,
             'default' => $value['default'] ?? null,
             'readonly' => $value['readonly'] ?? null,
             'hidden' => $value['hidden'] ?? null,
             'trigger' => $value['trigger'] ?? null,
          ];
          $this->settings[$key] = [...$value, ...['categoryId' => $parentId]];
          $this->settingValues[$key] = ['id' => $id, 'default' => $value['default'], 'settingId' => $id];

       } elseif (is_array($value)) {
          $currentId = $idCounter++;
          $flat[] = [
             'id' => $currentId,
             'name' => $key,
             'parent_id' => $parentId,
             'type' => 'group',
             'description' => null,
             'default' => null,
             'readonly' => null,
             'hidden' => null,
             'trigger' => null,
          ];
          $this->categories[$key] = [
             'id' => $currentId,
             'name' => $key,
             'parentId' => $parentId
          ];
          $this->flattenSettings($value, $currentId, $flat, $idCounter);
       }
    }
    return $flat;
}

echo "<pre>";
var_dump(flattenSettings(/*source array*/));
echo "</pre>";
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.