PHP中mysql查询输出及分页的综合示例

Sian

index.php

[PHP] 纯文本查看 复制代码

<?php
        // 包含类文件
        include 'page.class.php';
        // 连接数据库
        $link = mysql_connect("localhost", "root", "***") or die("数据库连接失败！");
        // 创建一个分页对象，共521条数据，每页12条数据
        $page = new Page(521, 12);
        // 使用数据库的think表
        mysql_select_db("think");
        // sql查询语句
        $sql = "selec t * from sa_user order by id {$page->limit()}";
        // 结果资源输出
        $result = mysql_query($sql);
        // 制作表格
        echo '<table width = "70%" border = "1" align = "center" >';
        echo "<caption><h2>{$sql}</h2></caption>";
        // 表头字段
        echo "<tr>";
        $fields = mysql_num_fields($result);
        for ($i = 0; $i < $fields; $i++){
                echo "<th>".mysql_field_name($result, $i)."</td>";
        }
        echo "</tr>";
        
        // 内容输出
        while($row = mysql_fetch_row($result)){
                echo "<tr>";
                foreach($row as $str){
                        echo "<td>{$str}</td>";
                }
                echo "</tr>";
        }
        // 尾部分页控件
        echo '<tr><td colspan = "'.$fields.'" align = "right">'.$page->fpage().'</td></tr>';
        echo "</table>";

page.class.php

[PHP] 纯文本查看 复制代码

<?php
        class Page{
                // 总数、每页条数、总页数、当前页码、基础url、起始条、结束条
                private $total, $number, $pages, $cpage, $url, $start, $end;
                
                public function __construct($total, $number){
                        $this->total = $total;
                        $this->number = $number;
                        $this->pages = $this->get_pages();
                        $cpage = $_GET["page"];
                        $this->cpage = empty($cpage) ? 1 : $cpage;
                        $this->url = "http://php.yusian.com";
                        $this->start = ($this->cpage - 1) * $this->number;
                        $end = $this->start + $number;
                        $this->end = ($end > $total) ? $total : $end;
                }
                // 计算总页数
                private function get_pages(){
                        // ceil相当于(($this->total - 1)/$this->number)+1;
                        return ceil($this->total/$this->number);
                }
                // 首页 上一页
                private function first(){
                        $prev_num = $this->cpage - 1;
                        // 首页链接
                        $home_str = "<a href={$this->url}>首页</a>";
                        // 上一页链接，通过url?page=n来实现
                        $prev_str = "<a href={$this->url}?page="."{$prev_num}>上一页</a>";
                        // 如果当前页是第1页，则不显示
                        return ($this->cpage > 1) ? $home_str." ".$prev_str : NULL;
                }
                // 中间页码表
                private function flist(){
                        $list = "";
                        // 默认情况下显示9个页码链接，如果总页数少于9个，则按最大数显示
                        $count = $this->pages > 9 ? 9 : $this->pages;
                        // 如果是9个，则前面4个后面4个，中间一个，因此循环从当前页往前数4个
                        $num = ceil($this->cpage - $count / 2);
                        // 如果前面不足4个，则直接从第1页开始
                        if ($this->cpage < $count / 2){
                                $num = 1;
                        }
                        // 如果后面不足4个，则最大只到最后一页
                        if ($this->cpage > ($this->pages - $count / 2)){
                                $num = $this->pages - $count + 1;
                        }
                        // 开始循环拼接页码链接
                        for($i = 0; $i < $count; $i++){
                                $str = "<a href = {$this->url}?page={$num}>{$num}</a>";
                                // 如果是当前页，则只显示数字，不加超链接
                                if($num == $this->cpage) $str = $num;
                                $list = $list.$str;
                                // 排队最后一个，中间都加两个空格隔开
                                if ($i < $count - 1) $list .= "   ";
                                $num++;
                        }
                        return $list;
                }
                // 下一页 末页
                private function last(){
                        $next_num = $this->cpage + 1;
                        // 下一页链接，即为当前页加1
                        $next_str = "<a href={$this->url}?page={$next_num}>下一页</a>";
                        // 末尾页取总页码数加链接
                        $end_str = "<a href={$this->url}?page={$this->pages}>末页</a>";
                        // 如果当前为最后一页，则不显示
                        return ($this->cpage < $this->pages) ? $next_str." ".$end_str : NULL;
                }
                // 返回结果
                function fpage(){
                        $array = array();
                        $number = $this->end - $this->start;
                        // 将各种下标放到一个数组里再拼接，可根据需要加减组合
                        $array[] = "第{$this->start}-{$this->end}条";
                        $array[] = $this->cpage."/".$this->pages;
                        $array[] = $this->first();
                        $array[] = $this->flist();
                        $array[] = $this->last();
                        $array[] = "共{$this->total}条记录";
                        $array[] = "本页显示{$number}条记录";
                        $string = "";
                        foreach($array as $title){
                                $string .= $title."  ";
                        }
                        return $string;
                }
                
                public function limit(){
                        return "LIMIT {$this->start}, {$this->number}";
                }
        }

table.zip (2.81 KB, 下载次数: 0)

2016-1-7 16:56 上传

点击文件名下载附件