html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link rel="stylesheet" href="static/css/bootstrap.min.css">
<link rel="stylesheet" href="static/css/index.css">
<link rel="stylesheet" href="static/css/bootstrap-table.min.css">
<script src="static/js/jquery-3.2.0.min.js" type="text/javascript"></script>
<script src="static/js/popper.min.js" type="text/javascript"></script>
<script src="static/js/bootstrap.min.js" type="text/javascript"></script>
<script src="static/js/bootstrap-table.min.js" type="text/javascript"></script>
<script src="static/js/bootstrap-table-en-US.js" type="text/javascript"></script>
</head>
<body>
<div><img class="img-fluid" src='static/picture/download_pic.png'></div>
<div class='ui-library-content'>
<div class="row pt-3 pb-3">
<div class="col-md-5">
<input class="form-control" type="text" id='geneId' value="" placeholder="Search for gene...">
<div class='ui-library-search-eg'>e.g. <a class="text-primary" style="color: #8EC21E!important;"
href="#" οnclick="$('#geneId').val('AT1G22920')">AT1G22920</a>, <a class="text-primary"
style="color: #8EC21E!important;" href="#"
οnclick="$('#geneId').val('IC4R-OSJ01G002300')">IC4R-OSJ01G002300</a>, <a class="text-primary"
style="color: #8EC21E!important;" href="#"
οnclick="$('#geneId').val('Pbr000006.2')">Pbr000006.2</a>
</div>
</div>
<div class="col-md-2">
<button οnclick="search()" type="button" class="btn btn-success">Search</button>
</div>
</div>
<div class='ui-library-table ui-library-tab'>
<table id="table" class="table table-hover">
</table>
</div>
</div>
<script type="text/javascript">
$(function () {
// ?????
Date.prototype.Format = function (fmt) {
var o = {
"M+": this.getMonth() + 1, //??
"d+": this.getDate(), //?
"H+": this.getHours(), //??
"m+": this.getMinutes(), //?
"s+": this.getSeconds(), //?
"q+": Math.floor((this.getMonth() + 3) / 3), //??
"S": this.getMilliseconds() //??
};
if (/(y+)/.test(fmt))
fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
for (var k in o)
if (new RegExp("(" + k + ")").test(fmt))
fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
}
$('#table').bootstrapTable({
method: 'get',
toolbar: '#toolbar', //?????????
striped: true, //????????
cache: false, //??????????true???????????????????*?
pagination: true, //???????*?
sortable: true, //??????
sortOrder: "asc", //????
pageNumber: 1, //??????????????
pageSize: 20, //????????*?
pageList: [20, 50, 100], //???????????*?
url: 'select.php',
queryParamsType: 'limit', //???? 'limit' ,?????? ??????????offset,limit,sort???? '' ????????????????pageSize,pageNumber
queryParams: queryParams,//????????????????????????????????????????????????
pagination: true,
sidePagination: "server", //?????client??????server??????*?
//search: true, //?????????????????????????????????????
strictSearch: true,
showColumns: false, //????????
showRefresh: false, //????????
minimumCountColumns: 2, //???????
clickToSelect: true, //?????????
searchOnEnterKey: true,
columns: [{
field: 'ChromosomeName',
title: 'ChromosomeName',
align: 'center',
width: '15%',
sortable: true,
formatter: function (value, row, index) {
return "<a class='text-primary' style='color: #8EC21E!important;' href='/web/geneSearch/getInfo?id=" + row.id + "'>" + value + "</a>";
}
}, {
field: 'GeneID',
title: 'GeneID',
width: '15%',
align: 'center',
sortable: true
}, {
field: 'StartLocation',
title: 'StartLocation',
width: '15%',
align: 'center',
sortable: true
}, {
field: 'EndLocation',
title: 'EndLocation',
width: '15%',
align: 'center',
sortable: true
}]
});
$("#geneId").keyup(function (event) {
if (event.keyCode == 13) {
search();
}
});
});
function queryParams(params) { //????
var temp = {
pageNumber: params.pageNumber,
pageSize: params.pageSize,
sortName: params.sortName, //????
sortOrder: params.sortOrder, //?????desc?asc?
geneId: $("#geneId").val()
};
return temp;
}
function search() {
var geneId = $("#geneId").val()
if (geneId == null || geneId == "") {
alert("please input gene ID");
}
else {
$("#table").bootstrapTable('refresh');
}
}
</script>
</body>
</html>
php:
<?php
// 设置响应头为 JSON 格式
header('Content-Type: application/json');
// 连接数据库（这里以 MySQL 为例，使用 mysqli 扩展）
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
$connection = new mysqli($servername, $username, $password, $dbname);
if ($connection->connect_error) {
die("Connection failed: ". $connection->connect_error);
}
// 获取请求参数
$pageNumber = isset($_GET['pageNumber'])? $_GET['pageNumber'] : 1;
$pageSize = isset($_GET['pageSize'])? $_GET['pageSize'] : 20;
$sortName = isset($_GET['sortName'])? $_GET['sortName'] : null;
$sortOrder = isset($_GET['sortOrder'])? $_GET['sortOrder'] : 'asc';
$geneId = isset($_GET['geneId'])? $_GET['geneId'] : null;
// 构建 SQL 查询语句
$sql = "SELECT * FROM gene";
$whereConditions = [];
if ($geneId!== null) {
$whereConditions[] = "geneId LIKE '%$geneId%'";
}
if (!empty($whereConditions)) {
$sql.= " WHERE ". implode(" AND ", $whereConditions);
}
if ($sortName!== null) {
$sql.= " ORDER BY $sortName ". ($sortOrder === 'asc'? 'ASC' : 'DESC');
}
$sql.= " LIMIT ". (($pageNumber - 1) * $pageSize). ", ". $pageSize;
$result = $connection->query($sql);
$data = [];
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
}
// 获取总记录数用于分页
$totalCountSql = "SELECT COUNT(*) AS total FROM gene";
if ($geneId!== null) {
$totalCountSql.= " WHERE geneId LIKE '%$geneId%'";
}
$totalCountResult = $connection->query($totalCountSql);
$totalCountRow = $totalCountResult->fetch_assoc();
$totalCount = $totalCountRow['total'];
// 关闭数据库连接
$connection->close();
// 返回 JSON 格式的数据
echo json_encode([
'total' => $totalCount,
'rows' => $data
]);
?>
