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

2022-07-15 80酷酷网 80kuku.com

以下为引用的内容：
<?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."’ solid #CCC;text-align:center;width:20px;’/> 页 ";
str.= "<a href=’#’ onClick=\"window.location=’".this->url."’+document.getElementById(’go_num_text’).value;\" 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
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
print_r(arr);
echo ’</pre>’;
?>

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