使用PHP和MYSQL分页显示数据,如果再加筛选功能,则很容易与分页功能相矛盾,这里使用SESSION简单解决这一问题
主要文件 manage.php
<?php
session_start();
$username=$_SESSION['username'];
$d=date("Y-m-d");
$j=date("w");
$_SESSION['date']=isset($_SESSION['date'])?$_SESSION['date']:">='$d'";
$_SESSION['ball']=isset($_SESSION['ball'])?$_SESSION['ball']:"is not null";
$_SESSION['time']=isset($_SESSION['time'])?$_SESSION['time']:"is not null";
$date1=$_SESSION['date'];
$ball=$_SESSION['ball'];
$time=$_SESSION['time'];
header("Content-type:html/text;charset=utf-8");
?>
<link rel="stylesheet" href="../public/style.css" />
<style type="text/css">
.bga{
background-color:green;
}
.bgb{
background-color:purple;
}
.bgd{
background-color:#2BD4D7;
}
.bgc{
background-color:blue;
}
input{ border:5px thin green;color:blue;width:200px; height:30px;}
input{font-family:楷体;font-size:20px;color:#06F87C;}
font{color:purple; font-size:20px;font-weight:bold;font-family:楷体;}
//td{font-family:楷体;font-size:25px;}
button{width:100px; height:30px;}
</style>
<script src="./../public/jquery.js">
</script>
<script>$(document).ready(function(){
///$('tr:even').addClass('bga');
//$('tr:odd').addClass('bgb');
$('tr:eq(0)').addClass('bgc');
});
$(document).ready(function(){
$('tr:gt(0)').hover(function(){
$(this).addClass('bgd');
},function(){
$(this).removeClass('bgd');
});
});
</script>
<?php
require "./../public/init.php";
require "../public/Page.class.php";
$page = isset($_GET['page'])?(int)$_GET['page']:1;
//获取总记录数
$sqlx = "select count(*) as total from place where date $date1 and ball $ball and time_start $time ";
$total =$pdo->query($sqlx)->fetchColumn();
//实例化分页类
$Page = new Page($total,8,$page); //page(总页数,每页显示条数,当前页)
//获取limit条件
$limit = $Page->getLimit();
//获取分页HTML链接
$page_html = $Page->showPage();
$sql="select * from place where date $date1 and ball $ball and time_start $time limit $limit ";
$info=array();
$info=$pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
?>
<?php
if($_SESSION['status']!="管理员"){
echo "<script>alert('非管理员,不可进入!');history.back();</script>";
}else{
?>
<div align="center">
<form name="form1" align="center" method="post" action="">
<font>球类:</font>
<select name="ball" size="1" >
<option value="is not null" selected>所有</option>
<option value="='篮球'">篮球</option>
<option value="='乒乓球'" >乒乓球</option>
<option value="='羽毛球'" >羽毛球</option>
<option value="='排球'">排球</option>
</select>
<font>日期:</font><select name="date" size="1" >
<option value=">='<?php echo $d;?>'" selected>所有</option>
<?php do{ ?>
<option value="='<?php echo $d; ?>'"><?php echo $d;?>
<?php $d=date("Y-m-d",strtotime("$d+1 day"));
$j=$j+1;
?></option>
<?php }while($j<=7&&$j>1) ?>
</select>
<font>开始时间:</font><select name="time" size="1" >
<option value="is not null" selected>所有</option>
<option value="='08:00:00'">8:00-10:00</option>
<option value="='10:00:00'" >10:00-12:00</option>
<option value="='14:00:00'" >14:00-16:00</option>
</select>
<button type="submit" name="submit1" value="筛选" >筛选</button>
</form>
</div>
<?php
if($_POST['submit1']=='筛选'){
$_SESSION['ball']=$_POST['ball'];
$_SESSION['date']=$_POST['date'];
$_SESSION['time']=$_POST['time'];
$date1=$_SESSION['date'];
$ball=$_SESSION['ball'];
$time=$_SESSION['time'];
$page = isset($_GET['page'])?(int)$_GET['page']:1;
//获取总记录数
$sqlx = "select count(*) as total from place where date $date1 and ball $ball and time_start $time ";
$total =$pdo->query($sqlx)->fetchColumn();
//实例化分页类
$Page = new Page($total,8,$page); //page(总页数,每页显示条数,当前页)
//获取limit条件
$limit = $Page->getLimit();
//获取分页HTML链接
$page_html = $Page->showPage();
$sql="select * from place where date $date1 and ball $ball and time_start $time limit $limit ";
$info=array();
$info=$pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
$arr1=explode("'",$ball);
$ballx=$ball=="is not null"?"所有":$arr1[1];
$arr2=explode("'",$date1);
$datex=$arr2[0]==">="?"所有":$arr2[1];
$arr3=explode("'",$time);
$timex=$time=="is not null"?"所有":$arr3[1];
echo "<div align='center'>筛选条件:$ballx,$datex,$timex</div>";
?>
<div align="center">
<table valign="middle" border="0">
<tr height="50" bgcolor="blue" >
<td width="100" align="center">球类</td>
<td width="100" align="center">场地号</td>
<td width="150" align="center">日期</td>
<td width="200" align="center">开始时间</td>
<td width="200" align="center">结束时间</td>
<td width="80" align="center">预约者</td>
<td width="100" align="center">操作</td>
</tr>
<?php
foreach($info as $v):
?>
<tr height="35">
<td align="center"> <?php echo $v['ball']; ?></td>
<td align="center"> <?php echo $v['No']; ?></td>
<td align="center"> <?php echo $v['date']; ?></td>
<td align="center"> <?php echo $v['time_start']; ?></td>
<td align="center"> <?php echo $v['time_end']; ?></td>
<td align="center"> <?php echo $v['UserNo']; ?></td>
<td width="100" align="center"><a href="cancel.php?id=<?php echo $v['placeNo']; ?>" title="点击此处,取消该场地的预约">取消预约</a></td>
</tr>
<?php
endforeach;
?>
</table>
</div>
<div class="pagelist"><?php echo $page_html; ?></div>
<div align="center">
<button title="预约者全部清空,所有日期都增加7" ><a href="cancelAll.php">全部置空</a></button>
</div>
<?php } ?>
使用条件查询时,条件变量存贮在SESSION中,这样即使使用“下一页”来刷新页面,条件已经固定,因此能获得想要的数据。由于条件表达式有所差别,理由ball="篮球" 与ball is not null ,因此等号要放在条件值里面,条件对应的值(例如变量名为$a1,从SESSION中获取的值)是 “=‘篮球’”或“is not null”,内带有变量的,外面双引号,变量用单引号,例如:“>='$d' ”,这样变量的值才能解析出来。所以查询语句就变成了:sql="select * from place where ball $a1"。
加载运行的其他重要文件包括init.php——用于连接数据库:
<?php
header('Content-Type:text/html;charset=utf-8');
$dsn='mysql:host=localhost;dbname=ydc;charset=utf8';
$options=array(PDO::MYSQL_ATTR_INIT_COMMAND=>"SET NAMES 'UTF8' ");
//$options这句话防止输出时,出现中文乱码
try{
$pdo=new PDO($dsn,'root','',$options);
}catch(PDOException $e){
exit('PDO连接数据库失败:'.$e->getMessage());
}
//echo 'PDO 连接数据库成功';
?>
控制分页的类,所在的文件为Page.class.php:
<?php
class Page{
private $total; //总记录数
private $pagesize; //每页显示的条数
private $current; //当前页
private $maxpage; //总页数
/**
* 分页类构造方法
* @param $total int 总记录数
* @param $pagesize int 每页显示的条数
* @param $current int 当前页
*/
public function __construct($total,$pagesize,$current){
$this->total = $total;
$this->pagesize = $pagesize;
$this->current = max($current,1);
$this->maxpage = ceil( $this->total / $this->pagesize );
}
//获取SQL中的limit条件
public function getLimit(){
//计算limit条件
$lim = ($this->current -1) * $this->pagesize;
return $lim.','.$this->pagesize;
}
//获得URL参数,用于在生成分页链接时保存原有的GET参数
private function getUrlParams(){
$params = $_GET; //接收GET参数
unset($params['page']); //删除参数中的page
return http_build_query($params); //重新构造GET字符串
}
//生成分页链接
public function showPage(){
//如果少于1页则不显示分页导航
if($this->maxpage <= 1) return '';
//获取原来的GET参数
$url = $this->getUrlParams();
//拼接URL参数
$url = $url ? '?'.$url.'&page=' : '?page=';
//拼接“首页”
$first = '<a href="'.$url.'1">[首页]</a>';
//拼接“上一页”
$prev = ($this->current == 1) ? '[上一页]' : '<a href="'.$url.($this->current-1).'">[上一页]</a>';
//拼接“下一页”
$next = ($this->current == $this->maxpage) ? '[下一页]' : '<a href="'.$url.($this->current+1).'">[下一页]</a>';
//拼接“尾页”
$last = '<a href="'.$url.$this->maxpage.'">[尾页]</a>';
//组合最终样式
return "当前为 {$this->current}/{$this->maxpage} {$first} {$prev} {$next} {$last}";
}
}
另外还有Jquery文件(这里不再多提)和用于修饰的style.css:
body{margin:0;font-family:'Microsoft YaHei';background:url('../images/bg.png');}
.box{width:1001px;margin:0 auto;}
.top{height:179px;background:url('../images/top.jpg');position:relative;}
.title{padding-left:60px;color:#000;font-size:40px;letter-spacing:5px;line-height:160px;}
.nav{position:absolute;right:0;bottom:6px;}
.nav a{color:#000;padding:6px 12px;background:#9cb945;text-decoration:none;}
.nav a:hover{color:#fff;}
.main{padding-bottom:10px;background:#fff;color:#333;}
.news-title{font-size:20px;text-align:center;padding:15px 0;width:70%;margin:0 auto;border-bottom:1px solid #999;font-weight:bold;}
.news-time{text-align:center;margin-top:10px;}
.news-content{width:70%;margin:20px auto;}
.news-edit{width:80%;margin:0 auto;padding-top:20px;}
.news-edit th{width:80px;font-weight:normal;vertical-align:top;padding-bottom:10px;}
.news-edit td{padding-bottom:10px;}
.news-edit input[type=text]{width:60%;border:1px solid #A2BBEA;padding:4px;border-radius:5px;font-family:'simsun';}
.news-edit input[type=text]:hover{background:#eeeeff;}
.news-edit textarea{width:80%;height:150px;border:1px solid #A2BBEA;padding:4px;border-radius:5px;font-family:'simsun';}
.news-edit textarea:hover{background:#eeeeff;}
.news-edit input[type=submit]{padding:5px 12px;cursor:pointer;}
.news-list{width:1000px;border-collapse:collapse;margin-bottom:10px;}
.news-list th{background:#0088cc;color:#fff;padding-top:5px;padding-bottom:5px;border:2px solid #fff;}
.news-list td{border-bottom:2px dashed #ccc;padding:8px;}
.news-list-title{text-indent:20px;}
.news-list a{color:#116FCE;text-decoration:none;}
.news-list a:hover{text-decoration:underline;}
.center{text-align:center;}
.pagelist{text-align:center;padding-bottom:10px;}
.pagelist a{color:#116FCE;text-decoration:none;}
.pagelist a:hover{text-decoration:underline;}
.action{margin-bottom:15px;}