CodeIgniter Pagination with Search Query Filter: Example with Bootstrap

On 11/10/2017

Hi, earlier in our codeigniter tutorial series, I have discussed about integrating bootstrap styles with codeigniter pagination library. And I got enquires from readers about adding search filter to it. Including search option to pagination results will greatly improve user experience, especially if you have several hundreds of pages to skim through. So in this post I will teach you exactly how to implement search query feature in codeigniter-bootstrap pagination.

Implementing CodeIgniter Pagination with Search Query Filter

To implement pagination with search, we need to retrieve a large data set from the database, display in chunks and add a search form on top to filter those results. For better understanding, let me go by an example and explain it with a sample database.

Create MySQL Database

Run this below sql commands in mysql to create the database ‘db_library’ with a table ‘tbl_books’ that stores details of books.

CREATE DATABASE `db_library`;
USE `db_library`;

CREATE TABLE IF NOT EXISTS `tbl_books` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `author` varchar(30) NOT NULL,
  `isbn` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21;

INSERT INTO `tbl_books` (`id`, `name`, `author`, `isbn`) VALUES
(1, 'Learning PHP, MySQL & JavaScript', 'Robin Nixon', 'ISBN-13: 978-1491918661'),
(2, 'PHP and MySQL for Dynamic Web Sites', 'Larry Ullman', 'ISBN-13: 978-0321784070'),
(3, 'PHP Cookbook', 'David Sklar', 'ISBN-13: 978-1449363758'),
(4, 'Programming PHP', 'Kevin Tatroe', 'ISBN-13: 978-1449392772'),
(5, 'Modern PHP: New Features and Good Practices', 'Josh Lockhart', 'ISBN-13: 978-1491905012'),
(6, 'Modern PHP New Features and Good Practices', 'Josh Lockhart', 'ISBN-13: 978-1491905012'),
(7, 'Learning PHP MySQL & JavaScript', 'Robin Nixon', 'ISBN-13: 978-1491918661'),
(8, 'PHP and MySQL for Dynamic Web Sites', 'Larry Ullman', 'ISBN-13: 978-0321784070'),
(9, 'PHP Cookbook', 'David Sklar', 'ISBN-13: 978-1449363758'),
(10, 'Programming PHP', 'Kevin Tatroe', 'ISBN-13: 978-1449392772'),
(11, 'Modern PHP New Features and Good Practices', 'Josh Lockhart', 'ISBN-13: 978-1491905012'),
(12, 'Learning PHP MySQL & JavaScript', 'Robin Nixon', 'ISBN-13: 978-1491918661'),
(13, 'PHP and MySQL for Dynamic Web Sites', 'Larry Ullman', 'ISBN-13: 978-0321784070'),
(14, 'PHP Cookbook', 'David Sklar', 'ISBN-13: 978-1449363758'),
(15, 'Programming PHP', 'Kevin Tatroe', 'ISBN-13: 978-1449392772'),
(16, 'Modern PHP New Features and Good Practices', 'Josh Lockhart', 'ISBN-13: 978-1491905012'),
(17, 'Learning PHP MySQL & JavaScript', 'Robin Nixon', 'ISBN-13: 978-1491918661'),
(18, 'PHP and MySQL for Dynamic Web Sites', 'Larry Ullman', 'ISBN-13: 978-0321784070'),
(19, 'PHP Cookbook', 'David Sklar', 'ISBN-13: 978-1449363758'),
(20, 'Programming PHP', 'Kevin Tatroe', 'ISBN-13: 978-1449392772');

Having the database structure in place, let’s move on to the coding part.

The Model (‘pagination_model.php’)

First we need a codeigniter model file to place all our database transactions. For this example, we have to write two methods in the model for 1. To fetch the list of all books from the table ‘tbl_books’ and, 2. To get the number of books returned from ‘tbl_books’ for a particular search query.

Now create a file named ‘pagination_model.php’ inside ‘application/models’ folder and copy paste the below code to it.

<?php
class pagination_model extends CI_Model{

