PHP+Bootstrap分页及多条件查询 |

rx online

分页就是对较多的数据按照一定的数量和条件进行分页展示。

分页的展示形式一般有三种：

直接展示记录的页数
加载更多
瀑布流的自动加载

分页的关键变量：

current_page 当前页码
total_num 总记录数
num 每页展示的记录数

分页的注意点：

不能超越极限范围

分页类的封装：

使用面向对象的思想来封装分页类

分页类的美化：

可以使用jQuery、bootstrap来进行美化

分页类的高级用法：

多条件查询可以满足大部分查询需求，按照特定条件进行排序。

分页类的优化：

1 2	SELECT * FROM list LIMIT 1,2000; SELECT * FROM list LIMIT 1000000,2000;

1,1000000叫做偏移量，偏移量越大查询的效率就越低。

pagenation.php

<html>

<head>

</head>

<body>

用户名:<input type="text" name='name' class="form-control" id="exampleInputEmail2" placeholder="" value='<?php if(isset($name) && !empty($name)) echo $name; ?>'>

</div>

年龄:<input type="text" name='age' class="form-control" id="exampleInputPassword2" placeholder="" value='<?php if(isset($age) && !empty($age)) echo $age; ?>'>

</div>

</form>

</div>

<th>年龄

<?php $tmpOne = "/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>"; ?>

<a class='btn <?php if($sortOne=='age' && $orderOne=='plus') echo 'btn-warning';else echo 'btn-success';?>' href='<?php echo $tmpOne;?>&sortOne=age&orderOne=plus&sortTwo=<?php if(!empty($sortTwo)) echo $sortTwo;?>&orderTwo=<?php if(!empty($orderTwo)) echo $orderTwo;?>'>升序</a>

<?php

$str = "<a class='btn ";

if($sortOne=='age' && $orderOne=='less'){

$str .= 'btn-warning';

}else{

$str .= 'btn-success';

}

$str .= " href='/page.php?name='";

if(isset($name) && !empty($name)) $str.= $name;

$str .= '&age=';

if(isset($age) && !empty($age)) $str .= $age;

$str .= '&sortOne=age&orderOne=less';

$str .= '&sortTwo=';

if(!empty($sortTwo)) $str.= $sortTwo;

$str .= '&orderTwo=';

if(!empty($orderTwo)) $str.= $orderTwo;

echo $str."'>";

降序</a>

</th>

<th>创建时间

<a class='btn <?php if($sortTwo=='createtime' && $orderTwo=='plus') echo 'btn-warning';else echo 'btn-success';?>' href='/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&sortTwo=createtime&orderTwo=plus&sortOne=<?php if(!empty($sortOne)) echo $sortOne;?>&orderOne=<?php if(!empty($orderOne)) echo $orderOne;?>'>升序</a>

<a class='btn <?php if($sortTwo=='createtime' && $orderTwo=='less') echo 'btn-warning';else echo 'btn-success';?>' href='/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&sortTwo=createtime&orderTwo=less&sortOne=<?php if(!empty($sortOne)) echo $sortOne;?>&orderOne=<?php if(!empty($orderOne)) echo $orderOne;?>'>降序</a>

</th>

<tr>

<?php foreach($dataRes as $k=>$v){ ?>

<tr >

<?php echo '<td>'.$v['id'].'</td>'; ?>

<?php echo '<td>'.$v['name'].'</td>'; ?>

<?php echo '<td>'.$v['age'].'</td>'; ?>

<?php echo '<td>'.date('Y-m-d H:i:s',$v['createtime']).'</td>'; ?>

</tr>

<?php } ?>

</table>

<li><a href="/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&p=<?php echo ($current_page>=2 ? $current_page-1 : 1); ?>&sortOne=<?php echo $sortOne; ?>&orderOne=<?php echo $orderOne; ?>&sortTwo=<?php echo $sortTwo; ?>&orderTwo=<?php echo $orderTwo; ?>">上一页</a></li>

<?php for($i=1;$i<=$pages;$i++){ ?>

<li <?php if($current_page == $i){ echo "class='active'";}?>><a href="/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&p=<?php echo $i ?>&sortOne=<?php echo $sortOne; ?>&orderOne=<?php echo $orderOne; ?>&sortTwo=<?php echo $sortTwo; ?>&orderTwo=<?php echo $orderTwo; ?>" ><?php echo $i; ?></a></li>

<?php } ?>

<li><a href="/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&p=<?php echo ($current_page<=($pages-1) ? $current_page+1 : $pages); ?>&sortOne=<?php echo $sortOne; ?>&orderOne=<?php echo $orderOne;?>&sortTwo=<?php echo $sortTwo; ?>&orderTwo=<?php echo $orderTwo; ?>">下一页</a></li>

</ul>

</nav>

</body>

</html>

page.php

<?php

// total_num 总记录数

// current_page 当前所在页

// perpage_num 每页显示多少条记录

function dump($arr){

echo "<pre>";

var_dump($arr);

echo "</pre>";

}

header('content-type:text/html;charset=utf-8');

//实例化PDO

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test','root','root');

