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

PHP
php 多线程上下文中安全写文件实现代码
PHP类的使用 实例代码讲解
用php实现让页面只能被百度gogole蜘蛛访问的方法
php 学习笔记
PHP编程过程中需要了解的this,self,parent的区别
php 操作excel文件的方法小结
使用PHP获取网络文件的实现代码
PHP 巧用数组降低程序的时间复杂度
php下将XML转换为数组
php 文件上传代码(限制jpg文件)
php 无极分类(递归)实现代码
PHP 采集获取指定网址的内容
PHP 将图片按创建时间进行分类存储的实现代码
PHP 存储文本换行实现方法
PHP 批量更新网页内容实现代码
用PHP查询搜索引擎排名位置的代码
用php实现的获取网页中的图片并保存到本地的代码
php实现首页链接查询 友情链接检查的代码
处理php自动反斜杠的函数代码
php实现的遍历文件夹下所有文件,编辑删除

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


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