Getting all children in tree structure table - MySql

Question

I have a tree structured table, one of my table is Categories:

category_id  |  parent_id (in foreign key it is category_id) | title

And now I want to have a query and give it a category_id and it search whole table and go inside each children and get their children to the end point and select all.

I can do this by PHP, but I want to see how can I do it by SQL, this is my PHP code:

function getChild($id){
    static $category_ids = [];
    $category_ids[] = $id;
    $list=$this->_model->select('categories','*', 'category_parent_id = '.$id);

    foreach($list as $category) {
        $category_ids[] = $category['category_id'];

        if($category['last_child'] == 0){
            $this->getChild($category['category_id']);
        }
    }

    $category_ids = array_unique($category_ids);

    return $category_ids;
}
  //$list=$this->_model->select('categories','*', 'parent_id = '.$id);
 //The above code is one of my functions (methods) and it select from table category * by parent_id

How to write a SQL query for it?

Which one is faster (PHP or that SQL query)

Possible duplicate of [How to create a MySQL hierarchical recursive query](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — trincot, Dec 30 '15 at 08:40
Once you have the SQL solution, it will be easy to do a performance comparison. — trincot, Dec 30 '15 at 08:43
maybe interesting? [Trees and Other Hierarchies in MySQL](http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html). Found in: [Common MySQL Queries](http://www.artfulsoftware.com/infotree/queries.php). — Ryan Vincent, Dec 30 '15 at 11:04

Getting all children in tree structure table - MySql

0 Answers0