How can I retrieve the site name from a channel entry as a query?

Question

I have a plugin that I am editing at the moment and I am looking to do a query that takes the site_id from a channel(not a channel entry) and then grabs the id and passes that through a function that then gets the site name from it. So far I have got this in my function which basically populates a sub menu in the main nav in the admin backend. The menu spits out the channel name and then lists that in chronological order. The issue here is that every multisite has the same name for each entry as its based on seasons for example: AW14, AW15,AW16, SS14, SS15 and so on. Here is the code:

//Set each channel name and its link
                foreach ($assigned_channels as $key => $channel_id) {

                    //Add the channel to the order sub_nav array
                    $channels['jf_re_order_channel_' . $channel_id] = BASE.AMP.'C=addons_modules'.AMP.'M=show_module_cp'.AMP.'module='.JF_RE_ORDER_PACKAGE.'&channel_to_order='.$channel_id;

                    //Get the cannel name
                    $query = $this->EE->db->get_where('exp_channels', array('channel_id' => $channel_id), 1);
                    $row = $query->row();               
                    $channel_name = $row->channel_name;
                    $channel_parent_site = $row->site_id;
                    var_dump($channel_parent_site);             

                    //Add a nav_channel_id = channel name to the current lang object
                    $this->EE->lang->language['nav_jf_re_order_channel_' . $channel_id] = $channel_name;

                } 

I can var_dump the id and I get the id for each site that channel is located in, what I need to do now is get the site name from that with the id. Is that possible with a query like below or inside this query.

Cheers

Answer 1

Here's the query to get the site name and site label for each channel:

SELECT 
    site_name, site_label, c.site_id, channel_name, channel_id
FROM
    exp_channels c
        LEFT JOIN
    exp_sites s ON s.site_id = c.site_id