Codeigniter-> using Pagination with sql select

Ok I learned how to do pagination with codeigniter, but what I'm stuck on is how do you incorporate something like:

$this->db->query("Select * FROM film_list WHERE category = 'documentary');

How do I get the pagination to only return the above data? I don't want to return all the data in the table just a certain category.

function start()
{
    $config['base_url'] = 'http://localhost/sakila/index.php/s开发者_Python百科ite/start/';
    $config['total_rows'] = $this->db->get('film_list')->num_rows();
    $config['per_page'] = '10';
    $config['num_links'] = '20';
    $config['full_tag_open'] = '<div id="pagination">';
    $config['full_tag_close'] = '</div>';


    $data['main_content'] = "start";
    $data['records'] = $this->db->get('film_list', $config['per_page'], 
                                      $this->uri->segment(3));
    $this->pagination->initialize($config);
    $this->load->view('includes/template', $data);
    echo $this->pagination->create_links();}

I've gotten that far and it works, pulls all data from the sql table inserts it into the HTML table and it paginates correctly, I just want it limited to a certain category.

Here's the view, if it helps.

<div id="main">
  <h1>Documentaries</h1>
  <?php 
    echo $this->table->generate($records);
    echo $this->pagination->create_links();
  ?>
 </div>

Hope this code will help you.

<?php
//CONTROLLER

$qry = "Select * FROM film_list WHERE category = 'documentary";

$limit = 10;
$offset = ($this->uri->segment(3) != '' ? $this->uri->segment(3):0);

$config['base_url'] = 'http://localhost/sakila/index.php/site/start/';
$config['total_rows'] = $this->db->query($qry)->num_rows();
$config['uri_segment'] = 3;
$config['per_page'] = $limit;
$config['num_links'] = 20;
$config['full_tag_open'] = '<div id="pagination">';
$config['full_tag_close'] = '</div>';

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

$qry .= " limit {$limit} offset {$offset} ";

$data['result_per_page'] = $this->db->query($qry)->result();

$this->load->view('includes/template', $data);

for your view..

<div id="main">
   <h1>Documentaries</h1>
   <?php 
       foreach($result_per_page as $row)
   {
    echo $row->column_to_show; //display result to your databse.column_to_show is just a dummy column name of the query result
   }

       echo $this->pagination->create_links();
  ?>
</div>

You need to add the where clause. Instead of just:

$data['records'] = $this->db->get('film_list', $config['per_page'], 
                                      $this->uri->segment(3));

Try something like this:

$this->db->where('category','documentary');
$data['records'] = $this->db->get('film_list', $config['per_page'], 
                                      $this->uri->segment(3));