Detailed explanation of paging and SqlHelper class usage examples in php

<?php

/**

 * Created by JetBrains PhpStorm.

 * User: lee

 * Date: 13-7-26

 * Time: 下午8:30

 * To change this template use File | Settings | File Templates.

 */

class SqlHelper{

  private $mysqli;

  private static $host="localhost";

  private static $user="root";

  private static $pwd="";

  private static $db="world";

  private $sql=false;

  private $result=false;

  function __construct(){

    $this->mysqli=new MySQLi(self::$host,self::$user,self::$pwd,self::$db);

    if($this->mysqli->connect_error){

      die("连接数据库失败！ ".$this->mysql->connect_error);

    }

    $this->mysqli->query("set names utf8");

  }

  function execute_dql_all($sql){

    //执行查询语句

    $arr=array();

    $this->result=$this->mysqli->query($sql) or die ($this->mysql->connect_error);

    //将数据转存到$arr数组中

    while($row=mysqli_fetch_array($this->result,MYSQL_BOTH)){

      $arr[]=$row;

    }

    $this->result->free();

    return $arr;

  }

  function execute_dql_num($sql){

    //执行查询语句

    $arr=array();

    $this->result=$this->mysqli->query($sql) or die ($this->mysql->connect_error);

    //将数据转存到$arr数组中

    while($row=mysqli_fetch_array($this->result,MYSQLI_NUM)){

      $arr[]=$row;

    }

    $this->result->free();

    return $arr;

  }

  function execute_dql_assoc($sql){

    //执行查询语句

    $arr=array();

    $this->result=$this->mysqli->query($sql) or die ($this->mysql->connect_error);

    //将数据转存到$arr数组中

    while($row=mysqli_fetch_array($this->result,MYSQLI_ASSOC)){

      $arr[]=$row;

    }

    $this->result->free();

    return $arr;

  }

  //查询某表中的记录数

  function execute_dql_counts($table,$id="*"){

    $this->sql="select count($id) from $table";

    $this->result=$this->mysqli->query($this->sql);

    $row=mysqli_fetch_all($this->result);

    $this->result->free();

    return $row[0][0];

  }

  function execute_dml($sql){

    //执行正删改

    $this->result=$this->mysqli->query($sql);

    if(!$this->result){

      return -1;//执行正删改失败

    }else{

      if($this->mysqli->affected_rows>0){

        return 1;//执行正删改成功，影响行数

      }else{

        return 0;//执行正删改成功，但没有影响行数

      }

    }

  }

}

<?php

/**

 * Created by JetBrains PhpStorm.

 * User: lee

 * Date: 13-7-27

 * Time: 下午2:48

 * To change this template use File | Settings | File Templates.

 */

header("Content-type:text/html;charset=utf-8;");

require_once("SqlHelper.php");

class Paging {

  private $sqlHelper=false;

  private $pageCount=false;//页数

  private $counts=false;//总记录数

  private $returnArr=false;//分页超链接的分页

  function __construct(){

    $this->sqlHelper=new SqlHelper();

    $this->returnArr=array();

  }

  /*

   * 参数说明

   *

   * $table 分页时对那个表的数据分页

   * $id 辅助查询当前分页的数据表的总记录数

   * $pageSize 每页显示多少条信息记录数

   * $pagingSize 分页栏每次循环显示出来的个数

   * $nowPage 当前是第几页，默认第一页

   * $href 分页栏的超链接将要往哪里连接

   */

  function paging_prev_next($table,$id="*",$pageSize,$pagingSize,$nowPage=1,$href){

    $this->counts=$this->sqlHelper->execute_dql_counts($table,$id);

    $this->pageCount=ceil($this->counts/$pageSize);

    $this->returnArr["count"]=$this->counts;

    $this->returnArr["start"]=($nowPage-1)*$pageSize;

    $this->returnArr["limit"]=$pageSize;

    if($nowPage>$this->pageCount || $nowPage<=0){

      return false;

    }

    $t=(ceil($nowPage/$pagingSize)-1)*$pagingSize+1;

    $pre=$nowPage-$pagingSize;

    $nex=$nowPage+$pagingSize;

    echo "

      <span class=&#39;paging-list-a paging-list-a-withBg&#39;>{$nowPage}/{$this->pageCount}</span>

      <a href=&#39;{$href}?nowPage={$pre}&#39; class=&#39;paging-list-a&#39;><</a>";

    for($i=$t;$i<$t+$pagingSize;$i++){

      if($i*$pageSize>$this->pageCount*$pageSize){

        break;

      }else{

        if($nowPage==$i){

          echo "

          <a href=&#39;{$href}?nowPage={$i}&#39; class=&#39;paging-list-a paging-list-a-withBg&#39;>{$i}</a>";

        }else{

          echo "

          <a href=&#39;{$href}?nowPage={$i}&#39; class=&#39;paging-list-a&#39;>{$i}</a>";

        }

      }

    }

    echo "

      <a href=&#39;{$href}?nowPage={$nex}&#39; class=&#39;paging-list-a&#39;>></a>";

    return $this->returnArr;

  }

}

/**

 * Created by JetBrains PhpStorm.

 * User: lee

 * Date: 13-7-27

 * Time: 下午5:56

 * To change this template use File | Settings | File Templates.

 */

.paging-list-a{

  border:1px solid #b5b5af;

  background-color:#efebed;

  font-family: 'Meiryo UI';

  font-size: 16px;

  font-weight: 600;

  padding: 0px 8px 0px 8px;

  /*cursor: pointer;*/

  text-decoration: none;

  color: #292927;

}

.paging-list-a-withBg{

  background-color: #1D92E2;

  color: white;

}

<!DOCTYPE html>

<html>

<head>

  <title></title>

  <link rel="stylesheet" type="text/css" href="paging-list-link.css">

</head>

<body>

<?php

header("Content-type:text/html;charset=utf-8;");

require_once 'Paging.php';

$paging=new Paging();

//参数说明

/*

 * $table 分页时对那个表的数据分页

 * $id 辅助查询当前分页的数据表的总记录数

 * $pageSize 每页显示多少条信息记录数

 * $pagingSize 分页栏每次循环显示出来的个数

 * $nowPage 当前是第几页，默认第一页

 * $href 分页栏的超链接将要往哪里连接，当前页链接地址

 */

//控制起始页为

$nowPage=1;

if(isset($_GET["nowPage"])){

  $nowPage=$_GET["nowPage"];

}

//定义分页所需参数

$meiyexiansi=10;

$meiyelianjieshu=10;

$receiveArr=array();

$receiveArr=$paging->paging_prev_next("city","ID",$meiyexiansi,$meiyelianjieshu,$nowPage,"usePaging.php");

//容错判断

if(!$receiveArr){

  return;

}

//查询每页需要显示的数据，大小限制存在 $receiveArr 数组中

$sqlHelper=new SqlHelper();

$result=$sqlHelper->execute_dql_num("select * from city limit ".$receiveArr['start'].",".$receiveArr['limit']."");

echo "<pre class="brush:php;toolbar:false">";

print_r($result);

echo "

if($nowPage>$this->pageCount || $nowPage<=0){

  return false;

}