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

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
                        (
      开发者_如何学JAVA                     [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.

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']);
    }

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.