通用功能: 快速填写参数用于自己的mysql数据表搜索,ajax载入数据
<?php
header("content-Type: text/html; charset=UTF-8");
//error_reporting(0);
$dbhost = "localhost"; //数据库地址本地localhost
$dbuser = "chalidecom"; //数据库账号
$dbpass = "7b4ZEGJTKy"; //数据库密码
$dbname = "chalidecom"; //数据库名称
$dbport = "3306"; //数据库端口号
$dbcode = "UTF8"; //数据库编码 UTF8 GB2312
$biao = "chafenba"; //数据表名称
$pagesize = 10; //每页数量
$maxp = 10; //最大显示页数
$title = "某某查询系统"; //页面标题
$b = array();
$b[$biao]["name"]= "成绩表"; //表格名称
$b[$biao]["duan"]= "姓名,学号,身份证号,科目1,科目2,科目3,科目4,科目5,科目,科目N";//显示字段
$b[$biao]["sox"] = "姓名,学号,身份证号";//查询条件
/*
//可以连续多组以上数据用于多表
$b["biao2"]["name"]= "其他表"; //表格名称
$b["biao2"]["duan"]= "姓名,学号,身份证号,科目1,科目2,科目3,科目4,科目5,科目,科目N";//显示字段
$b["biao2"]["sox"] = "姓名,学号,身份证号";//查询条件
*/
$biao = (isset($_GET['biao']))?addslashes($_GET['biao']):$biao;
if($_GET['Act']=="cha"){
$stime=microtime(true);
$biao = (isset($_POST['biao']))?addslashes($_POST['biao']):$biao;
$search = (isset($_POST["rame"]))?addslashes($_POST["rame"]):'';
$sort = (isset($_POST['sort']))?addslashes($_POST['sort']):"id";//
$order = (isset($_POST['orda']))?addslashes($_POST['orda']):"desc";
$duan = (isset($_POST['duan']))?addslashes($_POST['duan']):"";
$iduan = $b[$biao]["duan"]; $sox = $b[$biao]["sox"];
$duanx=explode(",", $iduan);
$duans = "`".join("`,`",$duanx)."`";
$soxs=explode(",", $sox);
if (!$b[$biao]) exit("该表【{$biao}】不可查(未授权)!");
if (!stristr("-desc-asc-","-$order-")) $order = "DESC";
if (!stristr(",$iduan,","-$sort-")) $sort=$soxs[0]; //排序字段:条件1
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) { die("Conn.Error: " . $conn->connect_error);}
$sql = "SELECT $duans FROM `{$biao}` ";
$tips = "字段[{$duan}]";
$sqlw = "WHERE `{$duan}` LIKE '%$search%' ";
$sql .= "$sqlw ORDER BY $sort $order ";
$sqc = "SELECT COUNT($sort) AS count FROM `{$biao}` $sqlw ";
$result = $conn->query($sqc);
if (!$result) exit("连接失败信息:".mysqli_error($conn));
$row = $result->fetch_assoc();
$count = $row["count"];
if($count<1) exit("查 表[$biao] => $tips => 关键词[$search]无结果");
$pagecount = ceil($count / $pagesize);
$page = 1;
if (isset($_POST["page"])) { $page = $_POST["page"];}
if ($page < 1) { $page = 1;}
if ($page > $maxp) { exit("[Most <b>$maxp</b> Page]");}
if ($page > $pagecount) { $page = $pagecount;}
if ($pagecount > $maxp) { $pagecount = $maxp;}
$start = ($page - 1) * $pagesize;
$end = $start + $pagesize;
$sql .= " LIMIT $start, $pagesize";
$result = $conn->query($sql);
if (!$result) exit("连接失败信息:".mysqli_error($conn));
echo "<table cellspacing=\"0\">";
foreach ($result as $i=>$row) {
if(!$isti){
echo "<tr class='tt'>\r\n<th>".join("</th>\r\n<th>",array_keys($row))."</th>\r\n</tr>\r\n";
$isti="Y";
}
echo "<tr>\r\n<td>".join("</td>\r\n<td>",$row)."</td>\r\n</tr>\r\n";
}
echo "</table>";
$etime=microtime(true);
$total = $etime-$stime;
echo "<p>结果: $count, 页数: $pagecount 耗时: ".round($total,3)." \r\n";
if ($page > 1) { echo "<a href='#' onclick=\"show(1,'');\">首页</a> ";}
for ($i = 1; $i <= $pagecount; $i++) {
if($i == $page){ echo "$i ";}else{ echo "<a href='#' onclick=\"show($i,'');\">$i</a> ";}
}
if ($page < $pagecount) { echo "<a href='#' onclick=\"show($pagecount,'');\">尾页</a> ";}
if($count> $maxp*$pagesize) echo "结果{$count}较多:推荐优化输入!";
echo "</p>";
$conn->close();
exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title><?php echo $title; ?></title>
<meta name="viewport" content="width=device-width,minimum-scale=1.0,maximum-scale=1.0">
<meta name="apple-mobile-web-app-capable" content="yes" />
<style>
*{margin:0;padding:0;box-sizing:border-box}
body{font-family:Arial,sans-serif;font-size:14px;line-height:1.5;background-color:#eee}
header{display:flex;justify-content:space-between;align-items:center;background-color:#333;color:#fff;padding:5px 10px;}
.logo{font-size:16px;}
a {text-decoration:none;}
p{color:green;} b{color:blue;}
nav ul{display:flex;}
nav li{list-style:none;margin-left:10px;}
nav a{color:#fff;text-decoration:none;padding:5px;}
nav a:hover{background-color:#fff;color:#333}
.search{display:flex;justify-content:space-between;align-items:center;background-color:#fff;padding:10px;margin:10px 0;}
.search select{flex:1;padding:4px;}
.search input{flex:5;padding:5px;border:1px solid #ccc;margin-right:5px;}
.captcha{flex:2;display:flex;border:1px solid #ccc;align-items:center;margin-right:5px;}
.captcha input{border:0;padding:5px;margin-right:0px;}
.captcha img{height:22px;}
button{flex:1;background-color:#333;color:#fff;border:none;padding:4px 6px;cursor:pointer;}
button:hover{background-color:green;color:white;}
.content{background-color:#fff;padding:10px;min-height:360px;margin-bottom:88px;overflow-x:auto;}
.description{font-weight:bold;margin-bottom:10px;}
table{width:100%;border-collapse:collapse;}
table td,table th{padding:10px 0;border:1px solid #ccc;}
table th{background-color:#ddd;font-weight:bold;}
footer{display:flex;justify-content:space-between;font-size:12px;align-items:center;background-color:#333;color:#fff;padding:5px;position:fixed;bottom:0;width:100%;}
.footer div{margin-right:20px;}
.right a{color:#fff;text-decoration:none;}
.right a:hover{text-decoration:underline;}
@media screen and (max-width:656px){.search select,.search input,.search captcha,.search button{display:block;width:99.9%;}
}
</style>
</head>
<body>
<header>
<div class="logo"><?php echo $title; ?></div>
<nav>
<ul>
<?php
foreach ($b as $tt=>$vvv) {echo "<li><a href=\"?biao=$tt\">{$vvv["name"]}</a></li>\r\n";}
?>
</ul>
</nav>
</header>
<div class="search">
<select name="duan" id="duan" onchange="show(1,'');">
<?php
$sox = $b[$biao]["sox"];
$soxs=explode(",", $sox); $ia = count($soxs);
for($ii=0; $ii<$ia; $ii++){
$duen=$soxs[$ii]; echo "<option value=\"$duen\">$duen</option>\r\n";
}
?>
</select>
<input type="text" id="rame" placeholder="输入关键词" onfocus="this.select();">
<button onclick="show(1,'');">立即查找</button>
</div>
<div class="set" style="display:none;">
<input type="hidden" id="biao" value="<?php echo $biao; ?>">
<input type="hidden" id="page" value="1">
<input type="hidden" id="sort" value="id">
<input type="hidden" id="orda" value="DESC">
<input type="hidden" id="Act" value="cha">
</div>
<div class="content">
<div class="description">查询结果</div>
<div id="UpTip"></div>
</div>
<footer>
<div class="left">版权所有 © 2021</div>
<div class="mid">备案号：123456789</div>
<div class="right"><a href="#">链接右</a></div>
</footer>
</body>
<script>
function $(objId){ return document.getElementById(objId);}
var timer = null; //延时1秒自动查，延时与中文输入节能
var Inputs = document.querySelector('#rame');
Inputs.addEventListener('input', function(e) {
var keyword = e.target.value;
if (timer) { clearTimeout(timer);}
timer = setTimeout(function() { show(1,keyword); }, 1000);
});
function show(page,key){
$("page").value = page;
if(key != "") $("rame").value = key;
var fd = new FormData();
fd.append('rame',$("rame").value);
fd.append('biao',$("biao").value);
fd.append('orda',$("orda").value);
fd.append('page',$("page").value);
fd.append('sort',$("sort").value);
fd.append('duan',$("duan").value);
fd.append('Act',$("Act").value);
var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function(){
if(xhr.readyState == 4){
if(xhr.status == 200){
$('UpTip').innerHTML=xhr.responseText;
}
}
};
xhr.open('POST','?Act='+$("Act").value+'&t='+new Date(),true);
xhr.send(fd);
}
</script>
</html>