Login

03-06-2010, 01:44 AM

[eluser]MT206[/eluser]
Ok I know this shouldn't be as hard as I am making it but I have been having a lot of trouble getting this simple category system working. Just like any job/marketplace/product website, I will have multiple categories and then sub categories. I have a categories table with the fields: 'id', 'title', 'parent_id'. I would like to have the routes be like:

Top level categories:
mysite.com/categories
Sub-level categories:
mysite.com/categories/arts_crafts
Sub-sub categories:
mysite.com/categories/arts_crafts/painting

The problems I am facing are:
1) I don't know how to get the parent id for an item if the title is not unique
2) If a title is something like 'arts & crafts' I have been converting it into a proper url using something like: url_title(strtolower($row['title']),'underscore'). The problem with this is that it strips the '&' and as such I cannot find the id again.

Here is my code for reference:

MODEL:

Code:
function get_categories($parent_id) {

        $this->db->select('title')->from('categories')->where('parent_id', $parent_id);

        $this->db->order_by('title', 'asc');

        $category = $this->db->get();

        $result = $category->result_array();

        return $result;

    }

CONTROLLER:

Code:
public function categories($id='0')

    {

        $this->load->helper('url');

        $this->load->helper('categories');

        $this->load->model('categories_model');

        $menu = $this->categories_model->get_categories($id);

        $data['menu'] = buildMenu($menu);

        $this->load->view('categories_view', $data);

    }

HELPER:

Code:
function buildMenu($categories){

    $menu = "<ul class='cat_menu'>";

    foreach($categories as $row){

        $menu .= "<li>" . anchor( current_url() . "/" . url_title(strtolower($row['title']),'underscore'), $row['title']) . "</li>"; 

    }

    return $menu . "</ul>";

}

VIEW:

Code:
&lt;html&gt;

&lt;head&gt;

    &lt;title&gt;Categories View&lt;/title&gt;

&lt;/head&gt;

&lt;body&gt;

    &lt;?php echo anchor(base_url(), 'Home'); ?&gt;

    <h1>Categories</h1>

    &lt;?php echo $menu; ?&gt;

&lt;/body&gt;

&lt;/html&gt;

03-06-2010, 03:05 AM

[eluser]xeroblast[/eluser]
i think you need to reverse the url_title() to get the correct string for the category name. if i am right then use this seourl helper i created...

urltitle() - same as url_title() with additional features...
rev_urltitle() - reverse the output of the urltitle()...

only few characters that i detected so far... so if you have any then add it in the array and the reverse array too...

03-06-2010, 12:24 PM

[eluser]MT206[/eluser]
Yeah I was thinking I would have to do something like that but its great you already made something. Thanks for sharing!

I still am not exactly sure how I would get values that are not unique however.
For example:
Metal>Iron
Household Appliances>Iron

I am not sure how to get a particular one because I need to use the title to find the parent id.

03-06-2010, 12:44 PM

[eluser]helmutbjorg[/eluser]
In my applications I put an extra field in the database table like so

categories
----------
id
name
url_name
parent_id

Where url_name is the strtolower and url_title of the name. Some people refer to this field as the slug. That way you can search on it and still display the actual name.

However to search for unique values you may need to include the id in with the name in the url and then explode the parts out. For example

Code:
mysite.com/123-appliances/8782-stovetops

Then the first part of each category is always the id and you can use that to search for the products. However this makes it appear a little less clean but will still be good for seo.

One other way is to search on the parent too (event if the category doesn't have one). And disallow users from adding two categories with the same name to the one parent. This way you keep your urls clean and can be certain you are finding the correct category.

For the example above your search query would be like:

Code:
Find the category with the url_name 'stovetops' who has a parent with a url_name of 'appliances'

Obviously you would join the table to itself via the parent_id so you can search on the parents url_name.

Hope that helps.

03-07-2010, 02:38 AM

[eluser]MT206[/eluser]
Very interesting ideas. I want to have the routes be clean so the first option is out but I like the second idea. For joining tables with active record I am assuming you would do something like this(mostly taken from another post):

Code:
$this->db->join('categories c2', 'c2.parent_id = c.parent_id', 'left');

$this->db->order_by('c.title', 'asc');

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

03-07-2010, 03:12 AM

[eluser]helmutbjorg[/eluser]
Try to use the user guide first before using random code from other peoples posts. That way you will understand more rather than just guessing.

This should help you out:

Code:
$this->db->select('child.*');

$this->db->from('categories child');

$this->db->join('categories parent', 'parent.id = child.parent_id', 'left');

$this->db->where('child.url_name', 'stovetops');

$this->db->where('parent.url_name', 'appliances');

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

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

    echo $category->name;

}

03-08-2010, 01:40 AM

[eluser]MT206[/eluser]
Great. This is exactly what I needed. I eliminated the where statement:

Code:
$this->db->where('child.url_name', 'stovetops');

because I want to return all the results for the parent.

Now I just have to hook this into the posts and I will have a fully functioning category-post system. I think just adding a foreign key referring to the category id in the post table and having some validation that makes sure that people don't post in the top level category will suit what I need just fine.

Thank you both for all your help.