How could I either combine this query or send these to the view correctly?

[eluser]ekeretex[/eluser]
In this case, I'd use recursion (a function calling itself). It gives you the advantage of being able to go to any level of subcategories with minimal effort.

With your example, i'd do this in the model:

Code:

function getCategories() {

        $categories = array();

        $this->db->select('id, name, parent_id');

        $this->db->where('type', 'product');

        $this->db->where('active', 'Y');

        //to aid in putting them into the array - parents come first
        $this->db->orderby('parent_id');
    
        $query = $this->db->get('categories');
        
        if ($query->num_rows() > 0) {

            foreach ($query->result() as $category) {

            $categories[$category->parent_id][$category->id] = $category->name;

            }
        }
        return $categories;
    }

In the controller, pass the array to a recursive function and build the html in there.
This is one of the few times I've done html in the controller but I'd rather the function stays in the controller.

Code:

function show () {

    $listing = $this->site_model->getCategories();

    $this->_make_list($listing[0], $listing, 0);

    $this->data['content'] = $this->categories;  
              
    $this->load->view('layout', $this->data);        
}
    
function _make_list($parent, $listing, $depth) {

    foreach ($parent as $id => $category) {

        $this->categories .= '<p>' . str_repeat('-', $depth) . $category . '</p>';

        if (isset($listing[$id])) {

            $this->_make_list($listing[$id], $listing, $depth + 2);

        }
    }
}

Hope that helps