Categories and Sub Categories - Best Way To Get Parent Category Name?

[eluser]Vheissu[/eluser]
I am currently developing a classifieds system using Codeigniter and plan it being my first finished Codeigniter web project, but that is for another day. When displaying a listing I want to display the category it belongs to and if the category has a parent, then display the name of that category.

I have a solution that works at present (although it feels a bit dirty), I was wondering if anyone can come up with an awesome working solution to help me out? I've posted my code below.

My listings model with a function that returns active listings:

Code:

public function fetch_all_active()
{
        return
        $this->db
                 ->group_by('listings.listing_name')
                 ->from('listings, categories, usermeta')
                 ->where('listings.listing_status', '1')
                 ->join('categories c', 'c.category_id = listings.listing_category_id')
                 ->join('usermeta u', 'u.meta_user_id = listings.listing_owner_id', 'left')
                 ->get()
                 ->result_array();            
}

My listings controller:

Code:

public function index()
    {
        $data['CI'] = & get_instance();
        $data['listings'] = $this->mlisting->fetch_all_active();
        $this->parser->parse('listings.php', $data);
    }

My listings view (using Dwoo for templating):

Code:

{foreach $listings listing}
<p><strong>Listing name</strong>: {$listing.listing_name}</p>
<p><strong>Description</strong>: {$listing.listing_description}</p>
{$cat1 = $CI->mcategories->get_category($listing.category_parent)}
<p><strong>Category</strong>: {$cat1.category_name}&nbsp;->&nbsp;{$listing.category_name}</p>
<p><strong>Owner</strong>: {$listing.first_name} {$listing.last_name}</p>
<div><strong>Content:</strong> {$listing.listing_content}</div>
<hr />
{/foreach}

As you can see I am storing the Codeigniter instance inside of a variable and then I am calling another model which has a function that will get me the category name if it is supplied a category ID.

See below for the function that fetches a category name via an ID:
This is a model called, 'mcategories'

Code:

function get_category($id){
        $data = array();
        $Q = $this->db
             ->select('category_name')
             ->from('categories')
             ->where('category_id', $id)
             ->get();
        
        if ($Q->num_rows() > 0){
            $data = $Q->row_array();
        }
        
        $Q->free_result();
        return $data;
    }

Is there a better way that I can do this? I hate having to pass the Codeigniter instance to my view and then call a model function. I also would prefer to keep that function in it's mcategories.php file because I am trying to keep relevant code inside of relevant model files.

[eluser]mddd[/eluser]
Calling a model method from the view is not necessarily wrong. Something like this is okay by me:

Code:

if ($user_model->isloggedin()) { // show info }

I think the problem is more, that you are calling a model method inside a loop. The method does a database query to get the name of the parent. So this does many queries which is not good. You should just get the name of the parent along with the initial listing! Use a join to join the categories table on itself.

[eluser]Vheissu[/eluser]
I get what you are saying about joining the categories table on to itself, but what joining criteria should I be defining to make that achievable? My mind doesn't work very well in a join frame of mind.

If you notice in my code I am already joining the categories table in my query, is it possible to join the same database table twice in one query?

I already have this in my query:

Code:

join('categories c', 'c.category_id = listings.listing_category_id')

Thanks for the help.

[eluser]mddd[/eluser]
Yes you can join multiple times on the same table. But you have to give them different aliases.
I think it should be:

Code:

select ...
from listings l
left join categories c on c.category_id = l.listing_category_id
left join categories pc on pc.category_id = l.listing_category_parent
...

By the way, if you are naming the tables in the join part, you don't need to mention them all in the from part.

[eluser]Vheissu[/eluser]
Sorry to be a pain, but I have this code and it doesn't seem to have changed much. It seems that joining the categories table again doesn't add in any new table names and in-fact overwrites the previous field names. Isn't an alias supposed to stop field names being overwritten?

Code:

join('categories c', 'c.category_id = listings.listing_category_id')
join('categories pc', 'pc.category_id = c.category_parent', 'left')

Here is a print_r of my result:

Code:

Array ( [0] => Array ( [listing_id] => 2 [listing_owner_id] => 1 [listing_category_id] => 3 [listing_name] => Lead guitarist needs a metal band [listing_description] => A lead guitarist needs a metal band to strut his stuff in, maybe bust some caps. [listing_content] => experienced guitarist looking for an awesome band to join!
[listing_status] => 1 [listing_image] => [category_id] => 1 [category_name] => Musicians seeking bands [category_description] => Musicians looking for a band to join. [category_parent] => 0 [category_status] => 1 [meta_id] => 1 [meta_user_id] => 1 [first_name] => Dwayne [last_name] => Charrington [address_1] => 123 Fake street [address_2] => Fakeville [country] => Australia [postcode] => 1234 )

The category that the particular listing results I am showing you belongs to is a sub-category of the 'Musicians seeking bands' category called 'Bass players'

[eluser]mddd[/eluser]
Haha that's funny. I AM a bass player seeking a band!

Anyway, you need to name the right fields in your SELECT part. Otherwise you a right, fields with the same names won't show up.
Something like

Code:

select listings.*, c.category_name catname, pc.category_name parentname from ...

Then you can get the names as the fields 'catname' and 'parentname'.

[eluser]Vheissu[/eluser]
Thanks Mddd, that worked. I'm kind of new to the whole joins thing even though I've been using PHP and MySQL for about 4 years now (don't ask me how that's possible), I've always managed to get around using JOINS which probably means some of the applications I've coded have probably undergone some heavy optimisation (or you would hope so).

I appreciate the help and patience. This will help me with another project that I've got in the works after I finish this one.

[eluser]mddd[/eluser]
You're welcome.
I think most developers start out doing simple queries and working on them in php, and slowly move on doing more in mysql. In most situations it is just more efficient!