[eluser]codejack[/eluser]
Hi, guys.
This is a pretty straight forward question. My brain doesn't seem to be working this morning!
I am setting up a simple blog for my website, and I'm trying to add a "total comments" line on the blog's homepage under each post. A pretty common function. I have a separate table in my database for comments called "blog_comment" with the following columns:
id
post_id
user_id
date_posted
content
I understand I need to execute a query on the "blog_comment" table and return the num_rows() for how many rows contain each post_id, but I'm not sure how to add it into my existing code, which is:
CONTROLLER:
Code: class Blog extends CI_Controller
{
function index()
{
// If user has logged in.
if ($this->session->userdata('username'))
{
$this->load->model('blog_model');
$data['current_user'] = $this->user_model->current_user();
$data['posts'] = $this->blog_model->get_all_posts();
$this->load->view('blog_view', $data);
}
else
{
// Redirect to the login page.
redirect('login');
}
}
}
MODEL:
Code: class Blog_model extends CI_Model
{
function get_all_posts()
{
$this->db->select('blog_post.id, blog_post.user_id, blog_post.title, blog_post.category, blog_post.date_published, blog_post.content, user.username');
$this->db->from('blog_post');
$this->db->where('status_published', 1);
$this->db->order_by('date_published', 'desc');
$this->db->join('user', 'user.id = blog_post.user_id');
$query = $this->db->get();
if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
$data[] = $row;
}
return $data;
}
}
}
Any ideas?
Thanks in advance.
- Tim.
[eluser]vbsaltydog[/eluser]
there are several ways to accomplish your goal.
loop through the fetched blogs in the controller and fetch the comments for each one.
or
join the comments table to the blog table in the blog query.
etc.
Use the PHP count() function on the comments array to get a count of the comments found for blog x.
[eluser]codejack[/eluser]
Yeah, my first thought was to join the comments table to my existing query. I'm just not sure how to pull the comment count though, still a bit of a CI noob.
[eluser]vbsaltydog[/eluser]
Don't pull the comment count. Pull the comments and the run count() on the results array.
[eluser]Aken[/eluser]
[quote author="vbsaltydog" date="1327975054"]Don't pull the comment count. Pull the comments and the run count() on the results array.[/quote]
Curious why you'd recommend a method that requires two additional steps (a SQL query, then a PHP count() function) instead of just amending the original query to include the number of comments?
[eluser]vbsaltydog[/eluser]
[quote author="Aken" date="1327981794"]Curious why you'd recommend a method that requires two additional steps (a SQL query, then a PHP count() function) instead of just amending the original query to include the number of comments?[/quote]
There are no additional steps. One method is to pull the count from the db and one method is not to pull the count in the db but to count the resulting array. Either way, there is only one counting method.
[eluser]codejack[/eluser]
Okay, I've made some changes but it's not working.
What am I doing wrong here?
Code: class Blog extends CI_Controller
{
function index()
{
// If user has logged in.
if ($this->session->userdata('username'))
{
$this->load->model('blog_model');
$data['current_user'] = $this->user_model->current_user();
$data['posts'] = $this->blog_model->get_all_posts();
$this->load->view('blog_view', $data);
}
else
{
// Redirect to the login page.
redirect('login');
}
}
}
MODEL:
Code: class Blog_model extends CI_Model
{
function get_all_posts()
{
$this->db->select('blog_post.id, blog_post.user_id, blog_post.title, blog_post.category, blog_post.date_published, blog_post.content, user.username');
$this->db->from('blog_post');
$this->db->where('status_published', 1);
$this->db->order_by('date_published', 'desc');
$this->db->join('user', 'user.id = blog_post.user_id');
$query = $this->db->get();
if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
$data[] = $row;
$this->db->select('id');
$this->db->from('blog_comment');
$this->db->where('post_id', $row->id);
$query = $this->db->get();
$data['comments'] = $query->num_rows();
}
return $data;
}
}
}
[eluser]vbsaltydog[/eluser]
Code: class Blog_model extends CI_Model
{
function get_all_posts()
{
$this->db->select('blog_post.id, blog_post.user_id, blog_post.title, blog_post.category, blog_post.date_published, blog_post.content, user.username');
$this->db->from('blog_post');
$this->db->where('status_published', 1);
$this->db->order_by('date_published', 'desc');
$this->db->join('user', 'user.id = blog_post.user_id');
$query = $this->db->get();
// use this to see your sql statement // print $this->db->last_query();
// use this to see your query results // var_dump($query);
if ($query->num_rows() > 0)
{
foreach ($query->result() as $row)
{
$data[] = $row;
// You should look into PHP5 method chaining
$data[]['comments'] = $this->db->get_where('blog_comment', array('post_id' => $row->id))->num_rows();
}
// use this to see your data results // var_dump($data);
return $data;
}
}
}
[eluser]codejack[/eluser]
Thanks for taking the time to edit my code. It's still not working though. Here is the array structure being returned using your code:
Code: Array
(
[0] => stdClass Object
(
[id] => ""
[user_id] => ""
[title] => ""
[category] => ""
[date_published] => ""
[content] => ""
[username] => ""
)
[1] => Array
(
[comments] => ""
)
[2] => stdClass Object
(
[id] => ""
[user_id] => ""
[title] => ""
[category] => ""
[date_published] => ""
[content] => ""
[username] => ""
)
[3] => Array
(
[comments] => ""
)
And so on.
There are values in the arrays, I just took them out for readability.
Do I need to change something in my controller perhaps?
Code: class Blog extends CI_Controller
{
function index()
{
// If user has logged in.
if ($this->session->userdata('username'))
{
$this->load->model('blog_model');
$data['current_user'] = $this->user_model->current_user();
$data['posts'] = $this->blog_model->get_all_posts();
$this->load->view('blog_view', $data);
}
else
{
// Redirect to the login page.
redirect('login');
}
}
}
[eluser]Aken[/eluser]
That's a very inefficient way of doing things. You can retrieve all of the information you need (blog posts + comment total for each) in a single query, without any follow-up PHP required.
codejack, try changing your Model function to the following:
Code: public function get_all_posts()
{
$query = $this->db->select('blog_post.id, blog_post.user_id, blog_post.title, blog_post.category, blog_post.date_published,
blog_post.content, user.username, COUNT(blog_comment.comment_id) AS total_comments', false)
->from('blog_post')
->join('user', 'user.id = blog_post.user_id')
->join('blog_comment', 'blog_comment.post_id = blog_post.id', 'left')
->where('blog_post.date_published', 1)
->group_by('blog_comment.post_id')
->order_by('blog_post.date_published', 'desc')
->get();
return $query->result_array();
}
1) This query will return a column "total_comments" that contains the number of columns for that post, along with the other normal columns like title, username, etc.
2) The way your model code was originally set up was a bit redundant. You can return an array by simply getting result_array(). Also, because you were checking for num_rows(), if there are no results, your function would return nothing at all. That will likely lead to code issues in the future if you have no results for whatever reason. You'll always want to return something, even if it's an empty array.
That query is untested since I don't have access to your database, so if it does not work, please use $this->db->last_query() to show us what query it is generating, so we can figure out what might be wrong.
|
