Community Learn Tools Library Leisure
search
English
Home > List of blog posts > php+jquery实现无刷新分页和前端端交互操作数据库
Blogger Information
Blog 45
fans 0
comment 0
visits 34500
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
php+jquery实现无刷新分页和前端端交互操作数据库
咸鱼老爷
Original
678 people have browsed it

后端php查询所有数据，前端通过ajax请求获取并且动态生成带有分页的表格
前端html代码

  1. <!DOCTYPE html>
  2. <html lang="zh-CN">
  3. <head>
  4.     <meta charset="UTF-8">
  5.     <meta http-equiv="X-UA-Compatible" content="IE=edge">
  6.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  7.     <script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  8.     <title>Document</title>
  9.     <style>
  10.         .active{
  11.             color: green;
  12.         }
  13.         .modal{
  14.             width: 550px;
  15.            position: absolute;
  16.            top: 0;
  17.             background-color: skyblue;
  18.             display: none;
  19.         }
  20.         a{
  21.             text-decoration: none;
  22.             color: #000;
  23.         }
  24.     </style>
  25. </head>
  27. <body>
  28.     <table>
  29.         <caption>员工列表</caption>
  30.         <thead>
  31.             <tr>
  32.                 <th>id</th>
  33.                 <th>姓名</th>
  34.                 <th>性别</th>
  35.                 <th>年龄</th>
  36.                 <th>邮箱</th>
  37.                 <th>生日</th>
  38.                 <th>入职日期</th>
  39.                 <th>操作</th>
  40.             </tr>
  41.             </thead>
  42.         <tbody>
  44.         </tbody>
  45.     </table>
  46.     <p class="page">
  48.     </p>
  49.       <div class="modal">
  50.         <button class="close">关闭</button>
  51.         <form action="" method="POST" id="edit">
  52.             <p><label for="">姓名:<input type="text" value="" name="name" id="name" /></label></p> 
  53.             <p><label for="">性别:<select name="gender" id="gender">
  54.                     <option value="male">男</option>
  55.                     <option value="female">女</option>
  56.                   </select>
  57.                 </label></p>
  58.                <p> <label for="">年龄:<input type="text" value="" name="age" id="age" /></label></p>
  59.                 <p><label for="">邮箱:<input type="email" value="" name="email" id="email" /></label></p>
  60.                 <p><label for="">生日:<input type="text" value="" name="borthday" id="borthday" /></label></p>
  61.                 <input type="hidden" value="" name="id" id="id" />
  62.                <p> <label for=""><button class="save">保存</button></label></p>
  63.         </form>
  64.     </div>
  65. </body>
  66. <script>
  67.     select(1);
  68.     function show(data) {
  69.         var html = '';
  70.         $.each(data, function(index, value) {
  71.             html += '<tr>';
  72.             html += '<td>' + data[index]['id'] + '</td>';
  73.             html += '<td>' + data[index]['name'] + '</td>';
  74.             html += '<td>' + data[index]['gender'] + '</td>';
  75.             html += '<td>' + data[index]['age'] + '</td>';
  76.             html += '<td>' + data[index]['email'] + '</td>';
  77.             html += '<td>' + data[index]['borthday'] + '</td>';
  78.             html += '<td>' + data[index]['create_time'] + '</td>';
  79.             html += '<td><button class="edit" data-index='+data[index]['id']+'>编辑</button><button class="del"data-index='+data[index]['id']+'>删除</button></td>';
  80.             html += '</tr>';
  81.         })
  82.         return html;
  83.     }
  84.     // 查询
  85.     function select(page = 1) {
  86.         $.get("http://127.0.0.119/pdo/limit.php?action=select&p=" + page, function(res) {
  87.             var obj = jQuery.parseJSON(res);
  88.             let pages = obj.pages;
  89.             let rows = obj.rows;
  90.             console.log(rows);
  91.             $('tbody').html(show(rows));
  92.             $('.page').html(getPage(page, pages));
  93.         })
  94.     }
  96.     function getPage(page = 1, pages) {
  97.         let p = "";
  98.         let active = "";
  99.         // 首页|上一页
  100.         if (page <= 1) page = 1;
  101.         if (page !== 1) {
  102.             p += '<a href="http://127.0.0.119/pdo/limit.php?action=select&p=1">首页</a>';
  103.             p += '<a href="http://127.0.0.119/pdo/limit.php?action=select&p= '+ Math.max(1, page - 1) + '">上一页</a>';
  104.         }
  105.         // 高亮
  106.         for (i = 1; i <= pages; i++) {
  107.             active = "";
  108.             if (page == i) active = 'class="active"';
  109.             p += '<a href="http://127.0.0.119/pdo/limit.php?action=select&p=' + i+ '"'+  active + '>' + i + '</a>';
  110.         }
  111.         // 下一页|尾页
  112.         if (page >= pages) page = pages;
  113.         if (page != pages) {
  114.             p += '<a href="http://127.0.0.119/pdo/limit.php?action=select&p=' + ( parseInt(page) +1 ) + '">下一页</a>';
  115.             p += '<a href="http://127.0.0.119/pdo/limit.php?action=select&p=' + pages + '">尾页</a>';
  116.         }
  117.         return p;
  118.     }
  119.     $('.page').on('click',
  120.         function(event){
  121.             event.preventDefault();
  122.             event.stopPropagation();
  123.             let page=$(event.target).attr('href');
  124.             page=page.split('=')[2];
  125.             select(page);
  126.         }
  127.     );
  128.     //编辑
  129.     $(' tbody').on('click','.edit',function(){
  130.         let id=$(this).attr('data-index');
  131.         $.get("http://127.0.0.119/pdo/limit.php?action=getOne&id=" + id, function(res) {
  132.             var obj = jQuery.parseJSON(res);
  133.             let rows = obj.rows;
  134.             let name=rows.name;
  135.             let gender=rows.gender;
  136.             let age=rows.age;
  137.             let email=rows.email;
  138.             let borthday=rows.borthday;
  139.             let id=rows.id;
  140.          $('#name').val(name);
  141.          if (gender=='male'){
  142.             $('#gender option').removeAttr('selected');
  143.              $('#gender option:nth-of-type(0)').attr('selected',true);
  144.          }else{
  145.             $('#gender option').removeAttr('selected');
  146.              $('#gender option:nth-of-type(2)').attr('selected',true);
  147.          }
  148.          $('#age').val(age);
  149.          $('#email').val(email);
  150.          $('#borthday').val(borthday);
  151.          $('#id').val(id);
  152.          $('.modal').show();
  153.         });
  154.     });
  155.     $('.close').click(()=>{
  156.         $('.modal').hide();
  157.     })
  158.     //保存
  159.     $('.save').on('click',function(event){
  160.         event.preventDefault();
  161.         $.post("http://127.0.0.119/pdo/limit.php?action=save", $("#edit").serialize(),function(data){
  162.              alert(data);
  163.              $('.modal').hide();
  164.              location.reload();
  165.          });
  166.     })
  167.     //删除
  168.     $(' tbody').on('click','.del',function(){
  169.         let id=$(this).attr('data-index');
  170.         $.get("http://127.0.0.119/pdo/limit.php?action=delete&id=" + id, function(res) {
  171.             location.reload();
  172.            alert(res);
  173.         });
  174.     });
  175. </script>
  176. </html>