/**

* 由于引入多条件查询，需要增加where子句

$name = isset($_GET['name']) ? $_GET['name'] : '';

$age = isset($_GET['age']) ? intval($_GET['age']) : 0 ;

$where = ' where 1=1';

if( !empty($name)){

$where .= " and name='".$name."'";

}

if( !empty($age)){

$where .= ' and age='.$age;

}

$order = '';

$sortOne = isset($_GET['sortOne']) ? $_GET['sortOne'] : '';

$orderOne = isset($_GET['orderOne']) ? $_GET['orderOne'] : '';

if(!empty($sortOne) && !empty($orderOne)){

$orderBy = $orderOne == 'plus' ? 'asc' : ' desc';

$order .= " $sortOne $orderBy";

}

$sortTwo = isset($_GET['sortTwo']) ? $_GET['sortTwo'] : '';

$orderTwo = isset($_GET['orderTwo']) ? $_GET['orderTwo'] : '';

if(!empty($sortTwo) && !empty($orderTwo)){

$orderBy = $orderTwo == 'plus' ? 'asc' : ' desc';

$order .= ",$sortTwo $orderBy";

}

if(!empty($order)){

$order = 'order by '.trim($order,',');

}

dump($order);

//查询总记录数

$res = $pdo->query('select count(*) as num from user'.$where);

$dbRes = $res->fetch(PDO::FETCH_ASSOC);

$total_num = $dbRes['num'];

//设定每页显示的条数

$perpage_num = 10;

//计算出总页数

$pages = ceil($total_num/$perpage_num);

$current_page = isset($_GET['p']) ? $_GET['p'] : 1;//默认是第一页

//limit 0,20 第一页 20

//limit 20,20 第二页 20 20-40

//limit 40,20 第三页 20 40-60

$limit = " limit ".(($current_page-1)*$perpage_num).','.$perpage_num;

//筛选出最前的20条记录

$data = $pdo->query("select id,name,createtime,age from user ". $where . $order .$limit);

$dataRes = $data->fetchAll();

/*$table = '<table align=center border=1>';

$table .= '<tr>';

$table .= '<td>序号</td><td>姓名</td><td>年龄</td><td>创建时间</td>';

$table .= '</tr>';

foreach($dataRes as $k=>$v){

$table .='<tr><td>'.$v['id'].'</td><td>'.$v['name'].'</td><td>'.$v['age'].'</td><td>'. date('Y-m-d H:i:s',$v['createtime']).'</td></tr>';

}

$table .= '</table>';

echo $table;

$page = '';

$page .= "<a href='/page.php?p=1'>首页</a> ";

$page .= "<a href='/page.php?p=".($current_page>=2 ? $current_page-1 : 1)."'>上一页</a> ";

for($i=1;$i<=$pages;$i++){

$page .= "<a href='/page.php?p=$i'>";

if($i == $current_page){

$page .= "<font color=red>".$i."</font>";

}else{

$page .= $i;

}

$page .= "</a> ";

}

$page .= "<a href='/page.php?p=".($current_page<=($pages-1) ? $current_page+1 : $pages)."'>下一页</a> ";

$page .= "<a href='/page.php?p=".$pages."'>尾页</a>";

$page .= '| 总共'.$total_num.'条记录,目前是在第'.$current_page.'页';

echo $page;

*/ require './pagenation.php';

page.class.php

<?php

class Page{

public $total_num;

public $perpage_num;

public $current_page;

public $pages;

public function __construct($total_num,$perpage_num,$current_page){

header('content-type:text/html;charset=utf-8');

$this->total_num=$total_num;

$this->perpage_num=$perpage_num;

$this->current_page=$current_page;

$this->pages=ceil($total_num/$perpage_num);

}

public function showPage(){

$page = '';

if($this->current_page!=1){

$page .= "<a href='/page.class.php?p=1'>首页</a> ";

$page .= "<a href='/page.class.php?p=".($this->current_page>=2 ? $this->current_page-1 : 1)."'>上一页</a> ";

}

for($i=1;$i<=$this->pages;$i++){

$page .= "<a href='/page.class.php?p=$i'>";

if($i == $this->current_page){

$page .= "<font color=red>".$i."</font>";

}else{

$page .= $i;

}

$page .= "</a> ";

}

if($this->current_page!=$this->pages){

$page .= "<a href='/page.class.php?p=".($this->current_page<=($this->pages-1) ? $this->current_page+1 : $this->pages)."'>下一页</a> ";

$page .= "<a href='/page.class.php?p=".$this->pages."'>尾页</a>";

}

$page .= '| 总共'.$this->total_num.'条记录,目前是在第'.$this->current_page.'页';

echo $page;

}

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test','root','root');

//查询总记录数

$res = $pdo->query('select count(*) as num from user');

$dbRes = $res->fetch(PDO::FETCH_ASSOC);

$total_num = $dbRes['num'];

//设定每页显示的条数

$perpage_num = 20;

$current_page = isset($_GET['p']) ? $_GET['p'] : 1;//默认是第一页

//limit 0,20 第一页 20

//limit 20,20 第二页 20 20-40

//limit 40,20 第三页 20 40-60

$limit = " limit ".(($current_page-1)*20).',20';

//筛选出最前的20条记录

$data = $pdo->query("select id,name,createtime,age from user ". $limit);

$dataRes = $data->fetchAll();

$table = '<table align=center border=1>';

$table .= '<tr>';

$table .= '<td>序号</td><td>姓名</td><td>年龄</td><td>创建时间</td>';

$table .= '</tr>';

foreach($dataRes as $k=>$v){

$table .='<tr><td>'.$v['id'].'</td><td>'.$v['name'].'</td><td>'.$v['age'].'</td><td>'. date('Y-m-d H:i:s',$v['createtime']).'</td></tr>';

}

$table .= '</table>';

echo $table;

$pageObj=new Page($total_num,$perpage_num,$current_page);

$pageObj->showPage();

PHP+Bootstrap分页及多条件查询

Related Posts

Leave a Reply Cancel reply