    function __construct()
    {
        parent::__construct();
    }

    //fetch books
    function get_books($limit, $start, $st = NULL)
    {
        if ($st == "NIL") $st = "";
        $sql = "select * from tbl_books where name like '%$st%' limit " . $start . ", " . $limit;
        $query = $this->db->query($sql);
        return $query->result();
    }
    
    function get_books_count($st = NULL)
    {
        if ($st == "NIL") $st = "";
        $sql = "select * from tbl_books where name like '%$st%'";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
}
?>

The Controller (‘pagination.php’)

Next we have to create a controller file for pagination and load codeigniter’s basic set of helpers & libraries, along with pagination library and the model we have created above. In the controller we need two methods, one the index() method, and the another one search() for search option, in which we should pass the search string as the url parameter and filter the results retrieved from the database.

Now create a file named ‘pagination.php’ inside ‘application/controllers’ folder, copy paste the below code and save.

<?php
class pagination extends CI_Controller {
            
    public function __construct()
    {
        parent::__construct();
        $this->load->helper('form');
        $this->load->helper('url');
        $this->load->database();
        $this->load->library('pagination');
        $this->load->model('pagination_model');
    }

    public function index()
    {
        //pagination settings
        $config['base_url'] = site_url('pagination/index');
        $config['total_rows'] = $this->db->count_all('tbl_books');
        $config['per_page'] = "3";
        $config["uri_segment"] = 3;
        $choice = $config["total_rows"]/$config["per_page"];
        $config["num_links"] = floor($choice);

        // integrate bootstrap pagination
        $config['full_tag_open'] = '<ul class="pagination">';
        $config['full_tag_close'] = '</ul>';
        $config['first_link'] = false;
        $config['last_link'] = false;
        $config['first_tag_open'] = '<li>';
        $config['first_tag_close'] = '</li>';
        $config['prev_link'] = '«';
        $config['prev_tag_open'] = '<li class="prev">';
        $config['prev_tag_close'] = '</li>';
        $config['next_link'] = '»';
        $config['next_tag_open'] = '<li>';
        $config['next_tag_close'] = '</li>';
        $config['last_tag_open'] = '<li>';
        $config['last_tag_close'] = '</li>';
        $config['cur_tag_open'] = '<li class="active"><a href="#">';
        $config['cur_tag_close'] = '</a></li>';
        $config['num_tag_open'] = '<li>';
        $config['num_tag_close'] = '</li>';
        $this->pagination->initialize($config);

        $data['page'] = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
        
        // get books list
        $data['booklist'] = $this->pagination_model->get_books($config["per_page"], $data['page'], NULL);
        
        $data['pagination'] = $this->pagination->create_links();
        
        // load view
        $this->load->view('pagination_view',$data);
    }
    
    function search()
    {
        // get search string
        $search = ($this->input->post("book_name"))? $this->input->post("book_name") : "NIL";

        $search = ($this->uri->segment(3)) ? $this->uri->segment(3) : $search;

        // pagination settings
        $config = array();
        $config['base_url'] = site_url("pagination/search/$search");
        $config['total_rows'] = $this->pagination_model->get_books_count($search);
        $config['per_page'] = "5";
        $config["uri_segment"] = 4;
        $choice = $config["total_rows"]/$config["per_page"];
        $config["num_links"] = floor($choice);

        // integrate bootstrap pagination
        $config['full_tag_open'] = '<ul class="pagination">';
        $config['full_tag_close'] = '</ul>';
        $config['first_link'] = false;
        $config['last_link'] = false;
        $config['first_tag_open'] = '<li>';
        $config['first_tag_close'] = '</li>';
        $config['prev_link'] = 'Prev';
        $config['prev_tag_open'] = '<li class="prev">';
        $config['prev_tag_close'] = '</li>';
        $config['next_link'] = 'Next';
        $config['next_tag_open'] = '<li>';
        $config['next_tag_close'] = '</li>';
        $config['last_tag_open'] = '<li>';
        $config['last_tag_close'] = '</li>';
        $config['cur_tag_open'] = '<li class="active"><a href="#">';
        $config['cur_tag_close'] = '</a></li>';
        $config['num_tag_open'] = '<li>';
        $config['num_tag_close'] = '</li>';
        $this->pagination->initialize($config);

        $data['page'] = ($this->uri->segment(4)) ? $this->uri->segment(4) : 0;
        // get books list
        $data['booklist'] = $this->pagination_model->get_books($config['per_page'], $data['page'], $search);

        $data['pagination'] = $this->pagination->create_links();

        //load view
        $this->load->view('pagination_view',$data);
    }
}
?>

As you can see we have used a view file ‘pagination_view’ in the controller functions, this we will create in the next step.

Note: This code integrates Twitter Bootstrap Pagination classes with CI pagination which is completely optional. The details about integration part are already covered here, so I won’t be repeating it now.

The View (‘pagination_view.php’)

Here comes the final step. We need to create a user interface that contains a search form, html table structure to display the books info fetched from the database along with pagination links towards the end.

For view, create a file named ‘pagination_view.php’ inside ‘application/views’ and copy paste this code.

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CodeIgniter Pagination Example with Search Query Filter</title>
    <link rel="stylesheet" href="<?php echo base_url("bootstrap/css/bootstrap.css"); ?>">
    
