In this tutorial you will learn how to create search filter in php from a MySQL database table based on specific conditions using PHP.
Today, We want to share with you how to create search filter in php.In this post we will show you jquery search filter, hear for category filter in php we will give you demo and example for implement.In this post, we will learn about how to search and display data from database in php with an example.
Auto Product Filter Search feature is very popular in online eCommerce website to allow product search with different options like. how to create search filter in php product type, price, rating or range filter and checkbox search filter etc.
how to create search filter in php using PDO
Filter search result is a common functionality of online eCommerce website to provide relevant search result to user Like as product brand,size,material checkbox search filtering using php and jquery.
So let’s implement Filter Search Result functionality with Ajax, PHP & MySQL.
The major files are:
- Index.php
- search.js
- search_data.php
- Search.php
Step 1: Create MySQL Database Table
CREATE TABLE `categorizations` (
`id` int(11) NOT NULL,
`user_name` varchar(255) NOT NULL,
`product` varchar(255) NOT NULL,
`value` double(12,2) NOT NULL,
`date` date NOT NULL,
`status` enum('Processed','Pending','Cancelled') NOT NULL DEFAULT 'Pending'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Insert categorizations record using following insert statement.
INSERT INTO `categorizations` (`id`, `user_name`, `product`, `value`, `date`, `status`) VALUES (1, 'Ravi', 'Mobile', 1200.00, '2022-08-14', 'Processed'), (2, 'Kajal', 'Cricket Bat', 800.00, '2022-07-08', 'Cancelled'), (3, 'Bhoomi', 'Laptop', 750.00, '2022-06-29', 'Processed'), (4, 'Sejal', 'Washing Machine', 11250.00, '2022-05-20', 'Pending'), (5, 'Dahra', 'Shopping Trolley', 200.00, '2022-06-10', 'Pending'), (6, 'Bhagyshree', 'Sony Camera', 1200.00, '2022-06-06', 'Processed'), (7, 'Jalpa', 'Bike Tires', 2000.00, '2022-05-28', 'Processed'), (8, 'Bhavika', 'Bike', 1600.00, '2022-04-18', 'Cancelled'), (9, 'Monika', 'Computer', 1500.00, '2022-05-22', 'Pending'), (10, 'Ankita', 'Laptop', 2800.00, '2022-06-30', 'Cancelled'), (11, 'Dimple', 'Sony TV', 1880.00, '2022-05-03', 'Pending'), (12, 'Kinara', 'iPhone ', 2225.00, '2022-06-01', 'Processed'), (13, 'Falhuni', 'iphone 7', 15620.00, '2022-05-30', 'Pending'), (14, 'Meera', '3D Printer', 600.00, '2022-06-02', 'Processed'), (15, 'Jayshree', 'Kitchen Appliances', 35.00, '2022-07-29', 'Processed'), (16, 'Astha', 'SanDisk 32GB microSDHC', 85.00, '2022-04-05', 'Pending'), (17, 'Mithi', 'Tube Light', 12.00, '2022-05-04', 'Processed'), (18, 'Dipti', 'Bluetooth', 20.00, '2022-06-07', 'Pending'), (19, 'Shital', 'shampoo', 10.00, '2022-06-13', 'Cancelled'), (20, 'Laxmi', 'Mobile charger', 10.00, '2022-06-18', 'Pending');
Step 2: Search with Filter Design and Search Result Listing
Search and sorting interface and search result.
Example: Filter Search Result with Ajax, PHP & MySQL
searchResult(array('categorization_by'=>'id DESC')); if(!empty($allCategorizations)) { foreach($allCategorizations as $categorization) { $status = ''; if($categorization["status"] == 'Processed') { $status = 'btn-success'; } else if($categorization["status"] == 'Pending') { $status = 'btn-warning'; } else if($categorization["status"] == 'Cancelled') { $status = 'btn-danger'; } echo ' ID Customer Name Categorization Item Value Date Status '; } } else { ?> '.$categorization["id"].' '.$categorization["user_name"].' '.$categorization["product"].' $'.$categorization["value"].' '.$categorization["date"].' No user(s) found...
Step 3: handle search with filter by making Ajax request
Handle search jquery Ajax request and return response search result.
$(document).ready(function(){
$("#search").click(function() {
var keywords = $('#keywords').val();
getData('search', keywords, '');
});
$("#sortSearch").change(function() {
var sortValue = $(this).val();
var keywords = $('#keywords').val();
getData('sort', keywords, sortValue);
});
});
function getData(type, keywords, sortValue) {
$.ajax({
type: 'POST',
url: 'search_data.php',
data: 'type='+type+'&keywords='+keywords+'&sortValue='+sortValue,
beforeSend:function(html){
$('.loading-overlay').show();
},
success:function(html){
$('.loading-overlay').hide();
$('#userData').html(html);
}
});
}
Step 4: Handle Search with Filter
File to search and sorting functionality.
$_POST['keywords'],'product'=>$_POST['keywords'], 'value'=>$_POST['keywords']);
$sqlConditions['categorization_by'] = 'id DESC';
}elseif($_POST['type'] == 'sort'){
if($_POST['keywords']) {
$sqlConditions['search'] = array('user_name'=>$_POST['keywords'],'product'=>$_POST['keywords'],'value'=>$_POST['keywords']);
}
$sortValue = $_POST['sortValue'];
$sortArribute = array(
'new' => array(
'categorization_by' => 'date DESC'
),
'asc'=>array(
'categorization_by'=>'user_name ASC'
),
'desc'=>array(
'categorization_by'=>'user_name DESC'
),
'Processed'=>array(
'where'=>array('status'=>'Processed')
),
'Pending'=>array(
'where'=>array('status'=>'Pending')
),
'Cancelled'=>array(
'where'=>array('status'=>'Cancelled')
)
);
$sortKey = key($sortArribute[$sortValue]);
$sqlConditions[$sortKey] = $sortArribute[$sortValue][$sortKey];
}
}else{
$sqlConditions['categorization_by'] = 'id DESC';
}
$categorizations = $search->searchResult($sqlConditions);
if(!empty($categorizations)){
foreach($categorizations as $categorization){
$status = '';
if($categorization["status"] == 'Processed') {
$status = 'btn-success';
} else if($categorization["status"] == 'Pending') {
$status = 'btn-warning';
} else if($categorization["status"] == 'Cancelled') {
$status = 'btn-danger';
}
echo '';
echo ''.$categorization['id'].' ';
echo ''.$categorization['user_name'].' ';
echo ''.$categorization['product'].' ';
echo ''.$categorization['value'].' ';
echo ''.$categorization['date'].' ';
echo ' ';
}
}else{
echo 'No user(s) found...
Step 5: Filter Search Result with Ajax, PHP & MySQL
A class that hold methods related to search.
db)){
$conn = new mysqli($this->host, $this->username, $this->password, $this->database);
if($conn->connect_error){
die("Failed to connect with MySQL: " . $conn->connect_error);
}else{
$this->db = $conn;
}
}
}
public function searchResult($sqlQueryConditions = array()){
$sqlQuery = 'SELECT ';
$sqlQuery .= array_key_exists("select",$sqlQueryConditions)?$sqlQueryConditions['select']:'*';
$sqlQuery .= ' FROM categorizations';
if(array_key_exists("where",$sqlQueryConditions)){
$sqlQuery .= ' WHERE ';
$i = 0;
foreach($sqlQueryConditions['where'] as $key => $value){
$pre = ($i > 0)?' AND ':'';
$sqlQuery .= $pre.$key." = '".$value."'";
$i++;
}
}
if(array_key_exists("search",$sqlQueryConditions)){
$sqlQuery .= (strpos($sqlQuery, 'WHERE') !== false)?' AND (':' WHERE (';
$i = 0;
foreach($sqlQueryConditions['search'] as $key => $value){
$pre = ($i > 0)?' OR ':' ';
$sqlQuery .= $pre.$key." LIKE '%".$value."%'";
$i++;
}
$sqlQuery .= ")";
}
if(array_key_exists("categorization_by",$sqlQueryConditions)){
$sqlQuery .= ' ORDER BY '.$sqlQueryConditions['categorization_by'];
}
if(array_key_exists("start",$sqlQueryConditions) && array_key_exists("limit",$sqlQueryConditions)){
$sqlQuery .= ' LIMIT '.$sqlQueryConditions['start'].','.$sqlQueryConditions['limit'];
}elseif(!array_key_exists("start",$sqlQueryConditions) && array_key_exists("limit",$sqlQueryConditions)){
$sqlQuery .= ' LIMIT '.$sqlQueryConditions['limit'];
}
$searchResult = $this->db->query($sqlQuery);
if(array_key_exists("return_type",$sqlQueryConditions) && $sqlQueryConditions['return_type'] != 'all'){
switch($sqlQueryConditions['return_type']){
case 'count':
$searchData = $searchResult->num_rows;
break;
case 'single':
$searchData = $searchResult->fetch_assoc();
break;
default:
$searchData = '';
}
}else{
if($searchResult && $searchResult->num_rows > 0){
while($row = $searchResult->fetch_assoc()){
$searchData[] = $row;
}
}
}
return !empty($searchData)?$searchData:false;
}
}
I hope you get an idea about how to create search filter in php.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.