Pagination problem, total_rows

[eluser]Tud[/eluser]
Hello,

I have a problem with pagination. running a search, the first page is correct (for example $data['total_rows'] is = 12) but when I click on the second page $data['total_rows'] contains all rows (in my example 15). :-S
Any idea?

This is my code
controller:

Code:

function search_result() {
.....
$data['total_rows'] = $this->Real->search_count($this->input->post('', TRUE));
$this->load->library('pagination');
$config['base_url'] = site_url('pages/search_result');
$config['per_page'] = 4;
$config['uri_segment'] = '3';
$config['num_links'] = 3;
$config['first_link'] = 'Inizio';
$config['last_link'] = 'Fine';
$config['cur_tag_open'] = '<span class="active_link">';
$config['cur_tag_close'] = '</span>';
$config['total_rows'] = $data['total_rows'];
$this->pagination->initialize($config);

$data['search_result'] = $this->Real->search($this->input->post('', TRUE),$config['per_page'], (int)$this->uri->segment(3));
.....
}

model:

Code:

function search($post, $num, $offset) {
.....
$this->db->where($options_where);
$this->db->where_in($options_where_in);
$this->db->get('reals', $num, $offset);
.....
}

function search_count($post) {
.....
$this->db->where($options_where);
$this->db->where_in($options_where_in);
$Q = $this->db->get('reals');
$num_rows = $Q->num_rows();
return $num_rows;
}

view:

Code:

<?php foreach ($search_result as $s) { ?>
.....
<?php } ?>
<div id="pagination">&lt;?php echo $this->pagination->create_links(); ?&gt;</div>

Thanks
.Stefano

[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums.

I don't see a limit clause in your model methods. If you're wanting no more than x number of result, you'll need to use it.

[eluser]Tud[/eluser]
Thanks but the second parameter of $this->db->get method is the LIMIT ;-)

Code:

.....
$this->db->get('reals', $num, $offset);
.....

I solved the problem adding to my controller

Code:

.....
if ($this->input->post()) {
    $this->firephp->fb($this->input->post());
    $session_data = array(
        'search_query' => $this->input->post('', TRUE),
        'search_query_total_rows' => this->Real->search_count($this->input->post('', TRUE))
    );
    $this->session->set_userdata($session_data);
}
.....
$config['total_rows'] = $this->session->userdata('search_query_total_rows');
.....
$data['total_rows'] = $this->session->userdata('search_query_total_rows');
$data['search_result'] = $this->Real->search($this->session->userdata('search_query'), $config['per_page'], $this->uri->segment(3));
.....

.Stefano

NB: I hacked $this->input->post() standard method in order to return the whole post

[eluser]TheFuzzy0ne[/eluser]
Oh yes. Sorry, I missed that.

Can you confirm that $num actually contains the expected value?

There should be no need for any hacking of the post method. You can simply add a get_all_post() method to the input class, or better yet, if you've enabled global XSS filtering, you can just use $_POST as it is.

OK, I'm a bit confused. Please can you confirm:
1) How many results you have in your database
2) How many you see on the first page
3) How many you see on the next page (and possible more information as to the position of the result in your table).
4) Where $num is set, and how it's set.

In the interim, you can enable the profiler by adding:

Code:

$this->output->enable_profiler(TRUE);

To your controller constructor, or the start of your controller method. This will show you what queries are being run, and hopefully make clear to you where you are going wrong.

[eluser]slowgary[/eluser]
I'm confused by this and since I'm currently implementing pagination in my own project it's perfect timing.

Is there no better way to do pagination without 2 queries? One for the row_count, and one for the actual rows (num_rows, offset)?

I guess this is probably more of a database question, I just noticed in my own app that pagination never appears because the number I'm passing as 'total_rows' is the number of rows I have retrieved, which always ends up to be the 'num_per_page'. Since the 'num_per_page' threshold is never reached, no pagination appears. Now that I understand that, it makes sense. But now that I realize I need a second query for the row count, I think "pooh!".

Can I just do something like:

Code:

SELECT *, COUNT(*) AS total_rows FROM table_name

I'll go try this now. Thanks.

[eluser]TheFuzzy0ne[/eluser]
Shouldn't MySQL cache that query? If not, you can use SQL_CALC_FOUND_ROWS.

Code:

$this->db->select('SQL_CAL_FOUND_ROWS *', FALSE);
$res = $this->db->get($table);

Then it's a case of making a second query, but simply to select that row count, just like calling INSERT_ID():

Code:

$this->db->select('FOUND_ROWS()', FALSE);
$res = $this->db->get($table);

EDIT: And don't forget, you don't want all of the rows returned. You're query will most likely be limited. SQL_CALC_FOUND_ROWS just ignores any limit clause.

[eluser]Tud[/eluser]
thanks for your answers

@TheFuzzy0ne
> Can you confirm that $num actually contains the expected value?
yes

> or better yet, if you’ve enabled global XSS filtering, you can just use $_POST as it is.
I'm a CI newbie, thank you for this idea

> 1) 2) 3) 4)
the problem was that when I navigated to 2nd..nth page the post was empty and so the query retrivied all rows in the table and not just the rows I searched for.
I solved saving the post into a session userdata. In this way pagination works fine.

@slowgary
> Is there no better way to do pagination without 2 queries? One for the row_count, and one for the actual rows (num_rows, offset)?
I save the rows count into session userdata so it's just one query.

.Stefano