Welcome Guest, Not a member yet? Register   Sign In
Fetching related tables
#1

[eluser]Philo01[/eluser]
Hi all Smile

I was wonder what the best practice is to do the following.

We got 2 tables, categories and posts like so:

Categories
id
name

Posts
id
category_id
title
content

Now at the moment I often end up running models that run the query's in the view files to get something organized.
So to give you a better example I've replaced the models with the actual query's that are being executed by these models.

Code:
<?php
foreach ($categories->result() as $c)
{
    echo '<div id="cat_'.$c->id.'"> <h2>' . $c->name . '</h2>';
    
        $this->db->where('category_id' , $c->id);
        $posts = $this->db->get('posts');
        
        foreach ($posts->result() as $p)
        {
            echo '<p>'.$p->title.'</p>';
        }
    
    echo '</div>';
}
?&gt;

This works, but I'm sure this is not the way.
One other thing I did is joining the tables, but was unable to create the same markup as above.

Can someone point me in the good direction! =)

Thanks
#2

[eluser]Jaketoolson[/eluser]
Try this SQL:
Code:
"SELECT id.c, id.name, title.p FROM Categories c
INNER JOIN Posts p ON p.category_id = c.id
GROUP BY id.c"
#3

[eluser]Philo01[/eluser]
Thanks! Already tried that, but it only returns 1 post from each category. And I want to be able to markup the groups in seperate divs.
#4

[eluser]Jaketoolson[/eluser]
You could create the query and set the $vars in one loop, then run another loop to create the code.

Code:
$sql="SELECT id.c, id.name, title.p FROM Categories c
INNER JOIN Posts p ON p.category_id = c.id";
foreach ($query->result() as $row)
{
    $cat_id = $row->id;
    $post_title = $row->title;
    
    $datas[$cat_id][] = $post_title;
}

foreach ($datas as $key)
{
    echo '<div id="cat_'.key($key).'">';
    foreach ($key as $k => $v)
    {
        echo '<p>'.$v.'</p>';
    }
    echo '</div>';
}
#5

[eluser]Basketcasesoftware[/eluser]
It might be work your while going over to DataMapper ORM 1.8
Your table structure would change somewhat though. It would go from:
Quote:Categories
id
name

Posts
id
category_id
title
content

To
Quote:Categories
id
name

Posts
id
title
content

And your models would become:
Code:
class Post extends DataMapper
{
var $has_one=array("category");

function __construct($ID=NULL)
{
  parent::__construct($ID);
}
}

Code:
class Category extends DataMapper
{
var $table="categories";
var $has_many=array("post");

function __construct($ID=NULL)
{
  parent::__construct($ID);
}
}

DataMapper handles generating the queries for the table joins very well.

And as for the query (which lives in the controller in this example - typically that's the case for DM):
Code:
$c=new Category();
$c->get();
foreach($c as $category)
{
echo '<div id="cat_'.$category->id.'"> <h2>' . $category->name . '</h2>';

foreach($category->post as $post)
{
  echo '<p>'.$post->title.'</p>';
}
}
#6

[eluser]InsiteFX[/eluser]
Here is my code, may work for you
Code:
// --------------------------------------------------------------------

    /**
     * get_all_posts_by_category
     *
     * Description:
     *
     * @access    public
         * @param       string - $category_id
     * @return    array
     */
    public function get_all_posts_by_category($category_id)
    {
        $data = array();

        $this->db->where("category_id", $category_id);
        $this->db->where('status', 'published');

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

        if ($query->num_rows() > 0)
        {
            foreach ($query->result_array() as $row)
            {
                $data[] = $row;
            }
        }

        $query->free_result();

        return $data;
    }

InsitefX




Theme © iAndrew 2016 - Forum software by © MyBB