Hi Query per pagination problem.

[eluser]Miguel Diaz[/eluser]
Hi I am new working with databases and I am trying to make a blog and use the pagination library, If i use the get property works perfect but like I want to count the comments of each article when I change this to select doesnt work can someone help me please.

This is my controller

Code:

class Blog extends Controller {
    
    function blog()
    {
    parent::Controller();
    
    $this->load->model('data_model');
    }
    
    function index()
    {
        $this->load->library('pagination');
        
        $config['base_url'] = base_url().'blog/index/';
        $config['total_rows'] = $this->db->count_all('articles');
        $config['per_page'] = 2;
        
        $this->pagination->initialize($config);
        
        $op['pagination_links'] = $this->pagination->create_links();
        
        $tags = $this->uri->segment(1);
        $op['tags'] = $this->data_model->cargarTags($tags);
        
        
        $op['blog'] = $this->db->query("select f.entry_id as 'entry',
        a.title as 'Title',
        a.url as 'Url',
        a.description as 'Description',
        a.date as 'Fecha',
        a.graphics as 'Graphics',
        count(entry_id) as Comments
        from articles a left join
        forocomments f
        on a.url=f.entry_id        
        group by idArticles
        desc limit 3", $config['per_page'], $this->uri->segment(3));
        
        //This work perfect but i need to count my forocomments
        //$this->db->order_by('idArticles', 'DESC');
        //$op['blog'] = $this->db->get('articles', $config['per_page'], $this->uri->segment(3));
        
        //tags de scrypt extra por pagina//
        $op['js']= '';
        
        //Menu home este es diferente al contenido de la interior//
        $op['nav'] = $this->data_model->cargarNavInteriores();
        
        // Vista de Template
        $op['content'] = 'blog-index';
        $this->load->view('includes/template-content', $op);
        
    }
    
}

and this is my view where or my articles are display.

Code:

<? foreach($blog->result() as $articulo) : ?>
              <dt>&lt;?=anchor('articulos/'.$articulo->Url, $articulo->Title);?&gt;</dt>
              <dd class="pict"><img >Graphics; ?&gt;" alt="ultimas noticias" /></dd>
              <dd class="text"><p>&lt;?=character_limiter($articulo->Description, 550)?&gt;</p>
              <em>&lt;?=$articulo->Fecha;?&gt;</em>
              <span>&lt;?=$articulo->Comments;?&gt;</span>
              &lt;?=anchor('blog/'.$articulo->Url, 'read more', 'class="readmore"');?&gt;
              </dd>
&lt;?php endforeach; ?&gt;

I hope someone can help me
Thanks in advance

[eluser]Mischievous[/eluser]
Will most likely have to do 2 separate queries.

Code:

$op['blog'] = $this->db->query("select a.title as 'Title',
        a.url as 'Url',
        a.description as 'Description',
        a.date as 'Fecha',
        a.graphics as 'Graphics',
        from articles a    
        group by idArticles
        desc limit 3", $config['per_page'], $this->uri->segment(3));
//or whatever you want:
$this->db->get();

$where = "";
        foreach($op['blog'] as $index = $article)
        {
            if($index != 0)
            {
                $where .= ',';
            }
            $where .= $article['url']
        }
        $this->db->query("SELECT count(entry_id) FROM forocomments f WHERE entry_id IN (".$where.") GROUP BY entry_id");

[eluser]Miguel Diaz[/eluser]
Hi Thank you for your answer i try to put it but it send me a syntax error I put it like this

Code:

$op['blog'] = $this->db->query("select f.entry_id as 'entry',
        a.title as 'Title',
        a.url as 'Url',
        a.description as 'Description',
        a.date as 'Fecha',
        a.graphics as 'Graphics',
        count(entry_id) as Comments
        from articles a left join
        forocomments f
        on a.url=f.entry_id        
        group by idArticles
        desc limit 3", $config['per_page'], $this->uri->segment(3));
        
        $this->db->get();
        $where = "";
                         foreach($op['blog'] as $index = $article)
                         {
                         if($index != 0)
                         {
                         $where .= ',';
                         }
                         $where .= $article['url']
                         }
               $this->db->query("SELECT count(entry_id) FROM forocomments f WHERE entry_id IN (".$where.") GROUP BY entry_id");

Did I put it right?

[eluser]Mischievous[/eluser]
Honestly, I don't know... should be something like that... i was just putting stuff down for reference to go off... didn't test what I wrote. Also I dont use the db get like you are, I run something like this:

Code:

$query = 'this is my query';
if($result = $this->db->query($query)){
if($result->num_rows > 0){
foreach($result->result() as $index => $row){
if($index != 0)
{
$where .= ',';
}
$where .= $row->url;
}
$query = sprintf("SELECT count(entry_id) FROM forocomments f WHERE entry_id IN ($where) GROUP BY entry_id", $this->db->escape($where));
$this->db->query($query);

[eluser]Miguel Diaz[/eluser]
Thanks Mischievous but I try it also that way and it doesnt work to me due the pagination library. I keep trying if someone more knows how can I do it I will really appreciate.

Thanks again

[eluser]louisl[/eluser]
You don't have limit correct in your sql.

desc limit 3", $config['per_page'], $this->uri->segment(3));

You will need two queries.

One for the actual data eg.

Code:

$query = SELECT id, whatever FROM table_name WHERE column_name = 'something' LIMIT $start_from_record, $number_of_records_to_get;

One to count the total records that query would have without any limit eg.

Code:

$count = SELECT id, whatever FROM table_name WHERE column_name = 'something';