MySQLi按相关性排序：含关键词的title结果优先置顶

Absolutely, you can implement this directly in your SQL query without needing external algorithms! The key is to adjust the ORDER BY clause to prioritize rows where the search keyword appears in the title field.

How to Modify Your Query #

You can use a CASE statement in the ORDER BY clause to create a custom sort priority. Here's how to update your code:

if (isset($_POST['search'])) {
    // Fix: Assign the POST value first before sanitizing to avoid undefined variable
    $search = $_POST['search'];
    $search = filter_var($search, FILTER_SANITIZE_SPECIAL_CHARS);
    
    // Modified SQL with priority sorting
    $sql = "SELECT * FROM mydb 
            WHERE title LIKE :search OR description LIKE :search OR content LIKE :search 
            ORDER BY 
                CASE WHEN title LIKE :search THEN 1 ELSE 0 END DESC,
                dates DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
    $stmt->execute();
    
    if ($stmt->rowCount() > 0) {
        $result = $stmt->fetchAll();
        foreach($result as $row) {
            // Your loop logic here
        }
    } else {
        // No results handling
    }
    
    // Cleanup
    $stmt = null;
    $pdo = null;
}

How It Works #

• The CASE statement generates a temporary value for each row:
  • 1 if the title contains the search keyword
  • 0 otherwise
• Ordering by this value in descending order (DESC) ensures all title-matching rows appear first.
• After prioritizing title matches, the results are still sorted by dates DESC as your original query intended.

Bonus: Granular Priority (Title > Description > Content) #

If you want even more control (e.g., description matches come before content matches), you can extend the CASE statement to assign tiered priorities:

ORDER BY 
    CASE 
        WHEN title LIKE :search THEN 3
        WHEN description LIKE :search THEN 2
        WHEN content LIKE :search THEN 1
        ELSE 0 
    END DESC,
    dates DESC

This assigns higher numerical values to more relevant matches, so they appear at the top of your results in the order of title → description → content.

Quick Fix Note #

I adjusted your original code to assign $search from $_POST['search'] before sanitizing it—this avoids an undefined variable error that would have occurred otherwise.

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