We Recommend

Wicked Cool PHP: Real-World Scripts That Solve Difficult Problems
Wicked Cool PHP contains a wide variety of scripts to process credit cards, check the validity of email addresses, template HTML, and serve dynamic images and text.

Posted By

tylerhall on 12/19/07

Tagged

sql array nested

Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

gardnern
naz
romanos

Build Nested Array From SQL

Published in: PHP

URL: http://www.ideashower.com/solutions/create-a-parent-child-array-structure-in-one-pass/

During the time that I’ve been programming I’ve continually run into times when I needed to be able to store a multi-level list into a database and retrieve it. I’ve used this in a CMS system to allow uses to create and nest their own menu options or to allow users to create and nest categories of items. Saving it was never a problem, but retrieving it and building a PHP array with nested structure was a messy task. The problem was that you couldn’t easily enter a variable four levels deep into an array without knowing all of the parents of that item. This was okay if you retrieved the data in order of parents, but that meant if you wanted to sort the data by another column (such as name), you would have to do multiple passes through your data to continually reshuffle it.

Plain TextExpand

$refs = array();
$list = array();
 
$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";
$result = mysql_query($sql);
while($data = @mysql_fetch_assoc($result)) {
    $thisref = &$refs[ $data['item_id'] ];
 
    $thisref['parent_id'] = $data['parent_id'];
    $thisref['name'] = $data['name'];
 
    if ($data['parent_id'] == 0) {
        $list[ $data['item_id'] ] = &$thisref;
    } else {
        $refs[ $data['parent_id'] ]['children'][ $data['item_id'] ] = &$thisref;
    }
}

Report this snippet

Comment:

You need to login to post a comment.