Code Igniter - Query Join Question

[Lee]

I'm a beginner to PHP and i'm building a little script just to learn a little, using code igniter.

I have the following function;

public function index()
    {
 
$this->db->select('*');
$this->db->from('articles');
$this->db->join('comments', 'comments.article_id = articles.id');
 
$query = $this->db->get();
 
 
$data = array(
              'article_entries' => $query->result_array(),
 
            );
 
$this->load->view('header');
$this->parser->parse('index_page', $data);
$this->load->view('footer');
    }

and the following template;

{article_entries}
<h1>{title}</h1>
<h1>{subtitle}</h1>
<p>{content}</p>
<hr />
{comment} by {username}
{/article_entries}

What happens is, if the article has no comments nothing is selected. But if the article has more than one comment, it is selected over and over again. No doubt i'm making a very silly mistake here, but I was under the impression what I was doing is selecting the articles and then the comments that relate to each article. What actually appears to be happening is i'm selecting the article over and over again for each comment.

What I want to do is select all the articles, then if there is any comments - display those too.

Any help for a new comer to PHP?

 

[SneakyDave]

Your code using:

{article_entries}

and

{article_entries}

Doesn't look like Codeigniter. Is that Smarty templating code?

Never mind, I see you're using the parser template in CI.

You'll probably need to change things so that you have the comments in an array by itself.

Your query result is doing exactly what your query is asking it to do, joining the comments table with your articles table.

 

[SneakyDave]

You might want your template to look like this.

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

But that would require changing your query in your controller. Not knowing your table layout, I'm not sure if it's that easy with the CI template parser you're using.

 

[Lee]

Yup, that sorted it.

See, so simple when you are told what to do. Was literally an "oh yeah, obviously..." moment.

Thanks Dave!

 

[Lee]

For reference it now looks like;

    public function index()
    {
 
$this->db->select('*');
$this->db->from('articles');
 
$query = $this->db->get();
 
$this->db->select('*');
$this->db->from('comments', 'comments.article_id = articles.id');
$query1 = $this->db->get();
 
 
 
$data = array(
              'article_entries' => $query->result_array(),
              'comments' => $query1->result_array(),
            );
 
$this->load->view('header');
$this->parser->parse('index_page', $data);
$this->load->view('footer');
    }

and in the template I have;

{article_entries}
<h1>{title}</h1>
<h1>{subtitle}</h1>
<p>{content}</p>
<hr />
{comments}<div class="comment">{comment} by {username}</div>{/comments}
{/article_entries}

Which works fine. Do you think that what I have done is the correct way to do things?

EDIT: That above selects every comment for every article regardless of article id.... so it doesn't work. Further research is required.

 

[SneakyDave]

I'm not sure it'll give you what you want. Although it's closer, I'm not sure how it works.

Your first result gives you all articles.

Your second result gives you all comments where the id equals the articles.id, which isn't referenced anywhere but the first query.

But I think you're on the right track. Test it with more articles that have their own comments.

Just some general suggestions

• I normally don't do a SELECT * in my queries, I normally only select the columns I need to build the view. Saves memory and processing time.
• Standard practice is to use Models to execute your db calls, although I know that CI allows it in controllers.

 

[Lee]

Yeah, reworked it to use modals for that now. Getting closer to what I want.



Thanks for the help Dave...

 

[Lee]

Just for the record, although not related to the original question - there are some excellent libraries available for codeigniter - it seems like a very powerful framework to me.

I am using flexi-auth to manage registrations and login / permissions right now, which is good.

Next on my to-do list is to finally get these comments sorted, I think I have a way to do that now I have re-jigged how things work.

 

[SneakyDave]

There are a lot of great libraries for CI, especially authentication libraries. I've used it for a number of years for a number of projects.

I'm slowly trying to move to Laravel, it's a bigger learning curve, but allegedly all the cool kids are using it now.