Posted By

tylerhall on 12/19/07


sql array nested

Versions (?)

Who likes this?

6 people have marked this snippet as a favorite


Build Nested Array From SQL

 / Published in: PHP


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.

  1. $refs = array();
  2. $list = array();
  4. $sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";
  5. $result = mysql_query($sql);
  6. while($data = @mysql_fetch_assoc($result)) {
  7. $thisref = &$refs[ $data['item_id'] ];
  9. $thisref['parent_id'] = $data['parent_id'];
  10. $thisref['name'] = $data['name'];
  12. if ($data['parent_id'] == 0) {
  13. $list[ $data['item_id'] ] = &$thisref;
  14. } else {
  15. $refs[ $data['parent_id'] ]['children'][ $data['item_id'] ] = &$thisref;
  16. }
  17. }

Report this snippet  

You need to login to post a comment.