PHP/SQL recursive function to get all parent data

86
April 11, 2018, at 12:26 PM

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' 
Answer 1

After array push for $push variable, write:

 return $parent;

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

READ ALSO
How do I change the slashes that get appended to quotes from mysql, back to their original form for display

How do I change the slashes that get appended to quotes from mysql, back to their original form for display

I am trying to allow the user to store a string in a database which will often contain an anchor tag and or multiple paragraphsI am posting to my database and mysql appends on the \ to quotes and such via the mysql_real_escape_string() function

92
how to get price for hour PHP [on hold]

how to get price for hour PHP [on hold]

I have to do an exercise in which if a car has been parked for 1 hour or less it must pay $ 50 and if an hour passes it should be $ 100 (the double) and so on

144
LDAP SEARCH, Can&#39;t get information

LDAP SEARCH, Can't get information

Trying to get user information in LDAP in PHP but the return is

114
Error when uploading a video file as a block blob on Azure using PHP SDK

Error when uploading a video file as a block blob on Azure using PHP SDK

I was trying to upload a video of size close to 50 MB using Azure's PHP SDK

161