Join Query Help

[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums...and the awesome world PHP!

The problem you're having is due to the structure of your data.

First of all, you should use a LEFT JOIN.

Code:

$this->db->join('comments', 'comments.article_id = articles.id');

What you have at the moment is an INNER JOIN, which means that any articles that don't have any comments will be excluded from the result set.

Ideally, you need one of set of data for each article, and within each of those, an array of comments. So the array you're passing into the view might look something like this:

Code:

// I'm aware that you'll actually have more fields than I've specified.
Array
(
    [0] => Array
        (
            [article_id] => 1
            [title] => Title 1
            [content] => foo
            [comments] => Array
                (
                    [0] => Array
                        (
                            [comment_id] => 1
                            [user_id] => 1
                            [comment] => Cool blog!
                        )

                    [1] => Array
                        (
                            [comment_id] => 2
                            [user_id] => 2
                            [comment] => Yeah, man. It's AWZM! LOL KK TTFN!
                        )

                )

        )

    [1] => Array
        (
            [article_id] => 2
            [title] => Title 2
            [content] => bar
            [comments] => Array
                (
                    [0] => Array
                        (
                            [comment_id] => 3
                            [user_id] => 1
                            [comment] => This ain't as good as your last blog!
                        )

                    [1] => Array
                        (
                            [comment_id] => 4
                            [user_id] => 2
                            [comment] => Yeah, man. It SUX! Mmmkaaaay.
                        )

                )

        )

)

Basically, you're going to need to loop through the database results first, to put it into a nicer format for your view. Then in your view, you'd do something like this:

Code:

{article_entries}
    <h1>{title}</h1>
    <p>{content}</p>
    <hr />
    {comments}
        {comment} by {user_id}
    {/comments}
{/article_entries}

I've changed a few values, because username won't actually be available. For that, you'll need to join your users table onto your articles table and also your comments table.

I have a dev helper with the following function:

Code:

function dprint_r($arg)
{
    die('<pre>' . print_r($arg, TRUE) . '</pre>');
}

Once the helper has been loaded loaded, you can call it like this:

Code:

dprint_r($some_arg);

and it will halt execution, and dump data on the supplied object in an easy to read format. It comes in handy for inspecting data to see how it looks.

I hope I haven't confused you too much. If you have any questions, please feel free to ask.