Codeigniter Search Query Strings

Codeigniter Search Query Strings Web-development (jquery) 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> <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/

Posted by Hodie J