收藏
<?php
error_reporting(E_ALL || ~E_NOTICE);
//连接函数
if (!function_exists('myconnect')) {
function myconnect($dbType,$host,$dbname,$user,$pw,$port=0,$charset='utf8') {
if ($dbType == 'mysql') {
//端口号设置
$port = ($port == 0) ? 3306 : $port;
//dsn 建立
$dsn = "{$dbType}:host={$host};dbname={$dbname};charset={$charset};port={$port}";
// echo $dsn;
}
if ($dbType == 'sqlsrv') {
$port = ($port == 0) ? 1433 : $port;
$dsn = "{$dbType}:server=tcp:{$host},{$port};database={$dbname};";
// echo $dsn;
}
try{
$pdo = new PDO($dsn,$user,$pw);
// echo "<h3>数据库连接成功！</h3>";
}catch (PDOException $e){
die('连接失败。'.$e->getMessage());
}
return $pdo;
}
}
//新增函数
if (!function_exists('myinsert')) {
function myinsert($pdo,$table,$data=[]) {
$sql = "INSERT INTO {$table} (";
// Field 部分
foreach (array_keys($data) as $field) {
$sql .= $field.',';
}
//去掉尾部 , 并加上）
$sql = rtrim(trim($sql),',').') ';
//VALUE 部分
$sql .= ' VALUES (';
foreach (array_keys($data) as $value) {
$sql .= ':'.$value.',';
}
$sql = rtrim(trim($sql),',').') ';
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($data as $field => $value) {
$stmt->bindValue(":{$field}",$value);
}
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '新增记录成功！';
return true;
} else {
return false;
}
} else {
// echo '<h3>新增记录失败</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
//更新函数
if (!function_exists('myupdate')) {
function myupdate($pdo,$table,$data=[],$where) {
$sql = "UPDATE {$table} SET ";
// Field 部分
foreach (array_keys($data) as $field) {
$sql .= $field.'=' .':'.$field.',';
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}else{
exit('条件不能为空');
}
echo($sql);
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($data as $field => $value) {
$stmt->bindValue(":{$field}",$value);
}
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '修改记录成功！';
return true;
} else {
return false;
}
} else {
// echo '<h3>修改记录失败</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
//删除函数
if (!function_exists('mydelete')) {
function mydelete($pdo,$table,$where) {
$sql = "DELETE FROM {$table} ";
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}else{
exit('条件不能为空');
}
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($data as $field => $value) {
$stmt->bindValue(":{$field}",$value);
}
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '删除记录成功！';
return true;
} else {
return false;
}
} else {
// echo '<h3>修改记录失败</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
//查询单条函数
if (!function_exists('myselect_one')) {
function myselect_one($pdo,$table,$fields,$where,$order) {
$sql = "SELECT TOP 1 ";
if (is_array($fields)) {
foreach ($fields as $field) {
$sql .= $field.', ';
}
} else {
$sql .= $fields;
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
//FROM 部分
$sql .= ' FROM '.$table ;
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}
//ORDER BY 部分
if (!empty($order)) {
$sql .= ' ORDER BY ' .$order;
}
// echo $sql;
//创建pdo预处理对象
// mssql rowCount()无法返回行数
// mysql rowCount()可返回受影响的行数
// rowCount() 用 prepare()方法时，仅支持CURSOR_SCROLL
$stmt = $pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
if ($stmt->execute()) {
// var_dump($stmt->rowCount());
if ($stmt->rowCount() > 0) {
echo '查询记录成功！';
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
return $stmt->fetch(PDO::FETCH_ASSOC);
} else {
echo '没有匹配的记录！';
return false;
}
} else {
// print_r($stmt->errorInfo());
return false;
}
}
}
//查询符合条件的全部记录函数
if (!function_exists('myselect_all')) {
function myselect_all($pdo,$table,$fields,$where,$order) {
$sql = "SELECT ";
if (is_array($fields)) {
foreach ($fields as $field) {
$sql .= $field.', ';
}
} else {
$sql .= $fields;
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
//FROM 部分
$sql .= ' FROM '.$table ;
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}
//ORDER BY 部分
if (!empty($order)) {
$sql .= ' ORDER BY ' .$order;
}
//创建pdo预处理对象
$stmt = $pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
// var_dump($pdo->prepare($sql));
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '查询记录成功！';
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
echo '没有匹配的记录！';
return false;
}
} else {
// print_r($stmt->errorInfo());
return false;
}
}
}
//调用存储过程函数
if (!function_exists('myexecute')) {
function myexecute($pdo,$proc,$params=[]) {
$sql = "EXEC {$proc} ";
// 参数 部分
foreach (array_keys($params) as $param) {
$sql .= ':'.$param.',';
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
// exit($sql);
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($params as $param => $value) {
$stmt->bindValue(":{$param}",$value);
}
if ($stmt->execute()) {
echo '调用存储过程成功！';
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
echo '<h3>调用存储过程失败！</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
?>点击 "运行实例" 按钮查看在线实例
<?php
/**
*
*/
if (!function_exists('my_mysql_page'))
{
function my_mysql_page($db,$table,$page=1,$num=5){
$offset = ($page-1)*$num;
$sql = "SELECT * FROM {$table} LIMIT {$offset}, {$num};";
$res = mysqli_query($db,$sql);
$rows = mysqli_fetch_all($res,MYSQLI_ASSOC);
//获取总页数分2步:1.获取总记录数,2.再除以每次的显示数量,结果向上取整
$number = mysqli_query($db,"SELECT COUNT(*) FROM {$table}");
list($total) = mysqli_fetch_row($number); //总记录数保存到变量$total中
$pages = ceil($total / $num); //获取到总页数 $pages
//返回当前分页数据与总页数
return ['rows'=>$rows, 'pages'=>$pages];
}
}
if (!function_exists('my_mssql_page'))
{
function my_mssql_page($pdo,$table,$orderfield,$page=1,$num=10){
//mssql无limit语名， 采用排序字段 最大值的方式取数
//sql语句
$offset = $page*$num;
$sql = "SELECT TOP $num * FROM {$table} ";
$sql .= " WHERE ({$orderfield}> ";
$sql .= "(SELECT MAX({$orderfield}) FROM (SELECT TOP $offset {$orderfield} FROM {$table} ORDER BY {$orderfield}) AS x )) ";
$sql .= " ORDER BY {$orderfield}";
// die($sql);
//预处理对象，取数据集
$stmt = $pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// var_dump($rows);
//返回总记录数
$sql = "SELECT COUNT(*) AS total FROM {$table}";
$stmt = $pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
foreach ($stmt->fetch(PDO::FETCH_ASSOC) as $value) {
$rowCount = $value;
}
// echo $rowCount;
//求总页数
$pages = ceil($rowCount / $num);
//返回结果集 和 总页数
return ['rows'=>$rows,'pages'=>$pages];
}
}点击 "运行实例" 按钮查看在线实例
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>封装分页函数,进一步规范代码,实现代码复用</title>
<style>
table,th,td {
border: 1px solid black;
}
table th {
background-color: lightskyblue;
}
table {
border-collapse: collapse;
width: 80%;
margin: 30px auto;
text-align: center;
}
h3 {
font-size: 12pt;
text-align: center;
}
h3 a {
text-decoration: none;
margin-left: 5px;
/*display: inline-block;*/
}
h3 a:hover, .active {
background-color: red;
color: white;
}
form {
display: inline;
}
</style>
</head>
<body>
<?php
//连接数据库
//导入分页函数库
require 'lib/myfun_pdo.php';
require 'lib/myfun_page.php';
$dbType = 'sqlsrv';
$host = '127.0.0.1';
$dbname = 'WsErp';
$user = 'sa';
$pw = 'iloveyou2008';
$pdo = myconnect($dbType,$host,$dbname,$user,$pw);
// $db = myconnect('localhost','root','root','php');
$page = isset($_GET['p']) ? $_GET['p'] : 1;
$num = 15;
$table = 'bs_supplier';
$orderfield = 'keyid';
//调用分页函数
$data = my_mssql_page($pdo,$table,$orderfield,$page,$num);
$rows = $data['rows']; //当前分页数据
$pages = $data['pages']; //总页数
//边界限定
$page = ($page == 0) ? 1 : $page;
$page = ($page > $pages) ? $pages : $page;
?>
<table>
<caption><h2>厂商信息表</h2></caption>
<tr>
<th>厂商编号</th>
<th>厂商名称</th>
<th>简称</th>
<th>联系人</th>
<th>电话</th>
</tr>
<?php foreach ($rows as $row): ?>
<tr>
<td><?php echo $row['KeyId']; ?></td>
<td><?php echo $row['Name']; ?></td>
<td><?php echo $row['jName']; ?></td>
<td><?php echo $row['LinkMan']; ?></td>
<td><?php echo $row['Tel']; ?></td>
</tr>
<?php endforeach;?>
</table>
<h3>
<!-- 第一页的时候不显示首页上一页 -->
<?php if($page != 1): ?>
<a href="http://127.0.0.1/server/0427/job0427.php?p=1">首页</a>
<a href="http://127.0.0.1/server/0427/job0427.php?p=<?php echo $page-1; ?>">上一页</a>
<?php endif; ?>
<!--生成中间页-->
<?php for($i=1; $i<=$pages; $i++): ?>
<a class="<?php if($_GET['p']==$i){echo 'active';}?>" href="http://127.0.0.1/server/0427/job0427.php?p=<?php echo $i ?>"><?php echo $i ?></a>
<?php endfor; ?>
<!--最后一页时 下一页和最后一页不显示-->
<?php if($page != $pages) :?>
<a href="http://127.0.0.1/server/0427/job0427.php?p=<?php echo $page+1; ?>">下一页</a>
<a href="http://127.0.0.1/server/0427/job0427.php?p=<?php echo $pages; ?>">尾页</a>
<?php endif; ?>
<!--页面快速跳转-->
<form action="" method="get">
第
<select name="p" id="">
<?php for($i=1; $i<=$pages; $i++): ?>
<option value="<?php echo $i; ?>" <?php if($_GET['p']==$i){echo 'selected';} ?>><?php echo $i; ?></option>
<?php endfor; ?>
</select>
页
<button>跳转</button>
</form>
</h3>
</body>
</html>点击 "运行实例" 按钮查看在线实例
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
