Recursive categories with a single query?

by

If the tree isn’t too large, you can simply build the tree in PHP using some clever references.

$nodeList = array();
$tree     = array();

$query = mysql_query("SELECT category_id, name, parent FROM categories ORDER BY parent");
while($row = mysql_fetch_assoc($query)){
    $nodeList[$row['category_id']] = array_merge($row, array('children' => array()));
}
mysql_free_result($query);

foreach ($nodeList as $nodeId => &$node) {
    if (!$node['parent'] || !array_key_exists($node['parent'], $nodeList)) {
        $tree[] = &$node;
    } else {
        $nodeList[$node['parent']]['children'][] = &$node;
    }
}
unset($node);
unset($nodeList);

This will give you the tree structure in $tree with the children in the respective children-slot.

We’ve done this with fairly large trees ( >> 1000 items) and it’s very stable and a lot faster than doing recursive queries in MySQL.

More Related Contents:

  1. php+mysql Fatal error: Call to undefined function mysql_connect()
  2. Using PHP data in MySQL [closed]
  3. drop down menu using php and mysql [closed]
  4. php code for multiple search box after connecting to mysql [closed]
  5. Checking the availability of rooms
  6. How to sort user data by id
  7. Using PHP 5.5’s password_hash and password_verify function
  8. Example of how to use bind_result vs get_result
  9. mysqli::query(): Couldn’t fetch mysqli
  10. I have an array of integers, how do I use each one in a mysql query (in php)? [duplicate]
  11. Levenshtein: MySQL + PHP
  12. Incorrect Integer (2147483647) is inserted into MySQL?
  13. I need my PHP page to show my BLOB image from mysql database
  14. How to test if a MySQL query was successful in modifying database table data?
  15. jQuery UI Sortable, then write order into a database
  16. How do i “echo” a “Resource id #6” from a MySql response in PHP?
  17. Delete multiple rows by selecting checkboxes using PHP
  18. mysql_real_escape_string VS addslashes
  19. How to add a delete button to a PHP form that will delete a row from a MySQL table
  20. IP address storing in MySQL database using PHP [duplicate]
  21. “No such file or directory” or “No such host is known” when running migrations
  22. Password_Hash not working on my PHP login
  23. Select last 20 order by ascending – PHP/MySQL
  24. Formatting an SQL timestamp with PHP
  25. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  26. mysql_connect VS mysql_pconnect [closed]
  27. Difference between mysql_fetch_array and mysql_fetch_row?
  28. mysql_fetch_array skipping first row
  29. 500 Internal Server Error?
  30. Aggregated query without GROUP BY

Leave a Comment