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. stackoverflow.com/questions/20215744/… — Nerdi.org
– Nerdi.org, Commented Apr 11, 2018 at 2: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
– James, Commented Apr 11, 2018 at 2: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
– James, Commented Apr 11, 2018 at 2: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
– Nerdi.org, Commented Apr 11, 2018 at 3:34

Nerdi.org · Accepted Answer · 2018-04-11 02:18:19Z

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, 2018 at 2:18

Nerdi.org

9336 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Over a year ago

Seeking Canonical: Non-returning PHP Recursion

Collectives™ on Stack Overflow

PHP/SQL recursive function to get all parent data

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related