PHP连接与操作数据库常用代码
============连接数据库用到的代码========================================
//这里的代码可保存到单的conn.php文件， 在其它文件中用require "admin/conn.php";引用，或Require_once "conn.php";
$servername = "192.168.13.77";
$username = "root";
$password = "root";
$dbname = "ctcms";
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查数据库连接
if ($conn->connect_error) {
die("数据库连接失败：" . $conn->connect_error);
}
// 设置字符编码为 UTF-8 //这里很重要，没有这句PHP上传到mysql中的是GBK编码，在PHPMYamdin中显示乱码
if (!$conn->set_charset("utf8")) {
die("设置字符编码失败: " . $conn->error);
}
============================================================================
// 获取地址栏参数
$val = isset($_POST['val']);
===========分页代码1，仅有页码============================
// 定义每页显示的数据条数
$records_per_page = 16;
// 获取当前页数
if (isset($_GET['page']) && $_GET['page'] > 0) {
$page = $_GET['page'];
} else {
$page = 1;
}
// 计算查询的起始偏移量，即当前页从第多少条数据开始
$offset = ($page - 1) * $records_per_page;
//开始输出数据
$sql = "SELECT * FROM tbcatalog ORDER BY Path ASC, OrderID DESC LIMIT $offset, $records_per_page";
。。。。
//表格底部还要加上如下代码
// 计算总页数
$total_pages_sql = "SELECT COUNT(*) as count FROM tbcatalog";
$total_pages_result = $conn->query($total_pages_sql);
$total_pages_row = $total_pages_result->fetch_assoc();
$total_pages = ceil($total_pages_row["count"] / $records_per_page);
注：这里的$total_pages_sql语句中的where语句和order必须与输出数据的sql一样，但不能为LIMIT子语句，否则分页总数为0
// 显示分页链接
for ($i = 1; $i <= $total_pages; $i++) {
echo "<a href='?page=" . $i . "'>" . $i . " </a> ";
}
注：页面如有多个参数或条件还需加上，如：echo "<a href='".$myurl."&page=" . $i . "'>" . $i . " </a> ";
=============分页代码2，适用于后台，需要控制每页显示代码数================================================
----共？条记录，每页？条，共？页 上一页 4 5 6 7 8 9 10 11 12 13 14 下一页 每页显示？条记录
// 定义每页显示的数据条数及默认每页数据条数
$records_per_page = isset($_GET['listnum']) ? $_GET['listnum'] : '30';
// 获取当前页数
$page = (isset($_GET['page']) && $_GET['page'] > 0)? $_GET['page'] : 1;
// 计算查询的起始偏移量
$offset = ($page - 1) * $records_per_page;
// 构建 URL
$myurl = "txtlist.php?Path=" . urlencode($fPath); //urllencode()用来确保URL中特殊字符被正确编码
$myurl = $myurl."&listnum=" . urlencode($records_per_page);
$curl = $myurl . "&page=" . urlencode($page);
$sql="select id,yesno,typeflag,path,title,ltime from ct_txthtml order by cast(ltime as datetime) desc LIMIT $offset, $records_per_page";
。。。。。
<?php
// 创建分页控件
// 计算总页数
$total_pages_sql="select COUNT(*) as count from ct_txthtml order by cast(ltime as datetime) desc";
$total_pages_result = $conn->query($total_pages_sql);
$total_pages_row = $total_pages_result->fetch_assoc();
$total_pages = ceil($total_pages_row["count"] / $records_per_page);
$rowcount=$total_pages_row["count"];
//输出总记录数，每页显示记录，总页数
echo "共".$rowcount."条记录，每页".$records_per_page."条，共".$total_pages."页 ";
//输出上一页，下一页，及当前页前后5页链接
if ($total_pages > 1) {
if ($page > 1) {
echo "<a href='".$myurl."&page=" . ($page - 1) . "'>上一页</a> ";
}
if ($page>5){ $minj= $page-5;}else{$minj=1;}
if ($page<$total_pages-5){ $maxj= $page+5;}else{$maxj=$total_pages;}
for ($j=$minj; $j<=$maxj; $j++){
if ($j==$page){echo "<a href='".$myurl."&page=" . $j . "' style='color:red'>" . $j . "</a> ";}else{echo "<a href='".$myurl."&page=" . $j . "'>" . $j . "</a> ";};
}
if ($page < $total_pages) {
echo "<a href='".$myurl."&page=" . ($page + 1) . "'>下一页</a> ";
}
}
?>
//输出每页显示指定条数记录
<select name="pagesize" id="pagesize" onchange="javascript:window.location.href='?path=<?php echo urlencode($fPath); ?>&page=<?php echo urlencode($page); ?>&listnum=' + this.options[this.selectedIndex].value" >
<?php
echo "<option value=''>选择每页显示条数...</option>";
for ($i = 10; $i <=50 ; $i+=10) {
echo "<option value=".$i.">每页显示".$i."条记录</option>";
}
for ($i = 60; $i <=180 ; $i+=20) {
echo "<option value=".$i.">每页显示".$i."条记录</option>";
}
for ($i = 200; $i <=1000 ; $i+=200) {
echo "<option value=".$i.">每页显示".$i."条记录</option>";
}
?>
</select>
============分页代码3，适用于前台，不需要控制每页显示条数=============================================================================================
页次：1/5 每页30 总数140 首页 上一页 下一页 尾页 转到 第1页
// 定义每页显示的数据条数
$records_per_page = 30;
// 获取当前页数
if (isset($_GET['page']) && $_GET['page'] > 0) {
$page = $_GET['page'];
} else {
$page = 1;
}
// 计算查询的起始偏移量，即当前页从第多少条数据开始
$offset = ($page - 1) * $records_per_page;
$sql = "SELECT id,typeflag,title,ltime FROM ct_txthtml WHERE typeflag = ? LIMIT $offset, $records_per_page";
.........
<?php
// 计算总页数
$total_pages_sql = "SELECT COUNT(*) as count FROM ct_txthtml WHERE typeflag =".$AllPath;
$total_pages_result = $conn->query($total_pages_sql);
$total_pages_row = $total_pages_result->fetch_assoc();
$total_pages = ceil($total_pages_row["count"] / $records_per_page);
//输出页次，即当前页/总页数，每页记录数，总记录数
echo "页次：".$page."/".$total_pages." 每页".$records_per_page." 总数".$total_pages_row["count"]." ";
//输出首页，上一页，下一页，尾页
echo "<a href='listmore.php?AllPath=".$AllPath."&page=1'>首页</a> ";
if ($page > 1) {
echo "<a href='listmore.php?AllPath=".$AllPath."&page=".($page - 1)."'>上一页</a> ";
}
if ($page < $total_pages) {
echo "<a href='listmore.php?AllPath=".$AllPath."&page=".($page + 1)."'>下一页</a> ";
}
echo "<a href='listmore.php?AllPath=".$AllPath."&page=".$total_pages."'>尾页</a> ";
?>
//输出跳转到多少页
转到  <select name="pagesize" id="pagesize" onchange="javascript:window.location.href='?AllPath=<?php echo urlencode($AllPath); ?>&page=' + this.options[this.selectedIndex].value" >
<?php
for ($i = 1; $i <=$total_pages ; $i++) {
echo "<option value=".$i.">第".$i."页</option>";
}
?>