• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Join Query Help

#11
[eluser]TheFuzzy0ne[/eluser]
./application/controllers/blog.php:
Code:
<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Blog extends CI_Controller {

    function __construct()
    {
        parent::__construct();
        
        // Load the blog model. We do this in the constructor to save repeating
        // it throughout our controller.
        $this->load->model('blog_m');
        $this->load->library('parser');
    }
    
    function index()
    {
        // Grab our view data from the blog model.
        $data['article_entries'] = $this->blog_m->index();
        
        // Load the views.
        //$this->load->view('header');
        $this->parser->parse('blog/index_view', $data);
        //$this->load->view('footer');
    }
}

/* End of file blog.php */
/* Location: ./application/controllers/blog.php */

Note that with the example above, it will output every article and every comment for each article all on one page. It's up to you to implement any limiting you want, so you don't end up with hundreds of articles and thousands of comments all on a single page.

./application/views/blog/index_view.php
Code:
...
<body>
{article_entries}
    <h1>{a_title} by {a_username} [{a_created_on}]</h1>
    <p>{a_content}</p>
    <hr />
    {comments}
        {c_comment} by {c_username} [{c_created_on}]<br />
    {/comments}
{/article_entries}
&lt;/body&gt;
...

One thing I don't particularly like about CodeIgniter's parser, is that it's too simple. There's not support for if-statements in your view, which makes it difficuly. When you run the example, you'll see that the article 4 has no comments (that's because the guy who wrote it is an a-hole). Instead of not showing any comment, the placeholders remain.

If you want to use a template engine, I would suggest going with a more advanced one, such as Smarty. Personally, I just prefer using plain old PHP in my views.

#12
[eluser]TheFuzzy0ne[/eluser]
Here's the output of dprint_r() on the array returned from the database:
Code:
Array
(
    [0] => Array
        (
            [a_id] => 1
            [a_user_id] => 1
            [a_content] => User1's first blog entry.
            [a_title] => Article 1
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 1
            [c_comment] => User2's first comment on User1's first blog entry.
            [c_user_id] => 2
            [c_username] => user2
            [c_created_on] => 2013-01-01 00:00:00
        )

    [1] => Array
        (
            [a_id] => 1
            [a_user_id] => 1
            [a_content] => User1's first blog entry.
            [a_title] => Article 1
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 2
            [c_comment] => User3's first comment on User1's first blog entry.
            [c_user_id] => 3
            [c_username] => user3
            [c_created_on] => 2013-01-01 00:00:00
        )

    [2] => Array
        (
            [a_id] => 1
            [a_user_id] => 1
            [a_content] => User1's first blog entry.
            [a_title] => Article 1
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 3
            [c_comment] => User4's first comment on User1's first blog entry.
            [c_user_id] => 4
            [c_username] => user4
            [c_created_on] => 2013-01-01 00:00:00
        )

    [3] => Array
        (
            [a_id] => 1
            [a_user_id] => 1
            [a_content] => User1's first blog entry.
            [a_title] => Article 1
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 4
            [c_comment] => User3's second comment on User1's first blog entry.
            [c_user_id] => 3
            [c_username] => user3
            [c_created_on] => 2013-01-01 00:00:00
        )

    [4] => Array
        (
            [a_id] => 2
            [a_user_id] => 1
            [a_content] => User1's second blog entry.
            [a_title] => Article 2
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 5
            [c_comment] => User4's first comment on User1's second blog entry.
            [c_user_id] => 4
            [c_username] => user4
            [c_created_on] => 2013-01-01 00:00:00
        )

    [5] => Array
        (
            [a_id] => 2
            [a_user_id] => 1
            [a_content] => User1's second blog entry.
            [a_title] => Article 2
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 6
            [c_comment] => User3's first comment on User1's second blog entry.
            [c_user_id] => 3
            [c_username] => user3
            [c_created_on] => 2013-01-01 00:00:00
        )

    [6] => Array
        (
            [a_id] => 3
            [a_user_id] => 1
            [a_content] => User1's third blog entry.
            [a_title] => Article 3
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 7
            [c_comment] => User4's first comment on User1's third blog entry.
            [c_user_id] => 4
            [c_username] => user4
            [c_created_on] => 2013-01-01 00:00:00
        )

    [7] => Array
        (
            [a_id] => 3
            [a_user_id] => 1
            [a_content] => User1's third blog entry.
            [a_title] => Article 3
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 8
            [c_comment] => User5's first comment on User1's third blog entry.
            [c_user_id] => 5
            [c_username] =>
            [c_created_on] => 2013-01-01 00:00:00
        )

    [8] => Array
        (
            [a_id] => 3
            [a_user_id] => 1
            [a_content] => User1's third blog entry.
            [a_title] => Article 3
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user1
            [c_comment_id] => 9
            [c_comment] => User4's second comment on User1's third blog entry.
            [c_user_id] => 4
            [c_username] => user4
            [c_created_on] => 2013-01-01 00:00:00
        )

    [9] => Array
        (
            [a_id] => 4
            [a_user_id] => 2
            [a_content] => User2's first blog entry.
            [a_title] => Article 4
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user2
            [c_comment_id] => 10
            [c_comment] => User1's first comment on User2's first blog entry.
            [c_user_id] => 1
            [c_username] => user1
            [c_created_on] => 2013-01-01 00:00:00
        )

    [10] => Array
        (
            [a_id] => 5
            [a_user_id] => 3
            [a_content] => User3's first blog entry.
            [a_title] => Article 5
            [a_created_on] => 2013-01-01 00:00:00
            [a_username] => user3
            [c_comment_id] =>
            [c_comment] =>
            [c_user_id] =>
            [c_username] =>
            [c_created_on] =>
        )

)

