如何用MySQL正则表达式结合AND运算符实现多关键词搜索？

Let's fix this step by step. The core issue with your current code is that it uses REGEXP with OR logic for all keywords, while you need every keyword to match either the vegetable or fruits column (AND logic between keywords), plus ensure unique id results. Also, your original code has critical SQL injection vulnerabilities—we'll address that too with secure prepared statements.

Key Fixes & Implementation #

Here's a complete, secure solution that matches your expected behavior:

<?php
// Initialize database connection (replace with your actual credentials)
$conn = mysqli_connect("your_host", "your_user", "your_password", "your_database");

$inputKeywords = '';
$results = [];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $inputKeywords = trim($_REQUEST['keyword'] ?? '');
    $keywords = [];
    
    // Split input by commas, clean up each keyword, filter out empty values
    if (!empty($inputKeywords)) {
        $splitKeywords = explode(',', $inputKeywords);
        foreach ($splitKeywords as $kw) {
            $trimmedKw = trim($kw);
            if (!empty($trimmedKw)) {
                $keywords[] = $trimmedKw;
            }
        }
    }

    if (!empty($keywords)) {
        // Build conditional clauses for each keyword
        $whereClauses = [];
        $params = [];
        $paramTypes = '';
        
        foreach ($keywords as $kw) {
            // Each keyword checks both columns with OR logic
            $whereClauses[] = '(vegetable LIKE ? OR fruits LIKE ?)';
            // Wrap keyword in wildcards for fuzzy matching (add twice, once per column)
            $params[] = "%{$kw}%";
            $params[] = "%{$kw}%";
            // Mark both parameters as strings for the statement
            $paramTypes .= 'ss';
        }
        
        // Combine all clauses with AND logic (every keyword must match)
        $where = implode(' AND ', $whereClauses);
        // Use DISTINCT to ensure each id only appears once
        $sql = "SELECT DISTINCT id, vegetable, fruits FROM your_table_name WHERE {$where}";
        
        // Prepare and execute the secure statement
        $stmt = mysqli_prepare($conn, $sql);
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, $paramTypes, ...$params);
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);
            
            // Fetch matching rows
            while ($rs = mysqli_fetch_assoc($result)) {
                $results[] = $rs;
            }
            
            mysqli_stmt_close($stmt);
        }
    }
}
?>

<html>
<body>
<form method="post">
    <input type="text" class="form-control" placeholder="SEARCH..." value="<?= htmlspecialchars($inputKeywords, ENT_QUOTES) ?>">
    <button type="submit">Search</button>
</form>

<?php if (!empty($results)): ?>
    <h3>Matching Results:</h3>
    <ul>
        <?php foreach ($results as $row): ?>
            <li>ID: <?= $row['id'] ?> | Vegetable: <?= htmlspecialchars($row['vegetable']) ?> | Fruits: <?= htmlspecialchars($row['fruits']) ?></li>
        <?php endforeach; ?>
    </ul>
<?php endif; ?>
</body>
</html>

What This Does #

1. Input Sanitization: Splits comma-separated input, trims whitespace, and removes empty entries to avoid invalid SQL clauses.
2. Correct Logic: For each keyword, creates a clause like (vegetable LIKE '%Keyword%' OR fruits LIKE '%Keyword%'), then joins all clauses with AND—exactly matching your desired SQL structure.
3. Security: Uses prepared statements to separate user input from SQL logic, eliminating SQL injection risks.
4. Unique Results: The DISTINCT keyword ensures each id appears only once in the output, even if a row matches multiple keywords across columns.
5. XSS Protection: Uses htmlspecialchars when displaying user input and database data to prevent cross-site scripting attacks.

Testing with Your Sample Data #

If you input Spinach, Watermelon, the generated SQL will be:

SELECT DISTINCT id, vegetable, fruits FROM your_table_name WHERE (vegetable LIKE '%Spinach%' OR fruits LIKE '%Spinach%') AND (vegetable LIKE '%Watermelon%' OR fruits LIKE '%Watermelon%')

This returns only row 3 (ID: 3, Vegetable: Spinach, Fruits: Watermelon)—exactly the result you want.

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