当前位置: 首页 > 图文教程 > 网络编程 > PHP > 用PHP操作MySql数据库(分页)

PHP
PHP 开发环境的选择、建立及使用(5)
PHP 开发环境的选择、建立及使用(6)
PHP 开发环境的选择、建立及使用(7)
PHP 开发环境的选择、建立及使用(8)
PHP 开发环境的选择、建立及使用(9)
Win2003下APACHE PHP5 MYSQL4 PHPMYADMIN 的简易安装配置
PHP新手上路（八） 文件上传
PHP新手上路（九） 投票系统
PHP新手上路（十） 简易banner动态更替
PHP新手上路（十一） 数据库链接
PHP新手上路（十二）使用PHP来操作Oracle数据库
PHP新手上路（十三）PHP资源
PHP新手上路（十四） 其他杂项
session全教程(一)
session全教程(二)
session全教程(三)
PHP编码规范
第十五节--Zend引擎的发展 -- Classes and Objects in PHP5 [15]
第十四节--命名空间 -- Classes and Objects in PHP5 [14]
第十二节--类的自动加载 -- Classes and Objects in PHP5 [12]

No. « 195 196 197 198 »

技术文章搜索

关键字

用PHP操作MySql数据库(分页)

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-08-14   浏览: 100 ::
收藏到网摘: n/a

