Need Help with Pagination

[eluser]vincej[/eluser]
Hi - I've read the user guide and I get the concept, but I am doing something stupid in the execution. Essentially my problem is finding the total number of rows in my query. My existing query returns an array. So if I could count the number of rows in the array, that would be a start but I have not yet found a function which counts the rows in an array.

Alternatively I could just do a num_rows() or a db->count_all_results(), on the query, but I have not found a way of return that value and my array.

So please forgive the newbie question - Every bit of help is gratefully received !

Model:

Code:

function getComment(){
$this->db->select('id,title, summary, body, createuser, createdate');
$this->db->where('status',1);
$this->db->order_by('createdate','desc');
$Q = $this->db->get('comments');
if ($Q->num_rows() > 0){
   $comments= $Q->result_array();
   $Q->free_result();
   return $comments;
}
  
}

Controller

Code:

function IndexComments(){
  
  $this->load->library('pagination');
  
   $data['title'] = "Community Blog | Country Lane Farms";
$data['navlist'] = $this->MCats->getCategoriesNav();
$data['comments'] = $this->MBlog->getComment();
$data['main'] = 'blog_comments';

$config['base_url'] = 'http://localhost/countrywide/index.php/blog/indexComments';
$config['total_rows'] = 10; // Change this !
$config['per_page'] = 10;

$this->pagination->initialize($config);
$this->load->vars($data);
$this->load->view('template');
  }

[eluser]LifeSteala[/eluser]
Model:

Code:

function getComment()
{
$this->db->select('id,title, summary, body, createuser, createdate');
$this->db->where('status',1);
$this->db->order_by('createdate','desc');
$Q = $this->db->get('comments');

if ($Q->num_rows() > 0)
{
  return $comments;
}
}

Controller:

Code:

function IndexComments()
{

$this->load->library('pagination');
  
$data['title'] = "Community Blog | Country Lane Farms";
$data['navlist'] = $this->MCats->getCategoriesNav();

$comments = $this->MBlog->getComment();
$data['comments'] = $comments;
$data['main'] = 'blog_comments';

$config['base_url'] = 'http://localhost/countrywide/index.php/blog/indexComments';
$config['total_rows'] = $comments->num_rows(); // Change this !
$config['per_page'] = 10;

$this->pagination->initialize($config);
$this->load->vars($data);
$this->load->view('template');
}

In your view, you should do:

Code:

if ($comments)
{
foreach($comments->result_array() as $comment)
{
   // Your comments output here
}
}

[eluser]Noobigniter[/eluser]
I do not speak English, so I may not understand the question, in which case I apologize in advance.

I use this:

Code:

'total_rows'   => $this->count($this->table),

and i have an general function

Code:

/*
* Count
*/
function count($table)
{
  return (int) $this->db->count_all_results($table);
}

Regards.

[eluser]InsiteFX[/eluser]

Code:

$counter = count($data['comments']);

[eluser]ludo31[/eluser]
I have the same problem and I solve it like this

Code:

public function getRow($genre)
       {
           // genre 1 homme 2 femme 3 enfant
            $query =  $this->db->select('identifiant_chaussure');
    $query =  $this->db->from('gnr_convenir');
           $query = $this->db->where('identifiant_genre', $genre);
          
   $query = $this->db->count_all_results();
  
   echo $query ; exit ;
  
   //return $query ;
  
          
        
       }

[eluser]johnpeace[/eluser]
[quote author="ludo31" date="1330341528"]I have the same problem and I solve it like this

Code:

public function getRow($genre)
       {
           // genre 1 homme 2 femme 3 enfant
            $query =  $this->db->select('identifiant_chaussure');
    $query =  $this->db->from('gnr_convenir');
           $query = $this->db->where('identifiant_genre', $genre);
          
   $query = $this->db->count_all_results();
  
   echo $query ; exit ;
  
   //return $query ;
  
          
        
       }

[/quote]

This approach never works for me and I always wind up with separate queries...one to get the data with limit, offset so the pagination will work...then another to count_all_results() WITHOUT the limit/offset, which reflects the total size of the data set and is necessary for the pagination lib to know how many pages will be required.

Is there a better way?

[eluser]pickupman[/eluser]
Running such a simple query to get the total number of rows has a very light impact on the overall query time. Even on a large data set, this is generally pretty quick with such a simple where() statement.

And the function count() does return the number of parent child keys in an array and not the total number of children in a multidimensional array. As InsiteFX pointed out using count() on the array returned would also work.

[eluser]johnpeace[/eluser]
[quote author="pickupman" date="1330364791"]
And the function count() does return the number of parent child keys in an array and not the total number of children in a multidimensional array. As InsiteFX pointed out using count() on the array returned would also work.[/quote]

Doesn't work for me...because the array returned by the query that gets my data for display is limited to the number of rows displayed per page. Counting it will always give me rows-per-page.

So, it always ends up looking like this for me:

$this->items->limit($this->settings->get('records_per_page'), $this->uri->segment(4));
$items = $this->items->get_all();
$item_count = $this->items->count_all();

// truncated for relevance
$config['total_rows'] = $item_count;
$config['per_page'] = $this->settings->get('records_per_page');

[eluser]vincej[/eluser]
Thanks to all - the function offered by InsiteFX solved the count problem dead easy. I now have the chevrons and the page numbers but for some unknown reason as I scroll through each page chevron I still get *all* the query results, rather than the number specified in $config[‘per_page’] = 10;

any Help in what I am doing wrong ? ?

Many Thanks !

[eluser]pickupman[/eluser]
You are not passing the limit and offset to your model, to get the correct range of results.

Code:

//Change model to
function getComment($limit = FALSE, $offset = 0)
{
$this->db->select('id,title, summary, body, createuser, createdate');
$this->db->where('status',1);

if ($limit)
    $this->db->limit($limit, $offset);

$this->db->order_by('createdate','desc');
$Q = $this->db->get('comments');

if ($Q->num_rows() > 0)
{
  return $comments;
}
}  

//Controller

$comments = $this->MBlog->getComment();
$data['main'] = 'blog_comments';

$config['base_url'] = site_url('/blog/indexComments/'); //Shorter syntax
$config['total_rows'] = $comments->num_rows(); // Change this !
$config['per_page'] = 10;

$this->pagination->initialize($config);

$data['comments'] = $this->MBlog->getComment($config['per_page'], $this->uri->segment(3,0) ); //Run query again with limit, offset