如何在PHP+MySQL用户搜索页面实现类似谷歌的相关搜索功能？

Got it, let's break this down step by step to get that clean, Google-like related search box working at the bottom of your page after it loads. We'll cover safe database querying, frontend styling, and even touch on async loading if you want to avoid blocking page rendering.

Step 1: Fix & Secure Your Database Queries #

First, let's update your existing code to safely fetch the current user and their related users (we'll use a sample logic here—you can swap it for your actual business rules, like same category, mutual connections, etc.). We'll use prepared statements to avoid SQL injection (critical for security!).

// Assume $db is your valid MySQLi connection
$id = 1; // Replace with your actual user ID source (e.g., $_GET['id'])

// 1. Fetch current user safely
$stmt = $db->prepare("SELECT username, category FROM `users` WHERE `id` = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
$currentUser = $result->fetch_assoc();
$stmt->close();

// 2. Fetch related users (example: same category, exclude current user)
$relatedUsers = [];
if ($currentUser) {
    $stmt = $db->prepare("SELECT username FROM `users` WHERE `category` = ? AND `id` != ? LIMIT 3");
    $stmt->bind_param("si", $currentUser['category'], $id);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $relatedUsers[] = $row['username'];
    }
    $stmt->close();
}

Step 2: Render the Search Box in Your HTML #

Add this HTML snippet at the bottom of your page (right before </body> is ideal). We'll use clean classes to style it later.

<!-- People Also Search Box -->
<div class="people-also-search">
    <p class="current-user-text"><?php echo $currentUser['username']; ?> ...</p>
    <p class="search-label">People also search :</p>
    <div class="related-users-list">
        <?php foreach ($relatedUsers as $user): ?>
            <span class="related-user"><?php echo htmlspecialchars($user); ?></span>
        <?php endforeach; ?>
    </div>
</div>

Step 3: Add CSS for Google-like Styling #

Drop this CSS in your stylesheet (or a <style> tag in the <head>) to match the look you described:

.people-also-search {
    max-width: 650px;
    margin: 30px auto;
    padding: 16px;
    border-top: 1px solid #e0e0e0;
}

.current-user-text {
    font-size: 16px;
    color: #202124;
    margin: 0 0 8px 0;
}

.search-label {
    font-size: 14px;
    color: #5f6368;
    margin: 0 0 12px 0;
}

.related-users-list {
    display: flex;
    gap: 12px;
    flex-wrap: wrap;
}

.related-user {
    font-size: 15px;
    color: #1a0dab;
    cursor: pointer;
    padding: 6px 12px;
    border-radius: 20px;
    background-color: #f1f3f4;
    transition: background-color 0.2s;
}

.related-user:hover {
    background-color: #e8eaed;
}

Step 4: Optional - Load Async After Page Load #

If you want the box to appear after the entire page finishes loading (to avoid blocking rendering), use AJAX instead of server-side rendering:

1. Add a placeholder container to your HTML: #

<div id="related-search-container" class="people-also-search" style="display: none;"></div>

2. Add this JavaScript (before </body>): #

window.addEventListener('load', function() {
    const userId = <?php echo $id; ?>; // Pass user ID to JS

    fetch(`get-related-users.php?id=${userId}`)
        .then(response => response.json())
        .then(data => {
            const container = document.getElementById('related-search-container');
            container.innerHTML = `
                <p class="current-user-text">${data.currentUser} ...</p>
                <p class="search-label">People also search :</p>
                <div class="related-users-list">
                    ${data.relatedUsers.map(user => `<span class="related-user">${user}</span>`).join('')}
                </div>
            `;
            container.style.display = 'block';
        })
        .catch(err => console.log('Failed to load related users:', err));
});

3. Create get-related-users.php (handles the AJAX request): #

<?php
// Include your database connection here
$db = new mysqli('localhost', 'username', 'password', 'database');

if (isset($_GET['id'])) {
    $id = $_GET['id'];

    // Fetch current user
    $stmt = $db->prepare("SELECT username FROM `users` WHERE `id` = ?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $currentUser = $result->fetch_assoc()['username'];
    $stmt->close();

    // Fetch related users
    $stmt = $db->prepare("SELECT username FROM `users` WHERE `category` = (SELECT category FROM users WHERE id = ?) AND `id` != ? LIMIT 3");
    $stmt->bind_param("ii", $id, $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $relatedUsers = [];
    while ($row = $result->fetch_assoc()) {
        $relatedUsers[] = htmlspecialchars($row['username']);
    }
    $stmt->close();

    // Return JSON response
    header('Content-Type: application/json');
    echo json_encode([
        'currentUser' => htmlspecialchars($currentUser),
        'relatedUsers' => $relatedUsers
    ]);
}
?>

Key Notes #

• Customize the related user logic: Swap the category-based query with whatever makes sense for your app (e.g., users with similar interests, recent searches, etc.).
• Always sanitize output: Use htmlspecialchars() when echoing user data to prevent XSS attacks.
• Security first: Prepared statements are non-negotiable here to avoid SQL injection.

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