[SOLVED]$this->db->count_all() in pagination issue

[eluser]Marlbor0[/eluser]
Hello,
After some time spent on google i cant find the right way to do this, i have a custom sql select with joins to make a list of all my products, that select dont pick up products that dont have a manufacturer, so not all the records are taken from the db.

For the pagination, i need to set the max resuts so it can generate enought "pages" but with $this->db->count_all() i get all the data, i tried using $this->db->where() before counting the results but no go.

Is there a simple way, or right way to use pagination with custom query?

[eluser]InsiteFX[/eluser]
Something like below:

Code:

$cnt = count($result);

InsiteFX

[eluser]Marlbor0[/eluser]
$result = $this->db->query('SELECT p . * , f.id AS fid, f.nome AS fnome FROM produtos p INNER JOIN fornecedor f ON p.fornecedor = f.id LIMIT '.$offset.', '.$limit.'');
$cnt = count($result);

I cant test it right now but is this it? or i gota do a $result = $result->result();?

Thanks alot InsiteFX

[eluser]alex.sash[/eluser]
[quote author="Marlbor0" date="1300996149"]Is there a simple way, or right way to use pagination with custom query?[/quote]

The "right way" is to
use db->where() + db->count_all_results()
and
same db->where() + db->get() with limit and offset generated by pagination uri segments.

Also there's a tutorial available.
http://net.tutsplus.com/videos/screencas...agination/

[eluser]Marlbor0[/eluser]

Code:

$data = array();
$this->load->library('pagination');
$offset=$this->uri->segment(3);
$limit=10;
$config['base_url'] = 'http://nomnomnom.com/site/';
$this->db->where('novo', 1);
$config['total_rows'] = $this->db->count_all('veiculos');
$config['per_page'] = $limit;
$this->pagination->initialize($config);
$this->db->where('novo', 1);
$this->db->order_by('id', 'DESC');
$this->db->limit($limit, $offset);
if($query = $this->db->get('veiculos'))
{
    $data['records'] = $query->result();
    $data['paginator'] = $this->pagination->create_links();

Is there anything wrong with the code? i always get more pages than products, like 2 or 3 blank pages on the pagination.
Thanks

[eluser]Marlbor0[/eluser]
My bad the right function is count_all_results() not count_all() in case you use a where
Thanks for the replys