1 <!doctype html>
2 <html lang="en">
3 <head>
4 <!-- Required meta tags -->
5 <meta charset="utf-8">
6 <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
7
8 <!-- Bootstrap CSS -->
9 <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css" integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
10 <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
11 <title>Imooc Users</title>
12 </head>
13 <body>
14 <header>
15 <nav class="navbar navbar-expand-lg navbar-light bg-light">
16 <a class="navbar-brand" href="#">Users List</a>
17 <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
18 <span class="navbar-toggler-icon"></span>
19 </button>
20 <div class="collapse navbar-collapse" id="navbarSupportedContent">
21 <ul class="navbar-nav mr-auto"></ul>
22 <form class="form-inline my-2 my-lg-0" onsubmit="return false;">
23 <input class="form-control mr-sm-2 keywords" type="search" placeholder="Search" aria-label="Search" >
24 <button class="btn btn-outline-success my-2 my-sm-0 searchBtn" type="submit">Search</button>
25 </form>
26 </div>
27 </nav>
28 </header>
29 <!--data-->
30 <div id="container" style="margin:10px">
31 <h2>Imooc Users</h2>
32 <table class="table table-striped">
33 <thead>
34 <tr>
35 <th scope="col">#</th>
36 <th scope="col">UersName</th>
37 <th scope="col">Age</th>
38 <th scope="col">Gender</th>
39 <th scope="col">Phone</th>
40 <th scope="col">Address</th>
41 <th scope="col">Create_at</th>
42 </tr>
43 </thead>
44 <tbody></tbody>
45 </table>
46 <!--pagination-->
47 <nav aria-label="Page navigation example" >
48 <ul class="pagination"></ul>
49 </nav>
50 </div>
51
52 <!-- Optional JavaScript -->
53 <!-- jQuery first, then Popper.js, then Bootstrap JS -->
54 <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
55 <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js" integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut" crossorigin="anonymous"></script>
56 <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js" integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k" crossorigin="anonymous"></script>
57 <script src='../library/ajax.js'></script>
58 <!-- <script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js" ></script> -->
59 <script>
60 let pageNo=1;
61 let kws='';
62 let searchBtn=document.getElementsByClassName('searchBtn')[0];
63 searchBtn.onclick=function(){
64 let search=document.getElementsByClassName('keywords')[0];
65 let keywords=search.value;
66 // console.log(keywords);
67 requestDate(pageNo,keywords);
68 kws=keywords;
69 }
70 let requestPage=function(page){
71 requestDate(page,kws);
72 pageNo=page;
73 }
74
75 let requestDate=function(page_number,keywords){
76 let pagination=document.getElementsByClassName('pagination')[0];
77 let tbody=document.getElementsByTagName('tbody')[0];
78 tbody.innerHTML='<tr><td colspan="7" class="text-center"><i class="fa fa-spinner fa-spin" style="font-size:24px"></i> 加载中 </td></tr>';
79 $.get('users.php',{'page':page_number,'keywords':keywords},function(res){
80 let trs='';
81 if(res.code==1){
82 //请求成功
83 res.rows.forEach(function(item){
84 let tr='<tr><th scope="row">'+ item.id +'</th>' +
85 '<td>'+ item.username+'</td>' +
86 '<td>'+ item.age+'</td>' +
87 '<td>'+ item.gender+'</td>' +
88 '<td>'+ item.phone+'</td>' +
89 '<td>'+ item.address+'</td>' +
90 '<td>'+ item.created_at+'</td>' +
91 '</tr>';
92 trs+=tr;
93 });
94 tbody.innerHTML=trs;
95 //加载页码导航
96 //previous
97 let previousBtn='';
98 if(res.page_number==1){
99 previousBtn+='<li class="page-item disabled" ><a class="page-link" href="JavaScript:requestPage('+ (res.page_number-1) +')">Previous</a></li>';
100 }else {
101 previousBtn+='<li class="page-item " ><a class="page-link" href="JavaScript:requestPage('+ (res.page_number-1) +')">Previous</a></li>';
102 }
103 //next
104 let nextBtn='';
105 if(res.page_total==res.page_number){
106 nextBtn='<li class="page-item disabled"><a class="page-link" href="JavaScript:requestPage('+ (res.page_number+1) +')">Next</a></li>';
107 }else {
108 nextBtn='<li class="page-item "><a class="page-link" href="JavaScript:requestPage('+ (res.page_number+1) +')">Next</a></li>';
109 }
110 let pages=previousBtn;
111 for(let page=1;page<=res.page_total;page++){
112 let active='';
113 if (page==res.page_number) {
114 active=' active';
115 }
116 pages+='<li class="page-item' + active +'"><a class="page-link" href="JavaScript:requestPage('+ page +')">'+ page +'</a></li>';
117 }
118 pages+=nextBtn;
119 pagination.innerHTML=pages;
120 }
121 },'json');
122 };
123 requestDate(1,'');
124 </script>
125 </body>
126 </html>
users.html
1 <?php
2 //请求数据库，相应对应页码的数据
3 //PDO
4 // sleep(1);
5 //接收请求数据
6 $pageNo=isset($_GET['page'])?$_GET['page']:1;
7 $pageSize=5;
8
9 //接收查询参数
10 $keywords=isset($_GET['keywords'])?$_GET['keywords']:'';
11
12
13 $data=[];
14 try {
15 $pdo=new PDO('mysql:host=localhost:3306;dbname=imooc',
16 'root',
17 '',
18 [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]); // 设置 PDO 错误模式为异常
19
20 //请求mysql 查询记录总数
21 $sql='SELECT COUNT(*) AS aggregate FROM imooc_users';
22 if (strlen($keywords)>0) {
23 $sql.= ' WHERE username like ? ';
24 }
25 $stmt=$pdo->prepare($sql);
26 if (strlen($keywords)>0) {
27 $stmt->bindValue(1,'%'.$keywords.'%',PDO::PARAM_STR);
28 }
29 $stmt->execute();
30 $total=$stmt->fetch(PDO::FETCH_ASSOC)['aggregate'];
31 //计算最大页码，设置页码边界
32 $minPage=1;
33 $maxPage=ceil($total/$pageSize);
34 $pageNo=max($pageNo,$minPage);
35 $pageNo=min($pageNo,$maxPage);
36 $offset=($pageNo-1)*$pageSize;
37
38 $pdo->exec('set names utf8');
39 $sql="SELECT id,username,age,gender,phone,address,created_at FROM imooc_users ";
40 if (strlen($keywords)>0) {
41 $sql.= ' WHERE username like ? ';
42 }
43 $sql.='LIMIT ?,?';
44 $stmt=$pdo->prepare($sql);
45 if (strlen($keywords)>0) {
46 $stmt->bindValue(1,'%'.$keywords.'%',PDO::PARAM_STR);
47 $stmt->bindValue(2,(int)$offset,PDO::PARAM_INT);
48 $stmt->bindValue(3,(int)$pageSize,PDO::PARAM_INT);
49 }else {
50 $stmt->bindValue(1,(int)$offset,PDO::PARAM_INT);
51 $stmt->bindValue(2,(int)$pageSize,PDO::PARAM_INT);
52 }
53
54 $stmt->execute();
55 $results=$stmt->fetchAll(PDO::FETCH_ASSOC);
56 $data=[
57 'code'=>1,
58 'msg'=>'ok',
59 'rows'=>$results,
60 'total_records'=>(int)$total,
61 'page_number'=>(int)$pageNo,
62 'page_size'=>(int)$pageSize,
63 'page_total'=>(int)$maxPage,
64
65 ];
66 } catch (PDOException $e) {
67
68 $data=[
69 'code'=>0,
70 'msg'=>$e->getMessage(),
71 'rows'=>[],
72 'total_records'=>0,
73 'page_number'=>0,
74 'page_size'=>(int)$pageSize,
75 'page_total'=>0,
76 ];
77 }
78 header('Content-type:application/json');
79 echo json_encode($data);
80 ?>
users.php