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

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

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How could I either combine this query or send these to the view correctly? (/showthread.php?tid=5451)

How could I either combine this query or send these to the view correctly? - El Forum - 01-22-2008

[eluser]Shpigford[/eluser]
I have a table named 'categories'. Each row has a field called 'parent'. If 'parent' is 0, it's a top-level category, if not...then it's a sub category.

I'm having an issue ultimately querying that and then sending it to the view.

It's obviously pretty easy to pull all the categories:

Code:
function getCategories()

    {

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

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

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

        $query = $this->db->get('categories');

        return $query->result();

    }

Then in my controller I set that with:

Code:
'categories' => $this->categories->getCategories()

Then in the view:

Code:
&lt;?php foreach($categories as $category): ?&gt;

 <p>&lt;?=$category->name?&gt;</p>

&lt;?php endforeach; ?&gt;

But what I ultimately want to pull of is having the top level categories display and then under each top level category have its subcategories listed:

Category 1
--Category 1a
--Category 1b
Category 2
--Category 2a
etc etc

Any suggestions for pulling that off?

How could I either combine this query or send these to the view correctly? - El Forum - 01-22-2008

[eluser]Armchair Samurai[/eluser]
This makes the assumption that the parent column contains a number which refers to the id column.

In the model:

Code:
$this->db->select('x.id, x.name, y.name AS parent');

$this->db->join('categories AS y', 'x.parent = y.id');

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

$this->db->where('x.parent !=', 0);

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

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

$this->db->orderby('x.parent', 'asc');

$query = $this->db->get('categories AS x');

In the view:

Code:
&lt;?php

$parent = '';

foreach ($categories as $category):

if ($category->parent != $parent):

$parent = $category->parent;

echo $parent.'<br />';

endif; ?&gt;

--&lt;?=category->name;?&gt;<br />

&lt;?php endforeach; ?&gt;

How could I either combine this query or send these to the view correctly? - El Forum - 01-22-2008

[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