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

PHP
PHP新手总结的PHP基础知识
php实现gb2312和unicode间编码转换
用php语言实现数据库连接详细代码介绍
详细解析 PHP 向 MySQL 发送数据过程
利用PHP V5开发多任务应用程序
详细讲解PHP中缓存技术的应用
php escapeshellcmd多字节编码漏洞
《PHP设计模式介绍》导言
《PHP设计模式介绍》第一章 编程惯用法
《PHP设计模式介绍》第二章 值对象模式
《PHP设计模式介绍》第三章 工厂模式
《PHP设计模式介绍》第四章 单件模式
《PHP设计模式介绍》第五章 注册模式
《PHP设计模式介绍》第六章 伪对象模式
《PHP设计模式介绍》第七章 策略模式
《PHP设计模式介绍》第八章 迭代器模式
《PHP设计模式介绍》第九章 观测模式
《PHP设计模式介绍》第十章 规范模式
《PHP设计模式介绍》第十一章 代理模式
《PHP设计模式介绍》第十二章 装饰器模式

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


出处:互联网   整理: 软晨网(RuanChen.com)   发布: 2009-08-14   浏览: 185 ::
收藏到网摘: 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