    <style type="text/css">
    .bg-border {
        border: 1px solid #ddd;
        border-radius: 4px 4px;
        padding: 15px 15px;
    }
    </style>
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-md-8 col-md-offset-2 well">
        <?php 
        $attr = array("class" => "form-horizontal", "role" => "form", "id" => "form1", "name" => "form1");
        echo form_open("pagination/search", $attr);?>
            <div class="form-group">
                <div class="col-md-6">
                    <input class="form-control" id="book_name" name="book_name" placeholder="Search for Book Name..." type="text" value="<?php echo set_value('book_name'); ?>" />
                </div>
                <div class="col-md-6">
                    <input id="btn_search" name="btn_search" type="submit" class="btn btn-danger" value="Search" />
                    <a href="<?php echo base_url(). "index.php/pagination/index"; ?>" class="btn btn-primary">Show All</a>
                </div>
            </div>
        <?php echo form_close(); ?>
        </div>
    </div>

    <div class="row">
        <div class="col-md-8 col-md-offset-2 bg-border">
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
                    <th>#</th>
                    <th>Book Name</th>
                    <th>Author Name</th>
                    <th>ISBN</th>
                    </tr>
                </thead>
                <tbody>
                <?php for ($i = 0; $i < count($booklist); ++$i) { ?>
                <tr>
                    <td><?php echo ($page+$i+1); ?></td>
                    <td><?php echo $booklist[$i]->name; ?></td>
                    <td><?php echo $booklist[$i]->author; ?></td>
                    <td><?php echo $booklist[$i]->isbn; ?></td>
                </tr>
                <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <div class="row">
        <div class="col-md-8 col-md-offset-2">
            <?php echo $pagination; ?>
        </div>
    </div>
</div>
</body>
</html>

That’s it! Now run the controller in the browser and you can see several books details listed with pagination links along with search query option at the top.

codeigniter-pagination-with-search-query-string-example

Now type whole/part of a book name and all the books containing that particular search string in their name will be filtered & displayed like this.

codeigniter-bootstrap-pagination-with-search-query-filter-example

I hope this post gives you a better idea of implementing search query filter for codeigniter pagination. Meet you in another interesting tutorial :-)

Read Also:

Last Modified: Nov-10-2017


33 comments:

  1. This tutorial very useful.
    Thank for your tutorial:D

    ReplyDelete
    Replies
    1. I found this code only query English variable, So I provide a function.

      This function allows CodeIgniter url can get ohter language variable.

      https://gyazo.com/bb073b4286289884e183c9dc63c604fa

