CodeIgniter Search Query Strings

CodeIgniter Search Query Strings

Create Database table film_list
CREATE TABLE IF NOT EXISTS `film_list` (
  `FID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `length` varchar(255) NOT NULL,
  `rating` varchar(255) NOT NULL,
  `price` varchar(255) NOT NULL,
  PRIMARY KEY (`FID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
Insert data
INSERT INTO `film_list` (`FID`, `title`, `category`, `length`, `rating`, `price`) VALUES
(1, 'Final Fantasy', 'Animation', '1:30', 'G', '2.0'),
(2, 'My Sassy Girl', 'Love Story', '1:40', 'G', '2.3'),
(3, 'The Shawshank Redemption', 'Horror', '1:40', 'PG', '3'),
(4, 'The Godfather', 'Drama', '1:60', 'G', '1.3'),
(5, 'Star Wars ', 'Animation', '2:10', 'G', '2'),
(6, 'Shichinin no samurai ', 'Action', '1:10', 'G', '2'),
(7, 'The Matrix', 'Action', '1:25', 'G', '1'),
(8, 'The Lord of the Rings: The Two Towers', 'Action', '2.60', 'G', '2');

Create Table Category
CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Insert data category
INSERT INTO `category` (`id`, `name`) VALUES
(1, 'Action'),
(2, 'Animation'),
(3, 'Children'),
(4, 'Comedy'),
(5, 'Drama'),
(6, 'Horror'),
(7, 'Love Story'),
(8, 'Sports');

Create table 
CREATE TABLE IF NOT EXISTS `ci_query` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `query_string` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

Setup base_url
application\config\config.php
$config['base_url']    = 'http://localhost/DEVELOPMENT/CODEIGNATER/';

Create Controller 
application\controllers\films.php

<?php
class Films extends CI_Controller{
 function __construct() {
        parent:: __construct();
        $this->load->helper("url");
  $this->load->model('Film_model');
  $this->load->helper(array('form', 'url'));
    }
 function display($query_id = 0, $sort_by = 'title', $sort_order = 'asc', $offset = 0) {
  $limit = 5;
  $data['fields'] = array(
   'FID' => 'ID',
   'title' => 'Title',
   'category' => 'Category',
   'length' => 'Length',
   'rating' => 'Rating',
   'price' => 'Price'
  );
  
  $this->input->load_query($query_id);
  
  $query_array = array(
   'title' => $this->input->get('title'),
   'category' => $this->input->get('category'),
   'length_comparison' => $this->input->get('length_comparison'),
   'length' => $this->input->get('length'),
  );
  
  $data['query_id'] = $query_id;
  
  $this->load->model('Film_model');
  
  $results = $this->Film_model->search($query_array, $limit, $offset, $sort_by, $sort_order);
  
  $data['films'] = $results['rows'];
  $data['num_results'] = $results['num_rows'];
  
  // pagination
  $this->load->library('pagination');
  $config = array();
  $config['base_url'] = site_url("films/display/$query_id/$sort_by/$sort_order");
  $config['total_rows'] = $data['num_results'];
  $config['per_page'] = $limit;
  $config['uri_segment'] = 6;
  $this->pagination->initialize($config);
  $data['pagination'] = $this->pagination->create_links();
  
  $data['sort_by'] = $sort_by;
  $data['sort_order'] = $sort_order;
  
  $data['category_options'] = $this->Film_model->category_options();
  
  $this->load->view('films', $data);
 }
 function search() {
  $query_array = array(
   'title' => $this->input->post('title'),
   'category' => $this->input->post('category'),
   'length_comparison' => $this->input->post('length_comparison'),
   'length' => $this->input->post('length'),
  );
  
  $query_id = $this->input->save_query($query_array);
  
  redirect("films/display/$query_id");
 }
}

Create Models
application\models\Film_model.php

<?php
class Film_model extends CI_Model {
 function search($query_array, $limit, $offset, $sort_by, $sort_order) {
  $sort_order = ($sort_order == 'desc') ? 'desc' : 'asc';
  $sort_columns = array('FID', 'title', 'category', 'length', 'rating', 'price');
  $sort_by = (in_array($sort_by, $sort_columns)) ? $sort_by : 'title';
  
  // results query
  $q = $this->db->select('FID, title, category, length, rating, price')
   ->from('film_list')
   ->limit($limit, $offset)
   ->order_by($sort_by, $sort_order);
  
  if (strlen($query_array['title'])) {
   $q->like('title', $query_array['title']);
  }
  if (strlen($query_array['category'])) {
   $q->where('category', $query_array['category']);
  }
  if (strlen($query_array['length'])) {
   $operators = array('gt' => '>', 'gte' => '>=', 'eq' => '=', 'lte' => '<=', 'lt' => '<');
   $operator = $operators[$query_array['length_comparison']];
      
   $q->where("length $operator", $query_array['length']);
  }
  
  $ret['rows'] = $q->get()->result();
  
  // count query
  $q = $this->db->select('COUNT(*) as count', FALSE)
   ->from('film_list');
  
  if (strlen($query_array['title'])) {
   $q->like('title', $query_array['title']);
  }
  if (strlen($query_array['category'])) {
   $q->where('category', $query_array['category']);
  }
  if (strlen($query_array['length'])) {
   $operators = array('gt' => '>', 'gte' => '>=', 'eq' => '=', 'lte' => '<=', 'lt' => '<');
   $operator = $operators[$query_array['length_comparison']];
      
   $q->where("length $operator", $query_array['length']);
  }
  
  $tmp = $q->get()->result();
  
  $ret['num_rows'] = $tmp[0]->count;
  
  return $ret;
 }
 function category_options() {
  $rows = $this->db->select('name')
   ->from('category')
   ->get()->result();
  $category_options = array('' => '');
  foreach ($rows as $row) {
   $category_options[$row->name] = $row->name;
  }
  return $category_options;
 }
}

Create View
application\views\films.php

<html lang="en-US">
<head>
 <title>Films</title>
 <meta charset="UTF-8">
 <style>
  * {
   font-family: Arial;
   font-size: 12px;
  }
  table {
   border-collapse: collapse;
  }
  td, th {
   border: 1px solid #666666;
   padding:  4px;
  }
  div {
   margin: 4px;
  }
  .sort_asc:after {
   content: "▲";
  }
  .sort_desc:after {
   content: "▼";
  }
  label {
   display: inline-block;
   width: 120px;
  }
 </style>
</head>
<body>
 <?php echo form_open('films/search'); ?>
  <div>
   <?php echo form_label('Title:', 'title'); ?>
   <?php echo form_input('title', set_value('title'), 'id="title"'); ?>
  </div>
  <div>
   <?php echo form_label('Category:', 'category'); ?>
   <?php echo form_dropdown('category', $category_options, 
    set_value('category'), 'id="category"'); ?>
  </div>
  <div>
   <?php echo form_label('Length:', 'length'); ?>
   <?php echo form_dropdown('length_comparison', 
    array('gt' => '>', 'gte' => '>=', 'eq' => '=', 'lte' => '<=', 'lt' => '<') , 
    set_value('length_comparison'), 'id="length_comparison"'); ?>
   <?php echo form_input('length', set_value('length'), 'id="length"'); ?>
  </div>
  <div>
   <?php echo form_submit('action', 'Search'); ?>
  </div>
 <?php echo form_close(); ?>
 <div>
  Found <?php echo $num_results; ?> films
 </div>
 <table>
  <thead>
   <?php foreach($fields as $field_name => $field_display): ?>
   <th <?php if ($sort_by == $field_name) echo "class=\"sort_$sort_order\"" ?>>
    <?php echo anchor("films/display/$query_id/$field_name/" .
     (($sort_order == 'asc' && $sort_by == $field_name) ? 'desc' : 'asc') ,
     $field_display); ?>
   </th>
   <?php endforeach; ?>
  </thead>
  
  <tbody>
   <?php foreach($films as $film): ?>
   <tr>
    <?php foreach($fields as $field_name => $field_display): ?>
    <td>
     <?php echo $film->$field_name; ?>
    </td>
    <?php endforeach; ?>
   </tr>
   <?php endforeach; ?>   
  </tbody>
  
 </table>
 
 <?php if (strlen($pagination)): ?>
 <div>
  Pages: <?php echo $pagination; ?>
 </div>
 <?php endif; ?>
</body>
</html>

Create file My_Input.php
application\core\My_Input.php

<?php
class MY_Input extends CI_Input {
 function save_query($query_array) {
  $CI =& get_instance();
  $CI->db->insert('ci_query', array('query_string' => http_build_query($query_array)));
  return $CI->db->insert_id();
 }
 function load_query($query_id) {
  $CI =& get_instance();
  $rows = $CI->db->get_where('ci_query', array('id' => $query_id))->result();
  if (isset($rows[0])) {
   parse_str($rows[0]->query_string, $_GET);  
  }
 }
}

View
http://localhost/CODEIGNATER/films/display/