How to get result parent name of category from category id using codeigniter

Question

Please help me, how to get parent name of category. I have table like this.

table Category
+-------------+-----------------+-----------------+---------------+
| category_id | category_name   | category_parent | category_slug |
+-------------+-----------------+-----------------+---------------+
|           7 | Framework CSS   |               0 |               |
|           9 | Bootstrap       |               7 |               |
|          10 | Material Design |               7 |               |
+-------------+-----------------+-----------------+---------------+

table relationship
+-----------------+---------+-------------+
| relationship_id | post_id | category_id |
+-----------------+---------+-------------+
|              35 |      59 |           9 |
+-----------------+---------+-------------+

I use codeigniter, and i have a function to fetch post and category.

public function post_detail($tableName, $where = '')
{
    $post_title = $this->uri->segment(3);

    $query = $this->db->query("SELECT p.*, r.*, c.* FROM tbl_relationship AS r 
        INNER JOIN tbl_posts AS p ON r.post_id = p.post_id
        INNER JOIN tbl_category AS c ON r.category_id = c.category_id
        WHERE p.post_name = '$post_title'
    ");
    return $query->result();    
}

Result of function : (7 and bootstrap). How to result parent name of 7.?

http://stackoverflow.com/questions/4116416/parent-child-in-same-table — dev, Aug 25 '16 at 08:15
parent name of 7.! category_id 7 have parent name is framework css. — bl4ck, Aug 25 '16 at 08:42

score 1 · Accepted Answer · answered Aug 25 '16 at 08:45

Try this:

public function post_detail($tableName, $where = '')
{
    $post_title = $this->uri->segment(3);

    $query = $this->db->query("SELECT p.*, r.*, c.*, c1.category_name as parent_name FROM tbl_relationship AS r 
        INNER JOIN tbl_posts AS p ON r.post_id = p.post_id
        INNER JOIN tbl_category AS c ON r.category_id = c.category_id
        INNER JOIN tbl_category AS c1 ON c.category_parent = c1.category_id
        WHERE p.post_name = '$post_title'
    ");
    return $query->result();    
}

How to get result parent name of category from category id using codeigniter

1 Answers1