无法从SQL Server获取数据到PHP页面？求jquery.bootgrid适配代码

DevOps 小满

2026-5-27

Fixing jquery.bootgrid "No results found" with PHP & SQL Server

Hey there, let's get your data showing up in jquery.bootgrid properly. The issue usually comes from mismatched request handling between bootgrid and your PHP backend, or incorrect SQL syntax for SQL Server. Here's a complete, tested implementation:

Backend PHP (data.php)

This file will handle bootgrid's AJAX requests, fetch data from SQL Server, and return the JSON format bootgrid expects. I'll build on the partial code you shared:

<?php
include 'dbh.inc.php'; // Ensure this file correctly connects to SQL Server using sqlsrv functions

// Initialize default values
$query = '';
$data = [];
$records_per_page = 10;
$current_page = 1;
$start_from = 0;

// Handle bootgrid's pagination parameters
if (isset($_POST["rowCount"])) {
    $records_per_page = intval($_POST["rowCount"]);
}
if (isset($_POST["current"])) {
    $current_page = intval($_POST["current"]);
}
$start_from = ($current_page - 1) * $records_per_page;

// Handle sorting (bootgrid sends sort as an object)
$sort_clause = '';
if (isset($_POST["sort"]) && is_array($_POST["sort"])) {
    $sort_parts = [];
    foreach ($_POST["sort"] as $column => $direction) {
        // Sanitize column name to prevent SQL injection
        $safe_column = preg_replace("/[^a-zA-Z0-9_]/", "", $column);
        $sort_parts[] = "$safe_column $direction";
    }
    if (!empty($sort_parts)) {
        $sort_clause = " ORDER BY " . implode(", ", $sort_parts);
    }
}

// Handle search phrase
$search_clause = '';
if (isset($_POST["searchPhrase"]) && !empty($_POST["searchPhrase"])) {
    $search_phrase = $_POST["searchPhrase"];
    // Adjust these columns to match your table's fields
    $search_clause = " WHERE (id LIKE '%$search_phrase%' OR name LIKE '%$search_phrase%' OR email LIKE '%$search_phrase%')";
}

// Query total records for pagination
$count_sql = "SELECT COUNT(*) AS total FROM your_table_name $search_clause";
$count_stmt = sqlsrv_query($conn, $count_sql);
$count_row = sqlsrv_fetch_array($count_stmt, SQLSRV_FETCH_ASSOC);
$total_records = intval($count_row['total']);

// Query paginated data (SQL Server 2012+ syntax)
$data_sql = "SELECT * FROM your_table_name $search_clause $sort_clause OFFSET $start_from ROWS FETCH NEXT $records_per_page ROWS ONLY";
$data_stmt = sqlsrv_query($conn, $data_sql);

// Fetch data rows
while ($row = sqlsrv_fetch_array($data_stmt, SQLSRV_FETCH_ASSOC)) {
    // Optional: Format datetime fields if needed
    // $row['created_at'] = $row['created_at']->format('Y-m-d H:i:s');
    $data[] = $row;
}

// Return bootgrid-compatible JSON
echo json_encode([
    "current" => $current_page,
    "rowCount" => $records_per_page,
    "total" => $total_records,
    "rows" => $data
]);

// Clean up resources
sqlsrv_free_stmt($count_stmt);
sqlsrv_free_stmt($data_stmt);
sqlsrv_close($conn);
?>

Backend Notes:

Replace your_table_name with your actual SQL Server table name
Update the search_clause columns to match the fields you want to search

If you're on SQL Server <2012, replace the data_sql with this ROW_NUMBER()-based pagination:

$data_sql = "WITH PaginatedData AS (
    SELECT *, ROW_NUMBER() OVER($sort_clause) AS RowNum
    FROM your_table_name $search_clause
)
SELECT * FROM PaginatedData WHERE RowNum BETWEEN ".($start_from + 1)." AND ".($start_from + $records_per_page);

Frontend HTML/JS

This page will render the bootgrid table and connect to your backend:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>SQL Server Data with Bootgrid</title>
    <!-- Include dependencies -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.min.css">
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.min.js"></script>
</head>
<body>
    <div class="container">
        <h1>Data Table</h1>
        <table id="data-grid" class="table table-condensed table-hover table-striped">
            <thead>
                <tr>
                    <!-- Match data-column-id to your database field names -->
                    <th data-column-id="id" data-type="numeric">ID</th>
                    <th data-column-id="name">Full Name</th>
                    <th data-column-id="email">Email Address</th>
                    <!-- Add more columns as needed -->
                </tr>
            </thead>
        </table>
    </div>

    <script>
        $(document).ready(function() {
            $("#data-grid").bootgrid({
                ajax: true,
                url: "data.php", // Point to your backend PHP file
                post: function() {
                    // Optional: Add extra parameters to send to the backend
                    return {
                        // example: "user_id": 123
                    };
                },
                rowCount: [10, 25, 50, -1], // Allow user to select page size
                searchSettings: {
                    delay: 100, // Wait 100ms after typing to trigger search
                    characters: 2 // Require at least 2 characters for search
                }
            });
        });
    </script>
</body>
</html>

Troubleshooting "No results found"

If you still see this message, check these:

Database Connection: Add var_dump($conn); to the top of data.php to confirm your dbh.inc.php connects successfully.
SQL Query: Copy the generated $data_sql from data.php and run it directly in SQL Server Management Studio—does it return rows?
JSON Response: Visit data.php directly in your browser. You should see a JSON object with total (greater than 0) and rows (an array of data).
Column Matching: Ensure the data-column-id values in your frontend table exactly match the field names from your SQL Server table (case-sensitive if your collation is case-sensitive).
Resource Loading: Check your browser's console for 404 errors on bootgrid CSS/JS files.

内容的提问来源于stack exchange，提问作者Yous k

无法从SQL Server获取数据到PHP页面？求jquery.bootgrid适配代码

Backend PHP (data.php) #

Backend Notes: #

Frontend HTML/JS #

Troubleshooting "No results found" #

Backend PHP (data.php)

Backend Notes:

Frontend HTML/JS

Troubleshooting "No results found"