#13
[eluser]TheFuzzy0ne[/eluser]
And here's the data after I formatted it:
Code:
Array
(
    [1] => Array
        (
            [a_id] => 1
            [a_user_id] => 1
            [a_username] => user1
            [a_created_on] => 2013-01-01 00:00:00
            [a_title] => Article 1
            [a_content] => User1's first blog entry.
            [comments] => Array
                (
                    [0] => Array
                        (
                            [c_id] => 1
                            [c_user_id] => 2
                            [c_username] => user2
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User2's first comment on User1's first blog entry.
                        )

                    [1] => Array
                        (
                            [c_id] => 2
                            [c_user_id] => 3
                            [c_username] => user3
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User3's first comment on User1's first blog entry.
                        )

                    [2] => Array
                        (
                            [c_id] => 3
                            [c_user_id] => 4
                            [c_username] => user4
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User4's first comment on User1's first blog entry.
                        )

                    [3] => Array
                        (
                            [c_id] => 4
                            [c_user_id] => 3
                            [c_username] => user3
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User3's second comment on User1's first blog entry.
                        )

                )

        )

    [2] => Array
        (
            [a_id] => 2
            [a_user_id] => 1
            [a_username] => user1
            [a_created_on] => 2013-01-01 00:00:00
            [a_title] => Article 2
            [a_content] => User1's second blog entry.
            [comments] => Array
                (
                    [0] => Array
                        (
                            [c_id] => 5
                            [c_user_id] => 4
                            [c_username] => user4
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User4's first comment on User1's second blog entry.
                        )

                    [1] => Array
                        (
                            [c_id] => 6
                            [c_user_id] => 3
                            [c_username] => user3
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User3's first comment on User1's second blog entry.
                        )

                )

        )

    [3] => Array
        (
            [a_id] => 3
            [a_user_id] => 1
            [a_username] => user1
            [a_created_on] => 2013-01-01 00:00:00
            [a_title] => Article 3
            [a_content] => User1's third blog entry.
            [comments] => Array
                (
                    [0] => Array
                        (
                            [c_id] => 7
                            [c_user_id] => 4
                            [c_username] => user4
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User4's first comment on User1's third blog entry.
                        )

                    [1] => Array
                        (
                            [c_id] => 8
                            [c_user_id] => 5
                            [c_username] =>
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User5's first comment on User1's third blog entry.
                        )

                    [2] => Array
                        (
                            [c_id] => 9
                            [c_user_id] => 4
                            [c_username] => user4
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User4's second comment on User1's third blog entry.
                        )

                )

        )

    [4] => Array
        (
            [a_id] => 4
            [a_user_id] => 2
            [a_username] => user2
            [a_created_on] => 2013-01-01 00:00:00
            [a_title] => Article 4
            [a_content] => User2's first blog entry.
            [comments] => Array
                (
                    [0] => Array
                        (
                            [c_id] => 10
                            [c_user_id] => 1
                            [c_username] => user1
                            [c_created_on] => 2013-01-01 00:00:00
                            [c_comment] => User1's first comment on User2's first blog entry.
                        )

                )

        )

    [5] => Array
        (
            [a_id] => 5
            [a_user_id] => 3
            [a_username] => user3
            [a_created_on] => 2013-01-01 00:00:00
            [a_title] => Article 5
            [a_content] => User3's first blog entry.
            [comments] => Array
                (
                )

        )

)

