Looping through an array and inserting into a table with parent child relationship

Question

Hi there,

I have an array which looks like

     Array
     (
     [438044691:maincategoryone] => Array
     (
      [0] => Array
       (
        [id] => 438044692
        [name] => main category one item one
                    [category] => Array
                    (
                        [2] => Array
                        (
                           [id] => 4380444456
                           [name] => main category one item one - sub
                        )

                    )
       )

      [1] => Array
       (
        [id] => 438044693
        [name] => main category one item two
       )
     )

     [438044701:maincategorytwo] => Array
     (
      [0] => Array
       (
        [id] => 438044702
        [name] => main category two item one
       )

     )

     [438044709:maincategorythree] => Array
     (
      [0] => Array
       (
        [id] => 438044710
        [name] => main category three item one
       )

      [1] => Array
       (
        [id] => 438044711
        [name] => main category three item two
       )

     )
 )

How do i loop through this in PHP and store the data in a parent-child-grandchild relationship within the same table (MYSQL).

Also An example query to retrive the data in order (parent-child-grandchild) once inserted would be great.

Hope my question makes sense. If not please excuse me, but i'll be able to give you more information.

Many thanks.

Answer 1

I suggest a reference key to the parent in your mySQL table, like this :

CREATE TABLE categories (id INTEGER, name VARCHAR(32), parent INTEGER)

And then in your PHP code, you have a tree model and you read all the items in an id-indexed array, then assemble it into a tree.

$q = mysql_query("SELECT * FROM categories");
$n = mysql_num_rows($q);
$items = array();

while($item = mysql_fetch_assoc($q))
    $items[$item['id']] = $item;

foreach($items as $item)
    $items[$item['parent']]['category'] = $item;

print_r($items);

To read the values from the tree, I would use a recursive function :

function add_to_tree($items, $tree_part, $parent) {
    $tree_part['parent'] = $parent;
    $items.push($tree_part);
    foreach($tree_part['categories'] as $child)
        add_to_tree($items, $child, $tree_part['id']);
}

Then you just have to create an empty array, fill it and read the values sequencially.

$items = array();
add_to_tree($items, $tree);
foreach($items as $item)
    mysql_query("INSERT INTO categories VALUES('{$item['id']}', '{$item['name']}', '{$item['parent']}');

Of course you will have to handle special cases like the root object and missing links, which are not supported in this previous code.

Answer 2

You could do this by creating a table with 3 columns id, name, parentid

You can use the PHP array_walk function to walk through the array and insert the lines as you go (or build an sql query to execute later):

    $myArray = ......
    array_walk($myArray , 'insertInDb', null);

    function insertInDb($item, $key, $parent_id) {
        if (is_null($parent_id)) {
            $sql = sprintf('INSERT INTO myTable (`id`, `name`, `parentid`) VALUES (%d, "%s", NULL)', $item['id'], $item['name']);
        } else {
           $sql = sprintf('INSERT INTO myTable (`id`, `name`, `parentid`) VALUES (%d, "%s", %d)', $item['id'], $item['name'], $parent_id);
        }
        array_walk($item['category'] , 'insertInDb', $item['id']);
    }