PHP/SQL recursive function to get all parent data

Question

I know there are plenty of questions on recursive queries, but this isn't a "how to" question so much as, "what am I missing?".

I feel like I'm close, but this just isn't working for me. I'm trying to get all the parents in the line of a sub-category:

Data

cat_id | cat_parent | cat_name | cat_description | cat_sort | cat_active 
1 | 0 | Home | Primary Forum Location | 6 | 1
2 | 0 | Software | Software Discussions | 1 | 1
17 | 1 | Programming |  | 1 | 1
19 | 1 | MySql Workbench | Description | 1 | 1
20 | 2 | SQL Tables | Description | 2 | 1
21 | 2 | Another Test |  | 1 | 1
22 | 21 | Sub-Sub Cat |  | 1 | 1
23 | 22 | Sub-Sub-Sub Cat |  | 1 | 1

Controller

    function getparents($catid = 1) {
    $parents = $this->forum_model->get_parent($catid);

    echo "<pre>" . print_r($parents, TRUE) . "</pre>";
}

Model

function get_parent($catid = 0) {
    $parent = array();
    $this->db->select('*');
    $this->db->from(TBL_FORUM_CATEGORIES);
    $this->db->where('cat_id',$catid);
    $child = $this->db->get()->row_array();
    $parent[] = $child;

       if ($child['cat_parent'] == 0) {
           return $parent;
       } else {
           $push = $this->get_parent($child['cat_parent']);
           array_push($parent,$push);
       }        
}

Result

<pre></pre>

I know it's doing the queries at least partly right, because I turned on the profiler and I can see the queries happening.

Running the controller with ID=22:

0.0007      SELECT * FROM `forum_categories` WHERE `cat_id` = '22' 
0.0004      SELECT * FROM `forum_categories` WHERE `cat_id` = '21' 
0.0004      SELECT * FROM `forum_categories` WHERE `cat_id` = '2'

This should help you greatly. https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Nerdi.org, Apr 11 '18 at 02:06
Yes, those are the definition of "how to" answers for recursive functions, but unfortunately doesn't tell me the the basic part of my question... "what am I missing?" — James, Apr 11 '18 at 02:11
That did it... though I'm confused on the reason why it needed to be there. If I'm looping through the parents and pushing each result in - why wouldn't the first return parent be enough? — James, Apr 11 '18 at 02:32
Every time you call get_parent() you are opening up a new instance of it. What this means is that get_parent is only ever returned when $child['cat_parent'] is = to 0. So it would only work if the first id you used has no parent (= 0). This is because if they did have a parent, it would open a new instance of get_parent with $push = get_parent(). However, even if $push gets a value from get_parent, it then gets pushed into $parent with array_push, but there is nothing after that to return it. Basically your first get_parent() opens up another get_parent () w/ new parent[] array — Nerdi.org, Apr 11 '18 at 03:34
and then that get_parent() opens another get_parent() inside of it, etc until it runs out of parents. You use return $parent at the end of each one so that they can pass along the information they found to the get_parent() function that called it... it's almost like get_parent_1(), get_parent_2(), get_parent_3() in a way, but it's just 3 copies of the get_parent() function, all running with their own initial & unique $catid to start off with. I hope this helps you understand how it works :) — Nerdi.org, Apr 11 '18 at 03:35

score 1 · Accepted Answer · answered Apr 11 '18 at 02:18

1

After array push for $push variable, write:

 return $parent;

That should be enough for your function to act as it's own loop.

answered Apr 11 '18 at 02:18

Nerdi.org

895
6
13

[Seeking Canonical: Non-returning PHP Recursion](https://meta.stackoverflow.com/q/418239/2943403) – mickmackusa Feb 12 '23 at 23:59

PHP/SQL recursive function to get all parent data

1 Answers1