Hope this helps.

#14
[eluser]Lee.[/eluser]
I'll report back tonight as I have made some changes to my system and will need to adapt it to work with what I have and I am at work right now so don't have access to those files.

I get the general gist of what you have done above.

Thank you very much for your time, very helpful.

#15
[eluser]Lee.[/eluser]
Getting there.

I have modified your code so that I can use a "slug" field to generate the url part.

I have this in my controller;

Code:
&lt;?php
class News_model extends CI_Model {

public function get_news($slug = FALSE)
{
if ($slug === FALSE)
{
// Grab the data from the database.
        $result = $this->db
            ->select(array(
                'a.article_id as a_id',
                'a.user_id AS a_user_id',
                'a.content AS a_content',
                'a.slug AS a_slug',
                'a.title AS a_title',
                'a.created_on as a_created_on',
                'au.username AS a_username',
                'c.comment_id AS c_comment_id',
                'c.comment AS c_comment',
                'c.user_id AS c_user_id',
                'cu.username AS c_username',
                'c.created_on AS c_created_on'
            ))
            ->from('articles AS a')
            ->join('comments AS c', 'a.article_id = c.article_id', 'left')
            ->join('users AS au', 'au.user_id = a.user_id', 'left')
            ->join('users AS cu', 'cu.user_id = c.user_id', 'left')
            ->get()->result_array();

        // Do we have a result?
        if ( ! $result)
        {
            return FALSE;
        }
        
        // Now we need to format the array for the view.
        $ret = array();
                foreach ($result as &$res)
        {
            $article_id = $res['a_id'];
            
            // If the article hasn't been added to the return array, add it now.
            if ( ! isset($ret[$article_id]))
            {
                $ret[$article_id] = array(
                    'a_id' => $article_id,
                    'a_user_id' => $res['a_user_id'],
                    'a_username' => $res['a_username'],
                    'a_created_on' => $res['a_created_on'],
                    'a_title' => $res['a_title'],
                    'a_content' => $res['a_content'],
                    'a_slug' => $res['a_slug'],

                    'a_comments' => array(),
                );
            }
        }
        // Free up some memory.
        unset($result);
        
        // Return the array we just built.
        return $ret;

}
  
  $query = $this->db->get_where('articles', array('slug' => $slug));
  // Grab the data from the database.
        $result = $this->db
            ->select(array(
                'a.article_id as a_id',
                'a.user_id AS a_user_id',
                'a.content AS a_content',
                'a.slug AS a_slug',
                'a.title AS a_title',
                'a.created_on as a_created_on',
                'au.username AS a_username',
                'c.comment_id AS c_comment_id',
                'c.comment AS c_comment',
                'c.user_id AS c_user_id',
                'cu.username AS c_username',
                'c.created_on AS c_created_on'
            ))
            ->from('articles AS a')
            ->join('comments AS c', 'a.article_id = c.article_id', 'left')
            ->join('users AS au', 'au.user_id = a.user_id', 'left')
            ->join('users AS cu', 'cu.user_id = c.user_id', 'left')
            ->get_where('articles', array('a.slug' => $slug))->result_array();

        // Do we have a result?
        if ( ! $result)
        {
            return FALSE;
        }
        
        // Now we need to format the array for the view.
        $ret = array();
        
        foreach ($result as &$res)
        {
            $article_id = $res['a_id'];
            
            // If the article hasn't been added to the return array, add it now.
            if ( ! isset($ret[$article_id]))
            {
                $ret[$article_id] = array(
                    'a_id' => $article_id,
                    'a_user_id' => $res['a_user_id'],
                    'a_username' => $res['a_username'],
                    'a_created_on' => $res['a_created_on'],
                    'a_title' => $res['a_title'],
                    'a_content' => $res['a_content'],
                    'a_slug' => $res['a_slug'],
                    'a_comments' => array(),
                );
            }
            
            // If we have a comment, add it to the article.
            if ($res['c_comment'])
            {
                $ret[$article_id]['comments'][] = array(
                    'c_id' => $res['c_comment_id'],
                    'c_user_id' => $res['c_user_id'],
                    'c_username' => $res['c_username'],
                    'c_created_on' => $res['a_created_on'],
                    'c_comment' => $res['c_comment'],
                );
            }
        }
        
        // Free up some memory.
        unset($result);
        
        // Return the array we just built.
        return $ret;

}

public function set_news()
{
$this->load->helper('url');

$slug = url_title($this->input->post('title'), 'dash', TRUE);

$data = array(
  'title' => $this->input->post('title'),
  'slug' => $slug,
  'content' => $this->input->post('content')
);

return $this->db->insert('news', $data);
}
}


