17370845950

新闻动态
将SQL数据转换为JSON并集成到Bootstrap Table的教程

本教程详细介绍了如何将SQL数据库中的数据通过PHP后端接口转换为Bootstrap Table所需的JSON格式，并实现服务器端分页、排序和搜索功能。通过创建独立的PHP文件作为数据API，配置HTTP响应头，执行PDO查询，并根据Bootstrap Table的特定数据结构（total、rows）格式化输出，最终实现动态、高效的数据展示。

理解Bootstrap Table的data-url机制

Bootstrap Table是一个功能丰富的表格插件，它支持从远程URL加载数据。当配置data-url属性时，Bootstrap Table会向该URL发起AJAX请求，并期望返回特定格式的JSON数据。对于服务器端分页（data-side-pagination="server"），它通常期望以下结构：

{
  "total": 100, // 总记录数
  "totalNotFiltered": 100, // 未过滤的总记录数 (在没有搜索条件时与total相同)
  "rows": [     // 当前页的数据行
    { "id": 1, "name": "Item A", "price": "$10" },
    { "id": 2, "name": "Item B", "price": "$20" }
  ]
}

用户遇到的问题是，直接在HTML的data-url属性中使用=json_encode($users)?>无法正确显示数据。这是因为data-url期望的是一个可以被浏览器独立访问并返回JSON字符串的HTTP端点，而不是直接嵌入在HTML页面中的PHP变量。服务器端需要一个独立的脚本来查询数据库，并将结果以JSON格式输出。

构建后端JSON数据接口 (PHP)

要解决这个问题，我们需要创建一个独立的PHP文件，它将作为Bootstrap Table的数据接口。

1. 创建独立的PHP文件

假设我们创建一个名为users_data.php的文件。

2. 设置HTTP响应头

在PHP脚本的开头，必须设置Content-Type头，告知客户端返回的内容是JSON格式。这必须在任何输出（包括空格或HTML）之前完成。

3. 连接数据库并执行SQL查询

使用PDO（PHP Data Objects）连接到SQL数据库并执行查询是推荐的做法，因为它提供了更好的安全性和灵活性。

 PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    // 生产环境中应记录错误而非直接输出
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]);
    exit();
}

// 示例查询，获取所有用户
$statement = $pdo->prepare("SELECT id, vorname AS name, rolle AS role FROM users WHERE cid = :cid");
// 假设 $user['cid'] 是从会话或其他安全方式获取的用户ID
// 这里为了示例方便，假设cid为1
$cid = 1; // 替换为实际的用户或租户ID
$statement->execute([':cid' => $cid]);
$users = $statement->fetchAll(); // 获取所有匹配的行
// ...
?>

注意： 这里的vorname AS name, rolle AS role是为了让数据库字段名与Bootstrap Table期望的name和role字段名保持一致，这有助于前端的列定义。

4. 格式化数据并输出JSON

Bootstrap Table期望的数据结构包含total和rows。我们需要将查询结果封装到这个结构中。

 count($users), // 当前查询结果的总数
    'totalNotFiltered' => count($users), // 在没有过滤的情况下，与total相同
    'rows' => $users, // 查询到的数据行
];

echo json_encode($data);
?>

示例代码：基础JSON接口 (users_data.php)

 PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]);
    exit();
}

// 假设 $user['cid'] 是从会话或其他安全方式获取的用户ID
$cid = 1; // 替换为实际的用户或租户ID

$statement = $pdo->prepare("SELECT id, vorname AS name, rolle AS role FROM users WHERE cid = :cid");
$statement->execute([':cid' => $cid]);
$users = $statement->fetchAll();

$data = [
    'total' => count($users),
    'totalNotFiltered' => count($users),
    'rows' => $users,
];

echo json_encode($data);
?>

集成服务器端分页、排序与搜索

对于大型数据集，仅返回所有数据是不高效的。Bootstrap Table支持服务器端分页、排序和搜索。当data-side-pagination="server"启用时，Bootstrap Table会在请求中发送以下GET参数：

  • limit: 每页显示的记录数。
  • offset: 记录的起始偏移量。
  • sort: 排序字段。
  • order: 排序顺序（asc或desc）。
  • search: 全局搜索关键字。

我们的PHP脚本需要接收并处理这些参数。

示例代码：带分页、排序和搜索的JSON接口 (users_data.php)

 PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]);
    exit();
}

// 获取Bootstrap Table发送的参数
$limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 10;
$offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
$sort = isset($_GET['sort']) ? $_GET['sort'] : 'id'; // 默认排序字段
$order = isset($_GET['order']) ? $_GET['order'] : 'asc'; // 默认排序顺序
$search = isset($_GET['search']) ? $_GET['search'] : ''; // 全局搜索关键字

// 验证和清理排序字段，防止SQL注入
$allowedSortFields = ['id', 'name', 'role']; // 允许排序的字段
if (!in_array($sort, $allowedSortFields)) {
    $sort = 'id'; // 默认值
}
$order = (strtolower($order) === 'desc') ? 'DESC' : 'ASC';

