如何用PHP等语言实现网页输入驱动MySQL取数并无刷新展示？求示例代码

Hey there! Let's break down your two requirements and build a working example together. We'll use PHP (with PDO for secure database interactions), MySQL, and plain JavaScript for the no-refresh part—no fancy frameworks needed.

1. First: Set Up a Test MySQL Table #

Let's start by creating a sample table and adding some test data you can use to verify the code. Run this SQL in your MySQL client (like phpMyAdmin or MySQL Workbench):

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT
);

-- Insert test records
INSERT INTO users (name, email, age) VALUES
('John Doe', 'john@example.com', 30),
('Jane Smith', 'jane@example.com', 28),
('Bob Brown', 'bob@example.com', 35),
('Alice Lee', 'alice@example.com', 25);

2. Full Working Code #

We'll split this into two core files: the main page (shows the initial table and search input) and an AJAX backend endpoint (handles database queries and returns updated table HTML).

Main Page (index.php) #

This page loads the full table on first load and includes the search functionality to trigger no-refresh updates:

<?php
// Update these with your own database credentials!
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_username';
$password = 'your_db_password';

// Connect to database using PDO (secure, prevents SQL injection risks)
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Oops, database connection failed: " . $e->getMessage());
}

// Fetch initial full dataset to display
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>MySQL Data Viewer</title>
    <style>
        table { border-collapse: collapse; width: 80%; margin: 2rem auto; }
        th, td { border: 1px solid #ddd; padding: 0.8rem; text-align: left; }
        th { background-color: #f5f5f5; }
        .search-bar { text-align: center; margin: 2rem; }
        input { padding: 0.6rem; width: 300px; }
        button { padding: 0.6rem 1.2rem; cursor: pointer; margin-left: 0.5rem; }
    </style>
</head>
<body>
    <div class="search-bar">
        <input type="text" id="searchInput" placeholder="Search by name or email...">
        <button onclick="refreshTable()">Search</button>
    </div>

    <div id="tableContainer">
        <table>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Age</th>
            </tr>
            <?php foreach($users as $user): ?>
            <tr>
                <td><?php echo htmlspecialchars($user['id']); ?></td>
                <td><?php echo htmlspecialchars($user['name']); ?></td>
                <td><?php echo htmlspecialchars($user['email']); ?></td>
                <td><?php echo htmlspecialchars($user['age']); ?></td>
            </tr>
            <?php endforeach; ?>
        </table>
    </div>

    <script>
        function refreshTable() {
            const searchTerm = document.getElementById('searchInput').value.trim();
            const xhr = new XMLHttpRequest();
            
            // Send request to our backend endpoint
            xhr.open('POST', 'fetch_data.php', true);
            xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
            
            // Update the table when we get a response
            xhr.onload = function() {
                if (this.status === 200) {
                    document.getElementById('tableContainer').innerHTML = this.responseText;
                }
            };
            
            // Send the search term (encoded to avoid issues with special characters)
            xhr.send('search=' + encodeURIComponent(searchTerm));
        }

        // Optional: Let users press Enter to search instead of clicking the button
        document.getElementById('searchInput').addEventListener('keypress', function(e) {
            if (e.key === 'Enter') {
                refreshTable();
            }
        });
    </script>
</body>
</html>

AJAX Backend Endpoint (fetch_data.php) #

This file handles the database query for the search term and returns the updated table HTML:

<?php
// Same database credentials as index.php
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_username';
$password = 'your_db_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Get the search term from the AJAX request
$searchTerm = isset($_POST['search']) ? $_POST['search'] : '';

// Prepare a secure query to avoid SQL injection
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE :search OR email LIKE :search");
$stmt->bindValue(':search', "%$searchTerm%", PDO::PARAM_STR);
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<table>
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>Age</th>
    </tr>
    <?php if(count($users) > 0): ?>
        <?php foreach($users as $user): ?>
        <tr>
            <td><?php echo htmlspecialchars($user['id']); ?></td>
            <td><?php echo htmlspecialchars($user['name']); ?></td>
            <td><?php echo htmlspecialchars($user['email']); ?></td>
            <td><?php echo htmlspecialchars($user['age']); ?></td>
        </tr>
        <?php endforeach; ?>
    <?php else: ?>
        <tr>
            <td colspan="4" style="text-align:center;">No matching results found.</td>
        </tr>
    <?php endif; ?>
</table>

Quick Important Notes #

• Security: We use PDO prepared statements to block SQL injection, and htmlspecialchars() to prevent XSS attacks. Never skip sanitizing user input!
• Customization: You can tweak the search logic (e.g., add age filtering) by updating the SQL query in fetch_data.php.
• Styling: The CSS is basic—feel free to adjust it to match your site's design.
• Compatibility: The vanilla JavaScript works in all modern browsers, no external libraries required.

Just replace the database credentials with your own, upload both files to your PHP-enabled server, and you're ready to test. Type a name or email into the search box, and you'll see the table update instantly without reloading the page!

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