      public function unescape($str){ // Use only utf-8
      $ret = '';
      $len = strlen($str);
      for ($i = 0; $i < $len; $i++){
      if ($str[$i] == '%' && $str[$i+1] == 'u'){
      $val = hexdec(substr($str, $i+2, 4));
      if ($val < 0x7f) $ret .= chr($val);
      else if($val < 0x800) $ret .= chr(0xc0|($val>>6)).chr(0x80|($val&0x3f));
      else $ret .= chr(0xe0|($val>>12)).chr(0x80|(($val>>6)&0x3f)).chr(0x80|($val&0x3f));
      $i += 5;
      }
      else if ($str[$i] == '%'){
      $ret .= urldecode(substr($str, $i, 3));
      $i += 2;
      }
      else $ret .= $str[$i];
      }
      return $ret;
      }

      :D

      Delete
  2. this $data['page'] = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
    need to be changed to
    $data['page'] = ($this->uri->segment(3)) ? (($this->uri->segment(3)-1)*$config['per_page']) : 0;

    ReplyDelete
  3. this $data['page'] = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
    need to be changed to
    $data['page'] = ($this->uri->segment(3)) ? (($this->uri->segment(3)-1)*$config['per_page']) : 0;

    ReplyDelete
  4. sorry my comment is not complete
    So if i put
    $data['page'] = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
    and
    $data['page'] = ($this->uri->segment(4)) ? $this->uri->segment(4) : 0;

    the data in table i get :
    in page 1
    1|A
    2|B
    3|C
    page 2
    3|C
    4|D
    5|E
    page 3
    4|D
    5|E
    6|F
    the data is mismatched
    so i try change it to
    $data['page'] = ($this->uri->segment(3)) ? (($this->uri->segment(3) - 1) * $page_limit) : 0;
    and
    $data['page'] = ($this->uri->segment(4)) ? (($this->uri->segment(4) - 1) * $page_limit) : 0;

    ReplyDelete
  5. Thanks for this tutorial.
    This tutorial is very helpful.

    ReplyDelete
  6. i want to serach with morethan one input feild please help me

    ReplyDelete
  7. i do same to same code but my search result isn't showing. showing previous result only, it doesn't changing. please help me

    ReplyDelete
  8. i do same to same code but my search result isn't showing. showing previous result only, it doesn't changing. please help me

    ReplyDelete
  9. Hello there,
    Just started to explore your site But I like a lot your content your content and the way you present it!!! Gongrats ;-)

    ReplyDelete
  10. How can I implement the search plus query with active record Join
    public function get_myinterns()
    {
    $this->db->SELECT('users.first_name, users.last_name, internship_oppotunity.tittle');
    $this->db->FROM('internship_oppotunity');
    $this->db->join('users', 'users.id = internship_oppotunity.internship_id');

    $query = $this->db->get();

    if ($query->num_rows()>0)
    {
    return $query->result();
    }
    else
    {
    return false;
    }
    }

    ReplyDelete
  11. thank you very much i used it in my website www.surf.az

    ReplyDelete
  12. will try............
    coming soon..............

    ReplyDelete
  13. Hi, look thanks for this example, it is help me so much, Thanks

    ReplyDelete
  14. Great example... Thanks a lot......

    ReplyDelete
  15. Great example.... Thanks a lot.....

    ReplyDelete
  16. can you give downloadable tutorials??

    ReplyDelete
  17. can u post a downloadable tutorial?

    ReplyDelete
  18. can we add get id query to fetch the details of the id from another table pls help me in that i want to creat that

    ReplyDelete
  19. The tutorial is very straight forward and helpful but there is a little problem. In the search method. $search = ($this->input->post("book_name"))? $this->input->post("book_name") : "NIL";
    It doesn't open the next search when you click on the pagination number,It converts the url to % format if there is a space in the search term.
    MHIS/index.php/users/search/Lagos%20Zone%205/100

    ReplyDelete
  20. hello,
    this is best but my problem is i remove the index.php using .htaccess file its giving error while using pagination object not found. can you please give me solution for that.

    ReplyDelete
  21. hi
    it's very good help for me.
    Can u help more? how the search button r work ?

    ReplyDelete

Contact Form

Name

Email *

Message *