PHP+JS+SQL搜索功能故障：无法联合检索姓名及优化需求

Hey there! Let's fix that full-name search issue for your company website—since you're new to PHP, JS, and SQL, I'll break everything down in simple, actionable steps.

What's Causing the Issue? #

Right now, your code treats the entire search input (like "John Doe") as a single string, trying to match it exactly against either the firstname or lastname field. Since "John Doe" doesn't exist as a standalone first or last name in your database, you get no results. We need to split the input into separate keywords and check each against both name fields.

Step-by-Step Solution #

1. Update the Backend (fetch.php) #

First, we'll rewrite the PHP code to safely handle split keywords and block SQL injection (critical for security!). Here's the revised version:

<?php
// Use mysqli object-oriented style for cleaner, safer code
$conn = new mysqli('localhost', 'your_username', 'your_password', 'your_database');

// Check database connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get and clean the search term (handle empty input gracefully)
$searchTerm = trim($_POST['search'] ?? '');

if (empty($searchTerm)) {
    echo json_encode([]);
    exit;
}

// Split search term into keywords, filter out empty strings (from multiple spaces)
$keywords = array_filter(explode(' ', $searchTerm));

// Base query to combine customers and employees into a single result set
$baseQuery = "
    SELECT firstname, lastname, 'customer' AS user_type 
    FROM customers
    UNION ALL
    SELECT firstname, lastname, 'employee' AS user_type 
    FROM employees
    WHERE 1=1
";

$conditions = [];
$params = [];
$paramTypes = '';

// Build search conditions for each keyword
foreach ($keywords as $keyword) {
    // Each keyword must match either firstname or lastname
    $conditions[] = "(firstname LIKE ? OR lastname LIKE ?)";
    // Wrap keywords in wildcards for partial matches
    $params[] = "%$keyword%";
    $params[] = "%$keyword%";
    // Mark parameters as strings ('s' for each)
    $paramTypes .= 'ss';
}

// Add conditions to the query if we have keywords
if (!empty($conditions)) {
    $baseQuery .= " AND " . implode(" AND ", $conditions);
}

// Prepare and execute the query safely (prevents SQL injection)
$stmt = $conn->prepare($baseQuery);
if (!empty($params)) {
    $stmt->bind_param($paramTypes, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

// Collect results into an array
$results = [];
while ($row = $result->fetch_assoc()) {
    $results[] = $row;
}

// Send results as JSON to the frontend
echo json_encode($results);

// Clean up database connections
$stmt->close();
$conn->close();
?>

2. Improve the Frontend JavaScript #

We'll add a debounce function to avoid spamming the server with every keystroke, plus smoother user feedback:

// Debounce function: waits 300ms after the last keystroke to run the search
function debounce(func, delay) {
    let timeoutId;
    return function(...args) {
        clearTimeout(timeoutId);
        timeoutId = setTimeout(() => func.apply(this, args), delay);
    };
}

// Get DOM elements
const searchInput = document.getElementById('search-bar');
const resultsContainer = document.getElementById('results');

// Debounced search logic
const debouncedSearch = debounce(function() {
    const searchTerm = this.value.trim();
    
    // Skip search if input is too short (adjust length as needed)
    if (searchTerm.length < 2) {
        resultsContainer.innerHTML = '';
        return;
    }

    // Show loading state
    resultsContainer.innerHTML = '<p>Searching...</p>';

    // Send request to backend
    fetch('fetch.php', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/x-www-form-urlencoded',
        },
        body: `search=${encodeURIComponent(searchTerm)}`
    })
    .then(response => response.json())
    .then(data => {
        resultsContainer.innerHTML = '';

        // Handle no results
        if (data.length === 0) {
            resultsContainer.innerHTML = '<p>No matches found.</p>';
            return;
        }

        // Render results
        data.forEach(item => {
            const resultItem = document.createElement('div');
            resultItem.className = 'result-item';
            resultItem.textContent = `${item.firstname} ${item.lastname} (${item.user_type})`;
            resultsContainer.appendChild(resultItem);
        });
    })
    .catch(error => {
        resultsContainer.innerHTML = '<p>Oops, something went wrong.</p>';
        console.error('Search error:', error);
    });
}, 300);

// Attach debounced search to input event
searchInput.addEventListener('input', debouncedSearch);

Key Optimization Tips for Beginners #

• Always block SQL injection: Never insert user input directly into SQL queries—use prepared statements like we did above to keep your database safe.
• Case-insensitive search: If your database is case-sensitive, modify the query to use LOWER() for consistent results:

  (LOWER(firstname) LIKE LOWER(?) OR LOWER(lastname) LIKE LOWER(?))
  

• Prioritize exact matches: To show exact name matches first, add an ORDER BY clause to your query:

  ORDER BY (firstname = ? AND lastname = ?) DESC, firstname ASC
  

  You'll need to add the exact keyword values as additional parameters for this.
• Add database indexes: Create indexes on the firstname and lastname fields to speed up search queries as your user list grows.
• Pagination: If you have hundreds of users, add pagination to avoid loading all results at once. Modify the query with LIMIT 10 OFFSET 0 and add page controls to the frontend.

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