后端php代码

  1. $config = [
  2.     'type' => 'mysql',
  3.     'host' => '127.0.0.1',
  4.     'dbname' => 'php',
  5.     'port' => 3306,
  6.     'charset' => 'utf8mb4',
  7.     'username' => 'root',
  8.     'passwrod' => '123456',
  9. ];
  10. extract($config);
  11. $dsn = sprintf('%s:dbname=%s;', $type, $dbname);
  13. try {
  14.     $pdo = new PDO($dsn, $username, $passwrod);
  15.     //设置结果集的返回类型
  16.     $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  17. } catch (PDOException $e) {
  18.     die('连接失败' . $e->getMessage());
  19. }
  20. //计算共计多少页？
  21. $action = $_GET['action'] ?? 'select';
  22. $page = $_GET['p'] ?? 1;
  23. $id = $_GET['id'] ?? 1;
  24. $data = $_POST;
  25. switch ($action) {
  26.     case 'select':
  27.         select($pdo, $page);
  28.         break;
  29.     case 'getOne':
  30.         getOne($pdo, $id);
  31.         break;
  32.     case 'save':
  33.         save($pdo, $data);
  34.         break;
  35.     case 'delete':
  36.         delete($pdo, $id);
  37.         break;
  38. }
  39. //查询所有数据
  40. function select($pdo, $page = 1, $num = 5)
  41. {
  42.     //计算当前页的起始偏移量
  43.     $offset = ($page - 1) * $num;
  44.     //ceil()向上取整
  45.     $sql = "SELECT CEIL(COUNT(1)/{$num}) total FROM user";
  46.     $row = $pdo->query($sql);
  47.     $pages = $row->fetch()['total'];
  48.     // print_r($row);
  49.     //每页显示的数据
  50.     $sql = "SELECT * FROM `user` LIMIT {$offset},{$num}";
  51.     $stmt = $pdo->prepare($sql);
  52.     $stmt->execute();
  53.     $rows = $stmt->fetchAll();
  54.     echo json_encode(['pages' => $pages, 'rows' => $rows]);
  55. }
  56. //获取单条信息
  57. function getOne($pdo, $id)
  58. {
  59.     $sql = "SELECT *  FROM user WHERE id={$id}";
  60.     $stmt = $pdo->prepare($sql);
  61.     $stmt->execute();
  62.     $rows = $stmt->fetch();
  63.     echo json_encode(['rows' => $rows]);
  64. }
  65. //更新数据
  66. function save($pdo, $data)
  67. {
  68.     $sql = "UPDATE `user` SET name = :name, gender = :gender, age = :age, email = :email, borthday = :borthday WHERE id=:id";
  69.     $stmt = $pdo->prepare($sql);
  70.     $stmt->bindParam(':name', $data['name'], PDO::PARAM_STR);
  71.     $stmt->bindParam(':gender', $data['gender'], PDO::PARAM_STR);
  72.     $stmt->bindParam(':age', $data['age'], PDO::PARAM_STR);
  73.     $stmt->bindParam(':email', $data['email'], PDO::PARAM_STR);
  74.     $stmt->bindParam(':borthday', $data['borthday'], PDO::PARAM_STR);
  75.     $stmt->bindParam(':id', $data['id'], PDO::PARAM_INT);
  76.     $stmt->execute();
  77.     if ($stmt->rowCount() > 0) {
  78.         echo  '更新成功';
  79.     } else {
  80.         echo '更新失败';
  81.     }
  82. }
  83. //删除数据
  84. function delete($pdo, $id)
  85. {
  86.     $sql = "DELETE  FROM `user`  WHERE id=:id";
  87.     $stmt = $pdo->prepare($sql);
  88.     $stmt->bindParam(':id', $id, PDO::PARAM_INT);
  89.     $stmt->execute();
  90.     if ($stmt->rowCount() > 0) {
  91.         echo  '删除成功';
  92.     } else {
  93.         echo '删除失败';
  94.     }
  95. }

效果图

Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post