怎樣用Layui簡化MySQL增刪改查

小樊
82
2024-10-02 17:09:34
欄目: 云計(jì)算

Layui是一個(gè)前端UI框架，它可以幫助我們快速構(gòu)建網(wǎng)頁界面，但它本身并不提供數(shù)據(jù)庫操作功能。要使用Layui簡化MySQL的增刪改查，你需要結(jié)合后端編程語言（如PHP、Python、Node.js等）來實(shí)現(xiàn)。以下是一個(gè)簡單的示例，使用PHP和Layui實(shí)現(xiàn)MySQL的增刪改查操作。

  1. 創(chuàng)建MySQL數(shù)據(jù)庫表

首先，在MySQL中創(chuàng)建一個(gè)名為user的表，用于存儲(chǔ)用戶信息。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. 創(chuàng)建PHP文件

創(chuàng)建一個(gè)名為index.php的文件，用于處理前端發(fā)送的請(qǐng)求并與MySQL數(shù)據(jù)庫進(jìn)行交互。

<?php
// 連接MySQL數(shù)據(jù)庫
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("連接失敗: " . $conn->connect_error);
}

// 獲取前端發(fā)送的請(qǐng)求參數(shù)
$action = isset($_GET['action']) ? $_GET['action'] : '';
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$username = isset($_POST['username']) ? $_POST['username'] : '';
$password = isset($_POST['password']) ? $_POST['password'] : '';
$email = isset($_POST['email']) ? $_POST['email'] : '';

// 根據(jù)請(qǐng)求參數(shù)執(zhí)行相應(yīng)的MySQL操作
switch ($action) {
  case 'add':
    $sql = "INSERT INTO user (username, password, email) VALUES ('$username', '$password', '$email')";
    break;
  case 'delete':
    $sql = "DELETE FROM user WHERE id=$id";
    break;
  case 'update':
    $sql = "UPDATE user SET username='$username', password='$password', email='$email' WHERE id=$id";
    break;
  case 'select':
    $sql = "SELECT * FROM user";
    break;
  default:
    echo "無效的請(qǐng)求參數(shù)";
    exit;
}

if ($conn->query($sql) === TRUE) {
  echo "操作成功";
} else {
  echo "錯(cuò)誤: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>
  1. 創(chuàng)建Layui前端頁面

創(chuàng)建一個(gè)名為index.html的文件，用于顯示表格和表單，并發(fā)送請(qǐng)求到后端PHP文件。

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Layui簡化MySQL增刪改查示例</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/layui/css/layui.css" media="all">
</head>
<body>

<div class="layui-container">
  <div class="layui-row">
    <div class="layui-col-xs12">
      <form class="layui-form" action="">
        <div class="layui-form-item">
          <label class="layui-form-label">用戶名</label>
          <div class="layui-input-block">
            <input type="text" name="username" required lay-verify="required" placeholder="請(qǐng)輸入用戶名" autocomplete="off" class="layui-input">
          </div>
        </div>
        <div class="layui-form-item">
          <label class="layui-form-label">密碼</label>
          <div class="layui-input-block">
            <input type="password" name="password" required lay-verify="required" placeholder="請(qǐng)輸入密碼" autocomplete="off" class="layui-input">
          </div>
        </div>
        <div class="layui-form-item">
          <label class="layui-form-label">郵箱</label>
          <div class="layui-input-block">
            <input type="email" name="email" required lay-verify="required" placeholder="請(qǐng)輸入郵箱" autocomplete="off" class="layui-input">
          </div>
        </div>
        <div class="layui-form-item">
          <div class="layui-input-block">
            <button type="button" class="layui-btn" id="add">添加</button>
            <button type="button" class="layui-btn" id="delete">刪除</button>
            <button type="button" class="layui-btn" id="update">更新</button>
            <button type="button" class="layui-btn" id="select">查詢</button>
          </div>
        </div>
      </form>
    </div>
  </div>
  <table class="layui-hide" id="userTable" lay-filter="userTableFilter"></table>
</div>

<script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/layui/layui.all.js"></script>
<script>
layui.use(['form', 'table'], function(){
  var form = layui.form;
  var table = layui.table;

  // 渲染表格
  table.render({
    elem: '#userTable',
    url: 'index.php', // 數(shù)據(jù)接口
    page: true, // 開啟分頁
    cols: [[ // 表頭
      {field: 'id', title: 'ID', sort: true},
      {field: 'username', title: '用戶名', sort: true},
      {field: 'password', title: '密碼', sort: true},
      {field: 'email', title: '郵箱', sort: true},
      {fixed: 'right', title:'操作', toolbar: '#barDemo', width:150}
    ]]
  });

  // 監(jiān)聽工具條
  table.on('tool(userTableFilter)', function(obj){
    var data = obj.data;
    if(obj.event === 'delete'){
      layer.confirm('真的刪除行么', function(index){
        obj.del();
        layer.close(index);
        // 發(fā)送刪除請(qǐng)求到后端
        $.post('index.php', {action: 'delete', id: data.id}, function(response) {
          if (response === '操作成功') {
            layer.msg('刪除成功');
          } else {
            layer.msg('刪除失敗');
          }
        });
      });
    } else if(obj.event === 'update'){
      // 發(fā)送更新請(qǐng)求到后端
      $.post('index.php', {action: 'update', id: data.id, username: data.username, password: data.password, email: data.email}, function(response) {
        if (response === '操作成功') {
          layer.msg('更新成功');
        } else {
          layer.msg('更新失敗');
        }
      });
    } else if(obj.event === 'select'){
      // 發(fā)送查詢請(qǐng)求到后端
      $.post('index.php', {action: 'select'}, function(response) {
        // 將查詢結(jié)果填充到表格中
        var html = '';
        response.forEach(function(item) {
          html += '<tr>';
          html += '<td>' + item.id + '</td>';
          html += '<td>' + item.username + '</td>';
          html += '<td>' + item.password + '</td>';
          html += '<td>' + item.email + '</td>';
          html += '<td><button class="layui-btn layui-btn-xs layui-btn-danger" lay-event="delete">刪除</button></td>';
          html += '<td><button class="layui-btn layui-btn-xs layui-btn-warning" lay-event="update">更新</button></td>';
          html += '</tr>';
        });
        $('#userTable').html(html);
      });
    }
  });

  // 監(jiān)聽添加按鈕
  $('#add').on('click', function(){
    layer.prompt({
      title: '請(qǐng)輸入用戶信息',
      formType: 1,
      value: ''
    }, function(value, index){
      $.post('index.php', {action: 'add', username: value.username, password: value.password, email: value.email}, function(response) {
        if (response === '操作成功') {
          layer.msg('添加成功');
          layer.close(index);
          table.reload('userTable'); // 刷新表格
        } else {
          layer.msg('添加失敗');
        }
      });
    });
  });
});
</script>

</body>
</html>

現(xiàn)在，你可以在瀏覽器中打開index.html文件，嘗試使用Layui簡化MySQL的增刪改查操作。請(qǐng)注意，這個(gè)示例僅用于演示目的，實(shí)際項(xiàng)目中你可能需要考慮更多的安全性和錯(cuò)誤處理。

0 踩