如何结合Ajax、PHP和MySQL正确显示大数据查询进度条

The issue with your current code is that you're using the xhr.upload.progress event, which only tracks the upload of data from your browser to the server. Since your form data is minimal (just a submit button), this upload finishes almost instantly—hence the progress bar jumps to 100% immediately, even though the server is still busy querying the database.

To track the server's actual processing progress, we need a way for the server to report back how much of the query work it's completed. The most straightforward approach is to use periodic client polling (sending small Ajax requests every second to check progress) paired with server-side session-based progress tracking. Here's how to implement it:

Step 1: Update Client-Side Code #

Modify your JavaScript to start a progress-checking timer when the form is submitted, and update the progress bar based on the server's responses.

<html>
<head>
<script src="jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function (e) {
    let progressInterval; // Variable to hold our timer reference

    // Function to update progress bar
    function updateProgress(percent) {
        $(".progressbar").width(percent + '%');
        $(".progressbar").html('<span>' + percent + '% Completed</span>');
    }

    // Function to check progress from server
    function checkProgress() {
        $.ajax({
            url: "check_progress.php",
            type: "GET",
            success: function(data) {
                const progress = parseInt(data);
                if (progress >= 100) {
                    clearInterval(progressInterval); // Stop checking once done
                    updateProgress(100);
                } else {
                    updateProgress(progress);
                }
            }
        });
    }

    $("#sForm").on('submit',(function(e) {
        e.preventDefault();
        $('.progressbar').css('width', '0');
        updateProgress(0);

        // Start checking progress every 1 second
        progressInterval = setInterval(checkProgress, 1000);

        // Main Ajax request to run the query
        $.ajax({
            url: "qdata.php",
            type: "POST",
            data: new FormData(this),
            contentType: false,
            cache: false,
            processData: false,
            success: function(data) {
                clearInterval(progressInterval);
                updateProgress(100);
                if(data.trim() == "good"){
                    alert('Completed now!');
                }
            },
            error: function() {
                clearInterval(progressInterval);
                updateProgress(0);
                alert('An error occurred.');
            }
        });
    }));
});
</script>
<style>
/* Add basic styling to make the progress bar visible */
.progressbar {
    height: 20px;
    background-color: #e0e0e0;
    border-radius: 10px;
    overflow: hidden;
    margin-bottom: 10px;
}
.progressbar span {
    display: block;
    height: 100%;
    background-color: #4CAF50;
    color: white;
    text-align: center;
    line-height: 20px;
}
</style>
</head>
<body>
<div class="progressbar"></div>
<form id="sForm" action="qdata.php" method="post">
<div id="resultdata"></div>
<input type="submit" value="Submit now" />
</form>
</body>
</html>

Step 2: Modify qdata.php to Track Progress #

We'll use PHP sessions to store the progress. Important: We need to release the session lock early so that our progress-check requests can access the session data while the main query is running.

<?php
session_start();
// Initialize progress to 0
$_SESSION['query_progress'] = 0;
// Release the session lock so other requests can read it
session_write_close();

// Include your dbconfig.php
include 'dbconfig.php';

// First, get total rows to calculate progress
$result = $db->prepare('SELECT COUNT(*) as total FROM users');
$result->execute();
$totalRows = $result->fetch()['total'];

// Run the actual query
$result = $db->prepare('SELECT fullname FROM users');
$result->execute();
$count = 0;

while ($row = $result->fetch()) {
    $name = htmlentities($row['fullname'], ENT_QUOTES, "UTF-8");
    $count++;
    
    // Update progress every 10 rows (adjust based on your dataset size)
    if ($count % 10 == 0) {
        // Re-open session to update progress
        session_start();
        $_SESSION['query_progress'] = round(($count / $totalRows) * 100);
        session_write_close();
    }
}

// Final update to 100%
session_start();
$_SESSION['query_progress'] = 100;
session_write_close();

echo 'good';
?>

Step 3: Create check_progress.php #

This simple script returns the current progress value from the session.

<?php
session_start();
// Default to 0 if progress isn't set
$progress = isset($_SESSION['query_progress']) ? $_SESSION['query_progress'] : 0;
echo $progress;
// Close session immediately to avoid locking
session_write_close();
?>

Key Notes: #

• Session Locking: Using session_write_close() is critical. PHP locks the session file during a request, so if we don't release it, our progress-check requests will hang until the main query finishes.
• Progress Update Frequency: Adjust the $count % 10 value based on your dataset size. For very large tables, updating every 100 or 1000 rows will reduce overhead.
• Cleanup: Add code to reset the query_progress session variable after the request completes to avoid showing old progress on subsequent form submissions.

Alternative: For a more efficient solution than polling, you could use Server-Sent Events (SSE) where the server pushes progress updates to the client without the client having to ask. But polling is simpler to integrate with your existing codebase.

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