search

博客列表 >php+jquery实现无刷新分页和前端端交互操作数据库

php+jquery实现无刷新分页和前端端交互操作数据库

咸鱼老爷
咸鱼老爷原创
2021年03月27日 14:40:33686浏览

后端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. }

效果图

上一条：I盘格式化了的文件寻回方案.下一条：210324 CSS flex 布局
声明：本文内容转载自脚本之家，由网友自发贡献，版权归原作者所有，如您发现涉嫌抄袭侵权，请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言，请遵守新闻评论服务协议

相关文章

查看更多