基于MySQL实现textarea定时自动刷新的技术求助

Hey there! Let's work through getting your chat's auto-refresh working smoothly, plus tighten up some security and reliability in your code. First, let's break down the issues in your current implementation, then fix them step by step.

Key Issues in Your Current Code #

• Undefined parameters variable: Your AJAX call references parameters but never defines it. Since you're pulling advertid from the session, you don't even need to send extra data here—but we'll make that clear.
• Duplicate timers: Putting setInterval(sendRequest, 5000) inside the complete callback means every request spawns a new timer. After a few minutes, you'll have dozens of concurrent requests running at once, which will slow down your app.
• SQL Injection Risk: Directly inserting $_SESSION['advertid'] into your SQL query leaves you open to injection attacks. We'll fix this with prepared statements, which are non-negotiable for database safety.

Corrected Frontend AJAX Code #

Replace your existing script with this version. We'll use setTimeout instead of setInterval in the complete callback to ensure only one request runs at a time:

<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
$(document).ready(function(){
    // Send the first request when the page loads
    sendRequest();

    function sendRequest(){
        $.ajax({
            type: 'POST',
            url: '/chatrefresh.php',
            // If you ever need to send data to the backend later, define it here (example below)
            // data: { lastMessageId: lastId },
            success: function(response){
                $('#chatwindow').html(response);
                // Optional: Auto-scroll to the bottom of the chat when new messages load
                $('#screen').scrollTop($('#screen')[0].scrollHeight);
            },
            error: function(xhr, status, error){
                // Better error handling than a raw alert
                console.error('Chat refresh failed:', status, error);
            },
            complete: function() {
                // Schedule the next request 5 seconds after the current one finishes
                setTimeout(sendRequest, 5000);
            }
        });
    };
});
</script>

<table id="chatwindow" name="chatwindow"><tr><td></td></tr></table>

Corrected Backend (chatrefresh.php) #

We'll use MySQLi prepared statements to secure your query, plus clean up the output to avoid breaking the textarea:

<?php
include_once('session.php');
include('config.php');

// Validate the session first
if (!isset($_SESSION['advertid'])) {
    die('Invalid session: No advert ID found');
}

$advertid = $_SESSION['advertid'];

// Use a prepared statement to eliminate SQL injection risk
$sql = "SELECT * FROM `$tbl_5` WHERE advertid = ? ORDER by id ASC";
$stmt = mysqli_prepare($dbconn, $sql);
// "s" means we're binding a string value (adjust to "i" if advertid is an integer)
mysqli_stmt_bind_param($stmt, "s", $advertid);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
?>

<textarea id="screen" name="screen" style="width:100%;height:300px;resize:none">
    <?php
    while ($row = mysqli_fetch_row($result)) {
        // Escape special characters to prevent breaking the textarea
        echo htmlspecialchars($row[5]) . "\n";
    }
    // Clean up the statement
    mysqli_stmt_close($stmt);
    ?>
</textarea>

Bonus Improvements to Consider #

• Only load new messages: Instead of reloading every message each time, track the last message ID you've loaded in JavaScript, send it to chatrefresh.php, and modify the SQL query to only fetch messages newer than that ID. This reduces database load and bandwidth.
• Use named columns: Swap mysqli_fetch_row for mysqli_fetch_assoc so you can reference columns by name (e.g., $row['message']) instead of index ($row[5]). This makes your code way more readable if your table schema changes later.

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