- [C#] Thông báo page Facebook Trang Lập Trình VB.NET, C# bị Hack
- [C#] Chia sẻ source code phần mềm đếm số trang tập tin file PDF
- [C#] Cách Sử Dụng DeviceId trong C# Để Tạo Khóa Cho Ứng Dụng
- [SQLSERVER] Loại bỏ Restricted User trên database MSSQL
- [C#] Hướng dẫn tạo mã QRcode Style trên winform
- [C#] Hướng dẫn sử dụng temp mail service api trên winform
- [C#] Hướng dẫn tạo mã thanh toán VietQR Pay không sử dụng API trên winform
- [C#] Hướng Dẫn Tạo Windows Service Đơn Giản Bằng Topshelf
- [C#] Chia sẻ source code đọc dữ liệu từ Google Sheet trên winform
- [C#] Chia sẻ source code tạo mã QR MOMO đa năng Winform
- [C#] Chia sẻ source code phần mềm lên lịch tự động chạy ứng dụng Scheduler Task Winform
- [Phần mềm] Tải và cài đặt phần mềm Sublime Text 4180 full version
- [C#] Hướng dẫn download file từ Minio Server Winform
- [C#] Hướng dẫn đăng nhập zalo login sử dụng API v4 trên winform
- [SOFTWARE] Phần mềm gởi tin nhắn Zalo Marketing Pro giá rẻ mềm nhất thị trường
- [C#] Việt hóa Text Button trên MessageBox Dialog Winform
- [DEVEXPRESS] Chia sẻ code các tạo report in nhiều hóa đơn trên XtraReport C#
- [POWER AUTOMATE] Hướng dẫn gởi tin nhắn zalo từ file Excel - No code
- [C#] Chia sẻ code lock và unlock user trong domain Window
- [DEVEXPRESS] Vẽ Biểu Đồ Stock Chứng Khoán - Công Cụ Thiết Yếu Cho Nhà Đầu Tư trên Winform
[WEB] Tìm kiếm trên dữ liệu cột trên Datatable sử dụng Ajax Jquery
Bài viết hôm nay, mình sẽ hướng dẫn các bạn cách tìm kiếm ajax jquery trên DataTable, khi chọn dữ liệu thì sẽ load ajax, bài viết này mình tham khảo trên trang webslesson, thấy rất hay, nên chia sẽ lại cho các bạn.
Hy vọng bài viết sẽ có ích cho bạn nào đang tìm hiểu về Datatable jquery.
Video Demo tìm kiếm ajax trên datatable
Source code:
File index.php
<!--?php
$connect = mysqli_connect("localhost", "root", "", "testing1");
$query = "SELECT * FROM category ORDER BY category_name ASC";
$result = mysqli_query($connect, $query);
?-->
<title>Datatables Individual column searching using PHP Ajax Jquery</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<div class="container">
<h1 align="center">Datatables Individual column searching using PHP Ajax Jquery</h1>
<br>
<div class="table-responsive">
<div id="product_data_wrapper" class="dataTables_wrapper form-inline dt-bootstrap no-footer"><div class="row"><div class="col-sm-6"><div class="dataTables_length" id="product_data_length"><label>Show <select name="product_data_length" aria-controls="product_data" class="form-control input-sm"><option value="10">10</option><option value="25">25</option><option value="50">50</option><option value="100">100</option></select> entries</label></div></div><div class="col-sm-6"><div id="product_data_filter" class="dataTables_filter"><label>Search:<input type="search" class="form-control input-sm" placeholder="" aria-controls="product_data"></label></div></div></div><div class="row"><div class="col-sm-12"><table id="product_data" class="table table-bordered table-striped dataTable no-footer" role="grid" aria-describedby="product_data_info">
<thead>
<tr role="row"><th class="sorting" tabindex="0" aria-controls="product_data" rowspan="1" colspan="1" style="width: 133px;">Sr. No.</th><th class="sorting" tabindex="0" aria-controls="product_data" rowspan="1" colspan="1" style="width: 232px;">Product Name</th><th class="sorting_disabled" rowspan="1" colspan="1" style="width: 416px;">
<select name="category" id="category" class="form-control">
<option value="">Category Search</option>
<!--?php
while($row = mysqli_fetch_array($result))
{
echo '<option value="'.$row["category_id"].'"-->'.$row["category_name"].'';
}
?>
</select>
</th><th class="sorting" tabindex="0" aria-controls="product_data" rowspan="1" colspan="1" style="width: 224px;">Product Price</th></tr>
</thead>
<tbody></tbody></table><div id="product_data_processing" class="dataTables_processing panel panel-default" style="display: none;">Processing...</div></div></div><div class="row"><div class="col-sm-5"><div class="dataTables_info" id="product_data_info" role="status" aria-live="polite"></div></div><div class="col-sm-7"><div class="dataTables_paginate paging_simple_numbers" id="product_data_paginate"></div></div></div></div>
</div>
</div>
<script type="text/javascript" language="javascript">
$(document).ready(function(){
load_data();
function load_data(is_category)
{
var dataTable = $('#product_data').DataTable({
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"fetch.php",
type:"POST",
data:{is_category:is_category}
},
"columnDefs":[
{
"targets":[2],
"orderable":false,
},
],
});
}
$(document).on('change', '#category', function(){
var category = $(this).val();
$('#product_data').DataTable().destroy();
if(category != '')
{
load_data(category);
}
else
{
load_data();
}
});
});
</script>
2. Source file fetch.php
<!--?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing1");
$column = array("product.id", "product.name", "category.category_name", "product.price");
$query = "
SELECT * FROM product
INNER JOIN category
ON category.category_id = product.category
";
$query .= " WHERE ";
if(isset($_POST["is_category"]))
{
$query .= "product.category = '".$_POST["is_category"]."' AND ";
}
if(isset($_POST["search"]["value"]))
{
$query .= '(product.id LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR product.name LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR category.category_name LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR product.price LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY product.id DESC ';
}
$query1 = '';
if($_POST["length"] != 1)
{
$query1 .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$sub_array = array();
$sub_array[] = $row["id"];
$sub_array[] = $row["name"];
$sub_array[] = $row["category_name"];
$sub_array[] = $row["price"];
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM product";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" =--> intval($_POST["draw"]),
"recordsTotal" => get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
3. File T-SQL database Mysql
--
-- Database: `testing1`
--
-- --------------------------------------------------------
--
-- Table structure for table `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`category_id` int(11) NOT NULL,
`category_name` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `category`
--
INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Mobiles'),
(2, 'Computers'),
(3, 'Clothing'),
(4, 'Beauty Item'),
(5, 'Sports Item'),
(6, 'Toys Item'),
(7, 'Books'),
(8, 'Entertainment Item');
-- --------------------------------------------------------
--
-- Table structure for table `product`
--
CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) NOT NULL,
`category` int(11) NOT NULL,
`name` varchar(250) NOT NULL,
`price` double(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`id`, `category`, `name`, `price`) VALUES
(1, 1, 'Save on BLU Advance 5.5 HD', 74.99),
(2, 2, 'Dell Inspiron 15.6" Gaming Laptop', 860.00),
(3, 3, 'Women''s Slim Sleeveless', 69.00),
(4, 4, 'Andis 1875-Watt Fold-N-Go Ionic Hair Dryer', 17.00),
(5, 5, 'GM Ripple Cricket Grip, Set Of 3', 66.00),
(6, 6, 'Barbie Fashions and Accessories', 12.00),
(7, 7, 'The Ministry of Utmost Happiness', 6.00),
(8, 8, 'The Great Gatsby (3D)', 8.00),
(9, 1, 'iVooMi Me 1+', 49.00),
(10, 2, 'Apple MacBook Air MQD32HN/A 13.3-inch Laptop 2017', 896.00),
(11, 3, 'Balenzia Premium Mercerised Cotton Loafer Socks', 5.00),
(12, 4, 'Organix Mantra Lemon Cold Pressed Essential Oil', 4.50),
(13, 5, 'SpeedArm Cricket Ball Thrower', 15.00),
(14, 6, 'Mattel Bounce Off Game, Multi Color', 10.00),
(15, 7, 'Seven Days With Her Boss', 5.00),
(16, 8, 'Supernatural Season 1-9 DVD', 22.00),
(17, 1, 'InFocus Turbo 5', 189.00),
(18, 2, 'HP 15-bg008AU 15.6-inch Laptop , Jack Black', 350.00),
(19, 3, 'Seven Rocks Men''s V-Neck Cotton Tshirt', 12.00),
(20, 4, 'Exel Elixir Sublime Antioxidant Serum Cream', 55.00),
(21, 5, 'Gray Nicolls Bat Repair Kit', 9.00),
(22, 6, 'Think Fun Rush Hour, Multi Color', 22.00),
(23, 7, 'Pregnancy Notes: Before, During & After', 5.00),
(24, 8, 'Sherlock Season - 4', 15.00),
(25, 1, 'Vivo Y53', 105.00),
(26, 2, 'Dell Inspiron 15-3567 15.6-inch Laptop', 356.00),
(27, 3, 'Fastrack Sport Sunglasses (Black) (P222GR1)', 14.00),
(28, 4, 'Exel Lotion with stabilized Tea Tree Oil', 28.00),
(29, 5, 'Burn Vinyl Hexagonal Dumbbell', 45.00),
(30, 6, 'Cup Cake Surprise Princess', 8.00),
(31, 7, 'Word Power Made Easy', 2.00),
(32, 8, 'Star Wars: The Force Awakens', 5.00),
(33, 1, 'Lenovo Vibe K5 (Gold, VoLTE update)', 65.00),
(34, 2, 'Lenovo 110 -15ACL 15.6-inch Laptop , Black', 225.00),
(35, 3, 'Zacharias Ankle Socks Pack of 12 Pair', 5.00),
(36, 4, 'Exel SUNSCREEN Broad Spectrum UVA & UVB', 26.00),
(37, 5, 'Burn 500124 Inter Lock Mat (Black)', 24.00),
(38, 6, 'Toyshine Devis Boy 9', 10.00),
(39, 7, 'Think and Grow Rich', 2.50),
(40, 8, 'The Jungle Book', 10.00);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `category`
--
ALTER TABLE `category`
ADD PRIMARY KEY (`category_id`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=41;
HAPPY CODING