以下为引用的内容：
<?php
/*
* mysql数据库 分页类
* @package pagelist
* @author yytcpt(无影)
* @version 2008-03-27
* @copyrigth http://www.d5s.cn/ 
*/
/*
* 分页样式
.page{float: left;font: 11px Arial, Helvetica, sans-serif; padding:6px 0; margin: 0px 10%; margin-top: 10px;}
.page a, .page strong{padding: 2px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none;}
.page a:visited{padding: 2px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none;}
.page .break{padding: 2px 6px; border: none; background: #fff; text-decoration: none;}
.page strong{padding: 2px 6px; border-color: #999; font-weight: bold; font-size: 13px; vertical-align: top; background: #fff;}
.page a:hover{color: #fff; background: #0063DC; border-color: #036; text-decoration: none;}
.page a:hover div{color: #FFF;cursor: pointer !important;cursor: hand;}
*/
class pagelist{
var page; //分页页码
var sql; //分页sql
var img_path; //图标路径
var img; //图标名称
var img_btn; //图标地址
var page_size; //设置每页显示条数
var num_btn; //设置数字分页的显示个数
var total_pages; //一共分多少页
var total_records; //一共有多少条记录
var url;
var table;
var new_sql; //指定的SQL语句
var db;
function __construct(){
global db;
this->db = db;
tmp_page = intval(trim(_GET["page"]));
this->page = empty(tmp_page)?1:tmp_page;
this->set_table();
this->page_size = 20;
this->num_btn = 9;
this->img_path = ’/images/’;
this->img = array("ico_first.gif", "ico_front.gif", "ico_next.gif", "ico_last.gif");
}
function set_table(){
this->table["tablename"] = "";
this->table["id"] = "id";
this->table["orderby"] = this->table["id"];
this->table["descasc"] = "DESC";
this->table["fileds"] = "*";
this->table["where"] = "";
}
function set_img(){
this->img_btn[0] = "<img src=’".this->img_path.this->img[0]."’ alt=’首页’ border=’0’ align=’absmiddle’/>";
this->img_btn[1] = "<img src=’".this->img_path.this->img[1]."’ alt=’上一页’ border=’0’ align=’absmiddle’/>";
this->img_btn[2] = "<img src=’".this->img_path.this->img[2]."’ alt=’下一页’ border=’0’ align=’absmiddle’/>";
this->img_btn[3] = "<img src=’".this->img_path.this->img[3]."’ alt=’末页’ border=’0’ align=’absmiddle’/>";
}
function set_show_page(){
this->set_img(); //设置翻页图片路径
this->set_url();
this->set_total_records();
if (this->total_records<this->page_size){
this->total_pages = 1;
}else{
this->total_pages = ceil(this->total_records/this->page_size);
}
if (this->page>this->total_pages){
this->page = this->total_pages;
}
}
function show_first_prv(){
if (this->page==1){
str = "<strong>".this->img_btn[0]."</strong> <strong>".this->img_btn[1]."</strong>";
}else{
str = "<a href=’".this->url."1"."’>".this->img_btn[0]."</a> "; //此处1为首页，page值为1
str.= "<a href=’".this->url.(this->page-1)."’>".this->img_btn[1]."</a>";
}
return str;
}
function show_next_last(){
if (this->page>=this->total_pages){
str = "<strong>".this->img_btn[2]."</strong> <strong>".this->img_btn[3]."</strong>";
}else{
str = "<a href=’".this->url.(this->page+1)."’>".this->img_btn[2]."</a> ";
str.= "<a href=’".this->url.this->total_pages."’>".this->img_btn[3]."</a>";
}
return str;
}
function show_num_text(){
str = " 转到第 <input id=’go_num_text’ type=’text’ value=’".this->page."’ style=’border:0;border-bottom:1px solid #CCC;text-align:center;width:20px;’/> 页 ";
str.= "<a href=’#’ onClick=\"window.location=’".this->url."’+document.getElementById(’go_num_text’).value;\" style=’font-family: Arial, Helvetica, sans-serif;font-weight:bold;font-size:14px;’>[Go]</a>";
return str;
}
function show_num_select(){
if (this->total_pages<50){
str = "<select onchange=\"if(this.options[this.selectedIndex].value!=’’){location=this.options[this.selectedIndex].value;}\">";
for (i=1; i<=this->total_pages; i++){
str.= "<option value=’".this->url.i."’ ".(this->page==i ? " selected=’selected’":"").">".i."</option>";
}
str.= "</select> ";
}else{
str = "";
}
return str;
}
function show_num_btn(){
if (this->page>=1 and this->page<=this->total_pages){
tmp_p = (this->num_btn-1)/2;
if ((this->page - tmp_p)<=0){
start_p = 1;
}else{
if ((this->page-tmp_p)>this->num_btn and (this->page-tmp_p)>(this->total_pages - this->num_btn+1)){
start_p = this->total_pages - this->num_btn + 1;
}else{
start_p = this->page - tmp_p;
}
}
if ((this->page+tmp_p) < this->total_pages){
end_p = (this->page + tmp_p)<this->num_btn?this->num_btn:(this->page + tmp_p);
if (end_p>this->total_pages){
end_p = this->total_pages;
}
}else{
end_p = this->total_pages;
}
}
str = "";
for (i=start_p; i<=end_p; i++){
if (i==this->page){
str.= " <strong>".i."</strong> ";
}else{
str.= " <a href=’".this->url.i."’>".i."</a> ";
}
}
return str;
}
function show_page_info(){
str = " 共".this->total_records."条/".this->total_pages."页";
return str;
}
function show_page(){
if (this->total_records<1){
this->set_show_page();
}
str = this->total_pages>1 ? this->show_first_prv().this->show_num_btn().this->show_next_last().this->show_page_info().this->show_num_text():"";
return str;
}
//总页数
function set_total_pages(){
this->total_pages = ceil(this->total_records/this->page_size);
}
//总记录数
function set_total_records(){
if (this->total_records==0 or !isset(this->total_records)){
if (empty(this->count_sql) and !empty(this->table["tablename"])){
sql = "SELECT count(".this->table["id"].") as count_id FROM `".this->table["tablename"]."` ".(this->table["where"]!=""?" WHERE ".this->table["where"]:"");
}else{
sql = preg_replace("/SELECT(.*?)FROM(.*?)/i", "SELECT count(id) AS count_id FROM\\2", this->sql);
}
arr = this->db->row_query_one(sql);
this->total_records = arr["count_id"];
}
}
/*
* 根据sql返回查询数据
* 指定sql时，不必指定limit
*/
function get_rows_by_sql(sql){
this->sql = sql." LIMIT ".this->page_size*(this->page-1).", ".this->page_size; //指定的SQL;
return this->db->row_query(this->sql);
}
/*
* 最常用的分页方法，只需要传3个参数
* tablename 表名， where 查询条件， orderby 排序字段（默认以id倒序排列）
*/
function get_rows(tablename, where="", orderby=""){
this->table["tablename"] = tablename;
this->table["where"] = where;
orderby ? this->table["orderby"] = orderby : "";
arr = array(
"page" => this->show_page(), //分页代码
"rows" => this->get_rows_by_sql(), //记录数
"sum" => this->total_records, //总记录数
);
return arr;
}
/*
* 特殊查询，sql_query 查询sql语句, row_count 统计总数
*/
function get_rows_sql(sql_query, row_count=0) {
this->total_records = row_count;
arr["rows"] = this->get_rows_by_sql(sql_query);
arr["page"] = this->show_page();
arr["sum"] = this->total_records;
return arr;
}
function get_sql(){
if (this->total_records>10000) {
this->sql = "SELECT ".this->table["fileds"]." FROM `".this->table["tablename"]."` ".(this->table["where"]!=""?" WHERE ".this->table["where"].’ AND ’.this->table["id"].’>=’:’ WHERE ’.this->table["id"].’>=’).’(SELECT ’.this->table["id"].’ FROM `’.this->table["tablename"].’` ORDER BY ’.this->table["id"].’ LIMIT ’.this->page_size*(this->page-1).’, 1)’." ORDER BY ".this->table["orderby"]." ".this->table["descasc"]." LIMIT ".this->page_size;
}else{
this->sql = "SELECT ".this->table["fileds"]." FROM `".this->table["tablename"]."` ".(this->table["where"]!=""?" WHERE ".this->table["where"]:"")." ORDER BY ".this->table["orderby"]." ".this->table["descasc"]." LIMIT ".this->page_size*(this->page-1).", ".this->page_size;
}
//SELECT * FROM articles ORDER BY id DESC LIMIT 0, 20
//SELECT * FROM articles WHERE category_id = 123 AND id >= (SELECT id FROM articles ORDER BY id LIMIT 10000, 1) LIMIT 10
return this->sql; //SQL语句
}
function set_url(){
arr_url = array();
parse_str(_SERVER["QUERY_STRING"], arr_url);
unset(arr_url["page"]);
if (empty(arr_url)){
str = "page=";
}else{
str = http_build_query(arr_url)."&page=";
}
this->url = "http://"._SERVER["HTTP_HOST"]._SERVER["PHP_SELF"]."?".str;
}
}
?>

 

