使用 jQuery 和 DataTables 传递表单数据到数据库并动态更新表格

<form method="POST" id="frm">
    <select name="selectplace">
        <option value="PLACE 1">PLACE 1</option>
        <option value="PLACE 2">PLACE 2</option>
        <option value="PLACE 3">PLACE 3</option>
    </select>
    <button type="submit" name="submitPlace">SUBMIT</button>
</form>

<div class="table-responsive">
    <table class="table table-bordered table-striped text-center" id="place-table">
        <thead>
            <tr>
                <th>PLACE #</th>
                <th>PLACE NAME</th>
                <th>TOTAL VISITORS</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>PLACE #</th>
                <th>PLACE NAME</th>
                <th>TOTAL VISITORS</th>
            </tr>
        </tfoot>
    </table>
</div>

$(document).ready(function() {
    var table = $('#place-table').DataTable({
        "ajax": {
            url: "json.php",
            "dataSrc": "",
            "data": function(d) {
                var frm_data = $('#frm').serialize(); // 使用 serialize() 方法
                return frm_data; // 直接返回序列化后的字符串
            }
        },
        columns: [{
            data: 'place_id',
        }, {
            data: 'place_name',
        }, {
            data: 'total_visitor',
        }]
    });

    // 监听表单提交事件
    $("#frm").submit(function(e) {
        e.preventDefault(); // 阻止默认的表单提交行为
        table.ajax.reload(); // 重新加载 DataTables 数据
    });
});

<?php
  $servername = "localhost";
  $username = "your_username";
  $password = "your_password";
  $dbname = "your_dbname";

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

  if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
  }

  $selectedplace = $_POST['selectplace']; // 获取 selectplace 的值

  // 安全起见，使用预处理语句防止 SQL 注入
  $sql = "SELECT * FROM placestable WHERE place_name = ?";
  $stmt = $conn->prepare($sql);
  $stmt->bind_param("s", $selectedplace); // "s" 表示字符串类型

  $stmt->execute();
  $result = $stmt->get_result();

  $data = array();
  while($row = $result->fetch_assoc()) {
      $data[] = array(
          "place_id"=>$row['id'],
          "place_name"=> $row['place_name'],
          "total_visitor"=> $row['total_visitor'],
      );
  }

  echo json_encode($data);

  $stmt->close();
  $conn->close();
?>