php mysql数据库分页_PHP+mysql数据库简单分页实例-sql分页

/*1.传入页码*/

$page = $_GET['p'];

/*2.根据页码取出数据, php -> mysql*/

$host = "localhost";

$username = "root";

$password = "123456";

$db = "page";

$pageSize = 3;

$showPage = 5;

//连接数据库

$conn = mysqli_connect($host, $username, $password, $db);

if (!$conn) {

var_dump("连接失败");

}

//设置数据库的编码格式，防止乱码

mysqli_query($conn, "SET NAMES UTF8");

//编写sql获取分页数据 SELECT * FROM 表名 LIMIT 起始位置，显示条数

$sql = "SELECT * FROM test LIMIT " . ($page - 1) * $pageSize . ",{$pageSize}";

//把sql语句传送数据中

$result = mysqli_query($conn, $sql);

//处理数据

echo "

";

echo "

echo "

IDname";

while ($row = mysqli_fetch_assoc($result)) {

echo "

{$row['id']}{$row['name']}";

}

echo "

";

//释放结果，关闭连接

mysqli_free_result($result);

//获取数据总数

$total_sql = "SELECT COUNT(*) FROM test";

$total_result = mysqli_fetch_assoc(mysqli_query($conn, $total_sql));

$total = $total_result['COUNT(*)'];

$total_page = ceil($total / $pageSize);

mysqli_close($conn);

//3.显示数据 + 分页条

$page_banner = '

';

if ($page > 1) {

$page_banner .= "首页";

$page_banner .= "上一页";

}else{

$page_banner .= "首页";

$page_banner .= "上一页";

}

//初始化数据

$start = 1; //开始页面

$end = $total_page; //结束页面

$pageOffset = ($showPage - 1) / 2;

if ($total_page > $showPage) { //如果总页面大于显示的页面

if ($page > $pageOffset + 1) { //如果当前页面大于偏移量

$page_banner .= "...";

}

if ($page > $pageOffset) { //当前页大于偏移量

$start = $page - $pageOffset;

$end = $total_page > $page + $pageOffset ? $page + $pageOffset : $total_page;

} else {

$start = 1;

$end = $total_page > $showPage ? $showPage : $total_page;

}

if ($page + $pageOffset > $total_page) {

$start = $start - ($page + $pageOffset - $end);

}

}

for ($i = $start; $i <= $end; $i++) {

if ($page == $i){

$page_banner .="{$i}";

}else{

$page_banner .= "{$i}";

}

}

//尾部省略

if ($total_page > $showPage && $total_page > $page + $pageOffset){

$page_banner .= "...";

}

if ($page < $total_page) {

$page_banner .= "下一页";

$page_banner .= "尾页";

}else{

$page_banner .= "下一页";

$page_banner .= "尾页";

}

$page_banner .= "总页数{$total_page},";

$page_banner .= "

";

$page_banner .= "到第页";

$page_banner .= "";

$page_banner .= "

";

echo $page_banner;

?>