如果您加了新功能，或者是有改进，请与大家一起分享。

测试代码如下，db.php 请到这里下载：

http://dwww.cn/news/2008-6/2008662043517349.shtml

<?php
db_config["hostname"] = "127.0.0.1"; //服务器地址
db_config["username"] = "root"; //数据库用户名
db_config["password"] = "root"; //数据库密码
db_config["database"] = "wap_blueidea_com"; //数据库名称
db_config["charset"] = "utf8";
config["charset"] = "utf-8"; //网站编码

include(’db.php’);
include(’pagelist.php’);
db = new db();
db->connect(db_config);
header("content-type:text/html;charset=".config["charset"]);//设置页面编码
pl = new pagelist();
arr = pl->get_rows(’table_name’);
unset(pl);
echo ’<pre style="text-align:left">’;
print_r(arr);
echo ’</pre>’;
//指定特殊 sql 时候
pl = new pagelist();
sql = ’SELECT * FROM `wap_article` AS a, `wap_article_info` AS b WHERE a.id=b.articleid’;
arr = pl->get_rows_sql(sql);
unset(pl);
echo ’<pre style="text-align:left">’;
print_r(arr);
echo ’</pre>’;
?>

当表中的记录总数在 10000条以上时，使用了 子查询分页，这样效率会更高一些，数据量小的时候，直接查询更快。这个是老王说的，参考这里：
http://hi.baidu.com/thinkinginlamp/blog/item/c5dea0ecdfef5e392697910f.html