// 假设 $user['cid'] 是从会话或其他安全方式获取的用户ID
$cid = 1; // 替换为实际的用户或租户ID

// 构建SQL查询
$sql = "SELECT id, vorname AS name, rolle AS role FROM users WHERE cid = :cid";
$params = [':cid' => $cid];

// 处理搜索条件
if (!empty($search)) {
    $sql .= " AND (vorname LIKE :search_name OR rolle LIKE :search_role)";
    $params[':search_name'] = '%' . $search . '%';
    $params[':search_role'] = '%' . $search . '%';
}

// 获取总记录数 (不带分页)
$countSql = "SELECT COUNT(*) FROM users WHERE cid = :cid";
$countParams = [':cid' => $cid];
if (!empty($search)) {
    $countSql .= " AND (vorname LIKE :search_name OR rolle LIKE :search_role)";
    $countParams[':search_name'] = '%' . $search . '%';
    countParams[':search_role'] = '%' . $search . '%';
}

$stmtCount = $pdo->prepare($countSql);
$stmtCount->execute($countParams);
$totalRows = $stmtCount->fetchColumn();

// 添加排序和分页
$sql .= " ORDER BY $sort $order LIMIT :limit OFFSET :offset";
$params[':limit'] = $limit;
$params[':offset'] = $offset;

$statement = $pdo->prepare($sql);
$statement->execute($params);
$rows = $statement->fetchAll();

$data = [
    'total' => $totalRows, // 实际的总记录数
    'totalNotFiltered' => $totalRows, // 如果没有高级过滤，可以与total相同
    'rows' => $rows,
];

echo json_encode($data);
?>

前端Bootstrap Table配置

现在，我们将data-url指向新创建的PHP接口文件，并确保启用服务器端分页。




    
    
    Bootstrap Table with Server-Side Data
    
    
    




    
用户列表


    
      
    

    
      data-click-to-select="true"
      data-toolbar="#toolbar"
      data-show-toggle="true"
      data-show-columns="true"
      data-search="true" 
      data-url="users_data.php"> 

      
        

          
          ID
          姓名
          角色

注意：

  • data-url属性的值应是你的users_data.php文件的可访问URL路径。
  • data-side-pagination="server"是启用服务器端分页的关键。
  • data-search="true"会显示一个搜索框，其输入会作为search参数发送到后端。
  • data-field应与后端JSON数据中的键名（如id, name, role）匹配。
  • data-sortable="true"启用列排序，Bootstrap Table会自动发送sort和order参数。

注意事项与最佳实践

  1. 安全性：
    • SQL注入：始终使用PDO预处理语句来防止SQL注入。本教程中的示例已采用此方法。
    • 参数验证：对所有来自客户端的输入（如limit, offset, sort, order, search）进行严格的验证和清理，防止恶意输入。特别是排序字段，应只允许预定义的白名单字段。
    • 敏感数据：避免在JSON接口中暴露敏感的用户数据。
  2. 错误处理：
    • 在生产环境中，PHP脚本不应直接输出数据库错误信息。应捕获异常，记录错误，并向客户端返回一个通用的错误消息（如HTTP 500状态码和{"error": "Internal Server Error"}）。
  3. 性能优化：
    • 数据库索引：在经常用于搜索、排序和过滤的数据库列上创建索引，以提高查询性能。
    • 分页查询：确保SQL查询只返回当前页所需的数据，而不是整个数据集。本教程中的LIMIT和OFFSET子句正是为此目的。
    • 缓存：对于不经常变动的数据，可以考虑使用缓存机制来减少数据库负载。
  4. 数据类型匹配：
    • 确保数据库中的数据类型与前端Bootstrap Table中预期的类型一致。例如，如果某个字段是数字，确保它在JSON中也是数字类型，而不是字符串。
  5. 前端资源：
    • 确保所有必要的CSS和JavaScript库（jQuery, Bootstrap, Bootstrap Table及其扩展）都已正确引入，并且版本兼容。

总结

通过以上步骤，我们成功地构建了一个PHP后端接口，它能够从SQL数据库中获取数据，将其格式化为Bootstrap Table所需的JSON结构，并支持服务器端的分页、排序和搜索功能。前端Bootstrap Table通过data-url属性连接到这个接口，从而实现了动态、高效且用户友好的数据展示。这种分离前后端逻辑的方法是构建现代Web应用程序的常见模式，它提高了代码的可维护性和可扩展性。

广照天下广告 广照天下广告 广照天下广告策划 广照天下广告策划 广照天下 广照天下 广照天下 广照天下 广照天下 广照天下 广照天下广告策划 广照天下广告策划 广照天下广告策划 广照天下广告策划 南昌市广照天下广告策划有限公司 南昌市广照天下广告策划有限公司 南昌市广照天下广告策划有限公司 南昌市广照天下广告策划有限公司

赣ICP备2024031479号