sqlhelper.class.php dao与分页方法封装
<?php
class sqlhelper{
public $conn;
public $username="root";
public $password=123456;
public $dbname='empmanage';
public $host='localhost';
function __construct(){
$this->conn=mysql_connect($this->host,$this->username,$this->password);
if(!$this->conn){
die("连接失败".mysql_error());
}
mysql_select_db($this->dbname,$this->conn);
}
//dql语句,执行查询操作
function execute_dql($dql){
$res=mysql_query($dql,$this->conn) or die(mysql_error());
$arr=array();
while ($row=mysql_fetch_assoc($res)){
$arr[]=$row;
}
mysql_free_result($res);
return $arr;
}
//dml语句,执行增删改操作
function execute_dml($dml){
$res_bool=mysql_query($dml,$this->conn) or die(mysql_error());
if(!$res_bool){
return 0;
}else{
if(mysql_affected_rows($this->conn)>0){
return 1;//表示执行成功
}else{
return 2;
}
}
}
//关闭连接
function close_conn(){
if(!empty($this->conn)){
mysql_close($this->conn);
}
}
//分页功能
/**($pagenow-1)*$pagesize
$sql1="select * from emp limit 3,6";
$sql2="select count(id) from emp "*/
function execute_dql_fengyePage($sql1,$sql2,$fengyePage){
$res=mysql_query($sql1) or die(mysql_error());
$res_arr=array();
//将结果集倒腾到数组中,得到显示数据
while ($row=mysql_fetch_assoc($res)){
$res_arr[]=$row;
}
mysql_free_result($res);
$fengyePage->res_arr=$res_arr;
$res2=mysql_query($sql2) or die(mysql_error());
if($row=mysql_fetch_row($res2)){
//向上取整,计算出总共多少页
$fengyePage->pagecount=ceil($row[0]/$fengyePage->pagesize);
//总共多少条记录
$fengyePage->rowcount=$row[0];
}
/* 实现上下页翻页功能 */
$navigate="";
if($fengyePage->pagenow>1){
$prePage=$fengyePage->pagenow-1;
$navigate="<a href='{$fengyePage->gotoURL}?pagenow=$prePage'>上一页 </a>";
}
if($fengyePage->pagenow<$fengyePage->pagecount){
$nextpage=$fengyePage->pagenow+1;
$navigate.="<a href='{$fengyePage->gotoURL}?pagenow=$nextpagePage'>下一页 </a>";
}
//动态十页十页的翻页
$page_whole=10;
$start=floor(($fengyePage->pagenow-1)/$page_whole)*$page_whole+1;
$index=$start;
//整体向前翻页
$preindex=$start-1;
$navigate.="<a href='{$fengyePage->gotoURL}?pagenow=$preindex'><< </a>";
//首先判断10页以后是否超过总页数
if($fengyePage->pagenow+10<=$fengyePage->pagecount){
for(;$start<$index+10;$start++){
$navigate.="<a href='{$fengyePage->gotoURL}?pagenow=$start'>[$start] </a>";
}
$navigate.="<a href='{$fengyePage->gotoURL}?pagenow=$start'>>> </a>";
}else{
//超过总页数,则按照到总页数为止
for(;$start<=$fengyePage->pagecount;$start++){
$navigate.="<a href='{$fengyePage->gotoURL}?pagenow=$start'>[$start] </a>";
}
}
$navigate.="当前第{$fengyePage->pagenow}页 ";
$navigate.="共{$fengyePage->pagecount}页";
$fengyePage->navigate=$navigate;
}
}
fengyePage.class
<?php
class fengyePage{
public $pagesize=6;
public $res_arr;//要显示的数据
public $rowcount;//总共多少条记录
public $pagenow;//当前页面
public $pagecount;//总共页数
public $navigate;//导航条
public $gotoURL;
}
?>
empService.class.php
<?php
require_once 'sqlhelper.class.php';
class empService{
function getfengyePage($fengyePage){
$sqlhelper=new sqlhelper();
$sql1="select * from emp limit ".($fengyePage->pagenow-1)*$fengyePage->pagesize.",$fengyePage->pagesize";
$sql2="select count(emp_id) from emp";
$sqlhelper->execute_dql_fengyePage($sql1, $sql2, $fengyePage);
}
function delEmpById($emp_id) {
$sql="delete from emp where emp_id=$emp_id";
$sqlhelper=new sqlhelper();
return $sqlhelper->execute_dml($sql);
}
function addEmp($emp_name,$grade,$email,$salary) {
$sqlhelper=new sqlhelper();
$sql="insert into emp (emp_name,grade,email,salary) values('$emp_name',$grade,'$email',$salary)";
return $sqlhelper->execute_dml($sql);
}
}
?>
empList.php
<html>
<head>
</head>
<?php
require_once 'empService.class.php';
require_once 'fengyePage.class.php';
$fengyePage=new fengyePage();
$fengyePage->pagenow=6;
$fengyePage->pagesize=6;
$fengyePage->gotoURL="empList.php";
//获取超链接传递的数据
if(!empty($_GET['pagenow'])){
$fengyePage->pagenow=$_GET['pagenow'];
}
$empService=new empService();
$empService->getfengyePage($fengyePage);//执行分页查询结果,返回结果到$fengyePage对象中
echo "<table border='1px' bordercolor='green'>";
//将表头显示出来
echo "<tr><th>emp_id</th><th>emp_name</th><th>grade</th><th>email</th><th>salary</th></tr>";
//显示查询数据
for($i=0;$i<count($fengyePage->res_arr);$i++){
$row=$fengyePage->res_arr[$i];
echo "<tr><td>{$row['emp_id']}</td><td>{$row['emp_name']}</td><td>{$row['grade']}</td><td>{$row['email']}</td><td>{$row['salary']}</td>
<td><a href='empProcess.php?flag=del&emp_id={$row['emp_id']}'>删除用户</a></td><td><a href='addEmp.php'>添加用户</a></td></tr>";
}
echo "<h1>雇员信息表</h1>";
echo "</table>";
echo "$fengyePage->navigate";
//跳转到某个页面
?>
<form action="empList.php" method="get">
跳转到:<input type="text" name="pagenow">
<input type="submit" value="GO">
</form>
</html>