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/
