- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在关注本教程: http://coderexample.com/datatable-custom-column-search/
这是演示: http://coderexample.com/demo/dataTable-custom-column-search/
注意在演示中搜索输入已删除/隐藏。
这是一个工作FIDDLE但这不是服务器端示例,而是列搜索和整体搜索结合使用那里的测试数据。
这是对每列进行过滤的代码
## Columns Search
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee WHERE 1 = 1";
// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){ //name
$sql.=" AND employee_name LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){ //salary
$sql.=" AND employee_salary LIKE '%".$requestData['columns'][1]['search']['value']."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
$sql.=" AND employee_age LIKE '%".$requestData['columns'][2]['search']['value']."%' ";
}
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; // adding length
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
这里是对每个整体搜索输入进行过滤的代码:
// if there is a search parameter
if( !empty($requestData['search']['value']) ) {
// if there is a search parameter
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$sql.=" WHERE employee_name LIKE '%".$requestData['search']['value']."%' "; // $requestData['search']['value'] contains search parameter // to search for ou to get satou
$sql.=" OR employee_salary LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR employee_age LIKE '%".$requestData['search']['value']."%' ";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result without limit in the query
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; // $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length.
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); // again run query with limit
} else {
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
}
我想做的是让所有列搜索和整体搜索结合起来,按照上面的 fiddle 示例(例如员工姓名搜索中的
> 和 o
2
在整体搜索
中)。
我认为这与 if 语句有关。我尝试过各种排列,但认为可能还有更多。有人可以建议吗?
if( !empty($requestData['search']['value']) ) {
//code here
}
if( !empty($requestData['columns'][0]['search']['value']) ){ //name
//code here
}
if( !empty($requestData['columns'][1]['search']['value']) ){ //salary
//code here
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
//code here
}
编辑1
这是我的完整代码,我很难将 @Ruslan Osmanov 的答案的不同部分放在哪里。你能提供建议吗?我已经尝试过,但收到500(内部服务器错误)
,所以这可能与我连接到数据库的顺序有关?
<?php
/* Database connection start */
/* $servername = "localhost";
$username = "root";
$password = "Password1";
$dbname = "test"; */
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
/* Database connection end */
// storing request (ie, get/post) global array to a variable
$requestData= $_REQUEST;
$columns = array(
// datatable column index => database column name
0 =>'employee_name',
1 => 'employee_salary',
2=> 'employee_age'
);
// getting total number records without any search
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
//* this column search works but not the overall search
## Search input
// if there is a search parameter
if( !empty($requestData['search']['value']) ) {
// if there is a search parameter
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$sql.=" WHERE employee_name LIKE '%".$requestData['search']['value']."%' "; // $requestData['search']['value'] contains search parameter // to search for ou to get satou
$sql.=" OR employee_salary LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR employee_age LIKE '%".$requestData['search']['value']."%' ";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result without limit in the query
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; // $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length.
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); // again run query with limit
} else {
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
}
## Columns Search
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee WHERE 1 = 1";
// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){ //name
$sql.=" AND employee_name LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){ //salary
$sql.=" AND employee_salary LIKE '%".$requestData['columns'][1]['search']['value']."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
$sql.=" AND employee_age LIKE '%".$requestData['columns'][2]['search']['value']."%' ";
}
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; // adding length
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$data = array();
while( $row=mysqli_fetch_array($query) ) { // preparing an array
$nestedData=array();
$nestedData[] = $row["employee_name"];
$nestedData[] = $row["employee_salary"];
$nestedData[] = $row["employee_age"];
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
最佳答案
我猜你的 SQL 中有一些逻辑错误。我认为您不需要 OR
,因为总体搜索只是每列的额外要求:
$conn = mysqli_connect($servername, $username, $password, $dbname) or
die("Connection failed: " . mysqli_connect_error());
$requestData = $_REQUEST;
$columns = ['employee_name', 'employee_salary', 'employee_age'];
$sql = "SELECT COUNT(*) FROM employee";
$res = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalData = mysqli_num_rows($res) ? mysqli_fetch_row($res)[0] : 0;
$overall_search_value = empty($requestData['search']['value']) ? null :
mysqli_real_escape_string($conn, $requestData['search']['value']);
for ($i = 0; $i < count($columns); $i++) {
$field = $columns[$i];
$value = empty($requestData['columns'][$i]['search']['value']) ? null :
mysqli_real_escape_string($conn,
$requestData['columns'][$i]['search']['value']);
$q = null;
if ($value) {
$q []= "$field LIKE '%$value%'";
}
if ($overall_search_value) {
$q []= "$field LIKE '%$overall_search_value%'";
}
if ($q) {
// Every column must match overall search string
// along with the current column search.
$q_where []= '(' . implode(' AND ', $q) . ')';
}
}
$q_where = implode(' AND ', $q_where);
$order_by_col = $columns[$requestData['order'][0]['column']];
$order_by_dir = $requestData['order'][0]['dir'];
$sql = "SELECT employee_name, employee_salary, employee_age
FROM employee WHERE $q_where
ORDER BY $order_by_col $order_by_dir
LIMIT {$requestData['start']}, {$requestData['length']}";
$res = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($res);
$data = [];
while ($row = mysqli_fetch_assoc($res)) {
$data[] = $row;
}
echo json_encode([
"draw" => intval($requestData['draw']),
"recordsTotal" => intval($totalData),
"recordsFiltered" => intval($totalFiltered),
"data" => $data
]);
注意mysqli_real_escape_string
。您必须转义进入 SQL 查询的所有内容,尤其是来自用户输入的内容。
关于javascript - 数据表+列搜索+整体搜索协同工作+服务器端处理,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36879723/
我正在阅读Head First Design Patterns一书,在第382页上说: Composite Patterns is used when you have collection of o
我将我的电脑用作 wifi 热点,并将其他设备连接到它。然后使用wireshark,我可以看到特定设备上发送/接收的数据包,但是我怎样才能看到设备正在查看的完整网页而不是单独的数据包? 最佳答案 尝试
在 URL 中打开我的 Jenkins 时,我似乎无法登录它会抛出消息“用户缺少整体/读取权限”。我试过answer来自类似的问题,但禁用安全性不起作用。 Jenkins 继续抛出错误。有人建议我一起
我已经使用工具分配测试了我的应用程序并得到了这张图片: 如图所示,我的应用程序正常工作,但它通常以总字节数计值吗?我担心“#Allocations (Net/Overall)”,因为它的颜色是红色。这
我遵循教程 Deployment on Tomcat without modification of monitored webapps (beta) .监控站点正在运行,我可以看到统计页面。问题是
所以我有一个 div,其中整个东西都是一个 anchor 标记,我试图控制颜色在悬停时的显示方式并获得不同的结果。希望我能用 CSS 做到这一点。发生的事情是悬停,一个文本发生变化,但另一个没有。但是
我创建了一个程序,您可以在其中输入行驶的英里数和每 jar 油使用的加仑数,该程序会显示每 jar 油的 mpg。我使用的是 Visual Studio 2010。当我输入标记值 -1 时,系统会给出
我有一个由 N 个节点组成的 zookeeper 集群(彼此了解)。如果我在 zk 客户端连接字符串中只传递 M < N 个节点地址怎么办?集群的行为是什么? 在更具体的情况下,如果我只从集群中传递
我一直在试图弄清楚当使用诸如 pushViewController:animated、presentModalViewController:animated 和 UITabBarViewControl
我已经在 VIM 中安装了 minikube,并且我拥有具有所有权限的服务帐户 token 。是否有来自 kubernetes 的 API 来获取资源使用情况(总体)。 最佳答案 要获取 CPU 和内
如何通过 HTTP 客户端(例如 CURL、Insomnia、Postman 等)快速使用使用 JWT token 的 JHipster 生成的应用程序? 最佳答案 嗯,我研究了一段时间,发现你必须遵
我是一名优秀的程序员,十分优秀!