CODEWITHSUNDEEP
phpmysql
WEBProject
WEBProject

Reputation: 1335

Recursive categories with a single query?

I have a website with articles and sections, each sections can have a parent section, as much as they like for example:

subject 1
 -subject 2 
 --subject 3
 -subject 4
 --subject 5
 --subject 6
 ---subject 7
subject 8
subject 9

etc..

Now, i want to fetch them recursively, what is the most efficient way to do it via php and mysql?

Tnx in advanced.

Upvotes: 14

Views: 13625

Answers (10)

Manish
Manish

Reputation: 3633

I have made a query for you. This will give you Recursive Category with a Single Query:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM app_category WHERE Parent_id=0
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM app_category AS a LEFT JOIN app_category AS b ON b.parent_id=a.id WHERE a.Parent_id=0 AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM app_category AS a LEFT JOIN app_category AS b ON b.parent_id=a.id LEFT JOIN app_category AS c ON c.parent_id=b.id WHERE a.Parent_id=0 AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM app_category AS a LEFT JOIN app_category AS b ON b.parent_id=a.id LEFT JOIN app_category AS c ON c.parent_id=b.id LEFT JOIN app_category AS d ON d.parent_id=c.id WHERE a.Parent_id=0 AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

Here is a fiddle.

Upvotes: 0

Daniel Egeberg
Daniel Egeberg

Reputation: 8382

That depends on how you have stored your data. There is a good article on MySQL.com called Managing Hierarchical Data in MySQL that talks about this.

Upvotes: 10

henry
henry

Reputation: 71

Mine uses recursion with one query as well...

A recursive method to storing hierarchical data without multiple calls to the database

I wanted to use recursion also due to its simplicity but like you I wanted to remove the overhead of a recursive query. My logic says in this way you are just moving the load from the database to memory depending on the size of your database result but I have not done any real scalability testing on this so I don't know how much of an impact it has vs non recursive methods.

Upvotes: 2

codez
codez

Reputation: 1391

From your example to each category save its full path in another field:
1 - 1
2 - 1.2
3 - 1.2.3
4 - 1.4
5 - 1.4.5
6 - 1.4.6
7 - 1.4.6.7
8 - 8
9 - 9

and then just query with ORDER BY on that field

Upvotes: 0

Luke
Luke

Reputation: 3353

I cant guarentee I havent made any syntax mistakes but this should work with one query. 

class menuSystem{ 
var $menu;
var $db; #this variable is my db class assigned from the construct, I havent written the construct in, I can if you need it
function startNav(){
    $this->db->runQuery("select * from table order by parent asc");
    $menu = array(0 => array('children' => array()));
    while ($data = $this->db->fetchArray()) {
      $menu[$data['category_id']] = $data;
      $menu[(is_null($data['parent']) ? '0' : $data['parent'] )]['children'][] = $data['category_id'];
    }
    $this->menu = $menu;
    $nav = '<ul>';
    foreach($menu[0]['children'] as $child_id) {
      $nav .= $this->makeNav($menu[$child_id]);
    }
    $nav .= '</ul>';
}

function makeNav($menu){
   $nav_one = '<li>'."\n\t".'<a href="#">'$menu['name'].'</a>';
    if(isset($menu['children']) && !empty($menu['children'])) {
      $nav_one .= "<ul>\n";
      foreach($menu['children'] as $child_id) {
        $nav_one .= $this->makeNav($this->menu[$child_id]);
      }
      $nav_one .= "</ul>\n";
    }
    $nav_one .= "</li>\n";
return $nav_one;
}

}

EDIT: sorry I use this in my code as a class and thought I had managed to take it out of a class for you but forgot I needed $this->menu

UPDATE: I think the below is out of a class now, sorry for such a long answer

$result = mysql_query("select * from table order by parent_id asc");
$menu = array(0 => array('children' => array()));
while ($data = mysql_fetch_array($result)) {
  $menu[$data['category_id']] = $data;
  $menu[(is_null($data['parent_id']) ? '0' : $data['parent_id'] )]['children'][] = $data['category_id'];
}
$global_menu = $menu;
$nav = '<ul>';
foreach($menu[0]['children'] as $child_id) {
  $nav .= makeNav($menu[$child_id]);
}
$nav .= '</ul>';

function makeNav($menu) {
  global $global_menu;
  $nav_one = '<li>'."\n\t".'<a href="#">' . $menu['name'].'</a>';
  if(isset($menu['children']) && !empty($menu['children'])) {
    $nav_one .= "<ul>\n";
    foreach($menu['children'] as $child_id) {
      $nav_one .= makeNav($global_menu[$child_id]);
    }
    $nav_one .= "</ul>\n";
  }
  $nav_one .= "</li>\n";
  return $nav_one;
}

Hope it helps

Luke

Upvotes: 1

Stefan Gehrig
Stefan Gehrig

Reputation: 83622

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.

Upvotes: 27

Serty Oan
Serty Oan

Reputation: 1726

You could have a look at this topic : how to get the hierarchical menu from mysql that was opened yesterday and is about the same thing.

Upvotes: 2

Simon
Simon

Reputation: 23141

if assume, that your table has id, id_parrent and name fields

function tree($id)
{
    $query = "SELECT `name`,`id` from `table` WHERE `id_parrent` = '$id'";
    $result = mysql_query($query);
     if(mysql_num_rows($result) != 0)
       {
            echo "<ul>";
            while($row = mysql_fetch_array($result))
            {             
                 echo "<li>",$row[name],"</li>";
                 tree($row[id]);
            }
            echo "</ul>";
       }
}

by so you'll get the whole tree

category1
       category1_1
       category1_2
              category1_2_1
              category1_2_2
       category1_3
...........................

Upvotes: 0

Jani
Jani

Reputation: 526

Well, you can fetch all the categories in an array in just the one query, as you know:

$query = "SELECT `name`,`id` from `table`";

Having that in an array, you can build the tree with some nested loops. It won't be fast, but is simple, and faster than using recursive queries. Also, you can cache the built tree and not having to rebuild it every time.

Upvotes: 3

WEBProject
WEBProject

Reputation: 1335

the first part of the article relates only to 4 levels, the last part is not the way I want to do it.

my structure is something like that:

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | test                 |   NULL |
|           2 | subject1             |   1    |
|           3 | subject2             |   1    |
|           4 | subject3             |   2    |
|           5 | subject4             |   4    |
+-------------+----------------------+--------+

I dont want to complicate things, I want to do it in the most simple way, but to fetch the data in the most efficient way.

Upvotes: 0

Related Questions