#16
[eluser]Lee.[/eluser]
and my controller:

Code:
&lt;?php
class News extends CI_Controller {

public function __construct()
{
parent::__construct();
$this->load->model('news_model');
}
    
    function index()
    {
        // Grab our view data from the blog model.
        $data['article_entries'] = $this->news_model->get_news();
        
        // Load the views.
  $this->load->view('templates/header');
        $this->parser->parse('news/index', $data);
  $this->load->view('templates/footer');
    }

public function view($slug)
{
        // Grab our view data from the blog model.
        $data['article_entries'] = $this->news_model->get_news($slug);
        
        // Load the views.
  $this->load->view('templates/header');
        $this->parser->parse('news/view', $data);
  $this->load->view('templates/footer');
}



public function create()
{
$this->load->helper('form');
$this->load->helper('ckeditor');
$this->load->library('form_validation');


$data['title'] = 'Create a news item';

$this->form_validation->set_rules('title', 'Title', 'required');
$this->form_validation->set_rules('text', 'text', 'required');

if ($this->form_validation->run() === FALSE)
{
  $this->load->view('templates/header', $data);
  $this->load->view('news/create');
  $this->load->view('templates/footer');
  
}
else
{
  $this->news_model->set_news();
  $this->load->view('news/success');
}
}

}

#17
[eluser]Lee.[/eluser]
What happens now is the comments are shown for each individual article not on the over view page (as intended) but they show up for the total number of articles.

Ie - I have 5 articles in total. Article 1 has 4 comments. Those 4 comments show up on article one as intended but 5 times.

Stuck...haha...

#18
[eluser]TheFuzzy0ne[/eluser]
Your code is difficult to follow, because it's not formatted correctly. At the moment, I've only got my mobile phone, so when I get back to my PC, I can neaten it up a little, I'll see if I can help you out a bit more.

I do have a few observations to make, however. Perhaps they will help you?

1) Try to format your code correctly, and use 4 spaces instead of a tab to ensure that your code displays correctly on these forums. It's also considered good practice.
2) Keep your code [url="http://en.wikipedia.org/wiki/Don't_repeat_yourself"]DRY[/url]. If you repeat blocks of code, your app will be more difficult to maintain and more difficult to follow. If you require a block of code to be repeated, that's a good indication that it should be encapsulated into a function/method so it can b re-used.
3) Before writing any code, write your comments. It will help you understand the code flow before you even write any code. For example:
Code:
// Make sure we have a parameter. Return FALSE if we don't.

// Check that the entry exists in the database. Return FALSE if it's not.

// Loop through the result and build the return array.

// Return the return array.

It's a very simple example, but it's pretty clear how the code will flow. Once everything makes sense, then you can add the code for each comment.

Hope this helps.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.