Welcome Guest, Not a member yet? Register   Sign In
How could I either combine this query or send these to the view correctly?
#1

[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:
<?php foreach($categories as $category): ?>
<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?
#2

[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;
#3

[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




Theme © iAndrew 2016 - Forum software by © MyBB