+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL query to get number of posts (/showthread.php?tid=12907)
MySQL query to get number of posts - El Forum - 11-04-2008
[eluser]CodeIgniterNoob[/eluser]
Hi, I have in one of my models, a function:
Code:
function getTopCategories()
{
$this->db->where('parentid',0);
$Q = $this->db->get('categories');
if ($Q->num_rows() > 0)
{
foreach ($Q->result_array() as $row)
{
$data[$row['id']] = $row['name'];
}
}
$Q->free_result();
return $data;
}What Im trying to do is, get a number of live posts associated with each category. Just a number (4) for example.
Can anyone throw me in the right direction? Thanks.
MySQL query to get number of posts - El Forum - 11-04-2008
[eluser]barbazul[/eluser]
Try this:
Code:
function getTopCategories()
{
$this->db->where('parentid',0);
$qty = $this->db->count_all_results('categories'); // <----------------
$Q = $this->db->get('categories')
if ($Q->num_rows() > 0)
{
foreach ($Q->result_array() as $row)
{
$data[$row['id']] = $row['name'];
}
}
$Q->free_result();
return $data;
}MySQL query to get number of posts - El Forum - 11-04-2008
[eluser]Elliot Haughin[/eluser]
There's an elegant way to do it with one query too...
Code:
$this->db->select('c.*');
$this->db->select('COUNT(p.id) as postCount', TRUE);
$this->db->where('parentid', 0);
$this->db->join('posts p', 'p.categoryid = c.id', 'left');
$query = $this->db->get('categories c');
if ( $query->num_rows() > 0 )
{
$categories = $query->result_array();
// Should look like:
//
//
// id: 3
// name: blahblah
// postCount: 15
}I've not tested it, but that looks about right.
Elliot
MySQL query to get number of posts - El Forum - 11-04-2008
[eluser]CodeIgniterNoob[/eluser]
Ok I modified the query that Elliot wrote, because before I was getting SQL errors.
So heres my function now and it works. But it works as before. It doesnt print number of posts.
Code:
function getTopCategories()
{
$this->db->select('c.*');
$this->db->select('COUNT(p.id) as postsCount', TRUE);
$this->db->where('parentid', 0);
$this->db->join('posts p', 'p.category_id = c.id', 'left');
$this->db->group_by('name');
$Q = $this->db->get('categories c');
if ( $Q->num_rows() > 0 )
{
foreach ($Q->result_array() as $row)
{
$data[$row['id']] = $row['name'];
}
}
$Q->free_result();
return $data;
}Ill continue working on this, but if any of you stops by, please drop a line. Thanks.
MySQL query to get number of posts - El Forum - 11-04-2008
[eluser]barbazul[/eluser]
For what I understand, you just want to get the number and not the other data, so change the get() call for the count_all_results() call
It returns a single integer
http://ellislab.com/codeigniter/user-guide/database/active_record.html
Code:
$this->db->select('c.*');
$this->db->select('COUNT(p.id) as postsCount', TRUE);
$this->db->where('parentid', 0);
$this->db->join('posts p', 'p.category_id = c.id', 'left');
$this->db->group_by('name');
$qty = $this->db->count_all_results('categories');MySQL query to get number of posts - El Forum - 11-05-2008
[eluser]CodeIgniterNoob[/eluser]
Its not working. My function selects the categories table and gets the id and name fields. Then the controller prints the name of the category to a view, when you click on the category it then takes you to a page displaying all the posts that belong to that category.
I just want to add a number of published posts on the bottom of each category link, a number of posts which belong to that category.
MySQL query to get number of posts - El Forum - 11-05-2008
[eluser]fesweb[/eluser]
NuclearArt: In your last example, you are not outputting the most important piece of information.
Code:
foreach ($Q->result_array() as $row)
{
$data[$row['id']] = $row['name'];
}You have to pass the resulting postsCount from the query to your data, or it will never work.
Code:
foreach ($Q->result_array() as $row)
{
$data[$row['id']] = $row['name'].' has this many posts: '.$row['postsCount'];
}MySQL query to get number of posts - El Forum - 11-05-2008
[eluser]CodeIgniterNoob[/eluser]
Great it worked. Thank you all for your help.
Here is my function in the model:
Code:
function getTopCategories()
{
$data = array();
$this->db->select('c.*');
$this->db->select('COUNT(p.id) as postsCount', TRUE);
$this->db->where('parentid', 0);
$this->db->join('posts p', 'p.category_id = c.id', 'left');
$this->db->group_by('name');
$Q = $this->db->get('categories c');
if ( $Q->num_rows() > 0 )
{
foreach ($Q->result_array() as $row)
$data[] = array
(
'id' => $row['id'],
'name' => $row['name'],
'postsCount' => $row['postsCount']
);
}
$Q->free_result();
return $data;
}And here is my view:
Code:
if (count($cats))
{
foreach ($cats as $key => $list)
{
echo "<div class='recentposts'>".anchor("welcome/post/".$list['id'],$list['name'])."</div>\n";
echo "<div class='postdetails'>".'Posts: (<span class="red">'.$list['postsCount']."</span>)</div>\n";
}
}Thank you all very much. I just learned alot about CI with this exercise, and will continue to learn.