Search code examples
phpmysqlsqlsql-like

SQL LIKE query to match any single word in a string


I am trying to query:

$title2 = (isset($row_DetailRS1['r_bus_name']) ? $row_DetailRS1['r_bus_name'] : "");

$query_test = "SELECT * 
                 FROM table 
                WHERE r_email = '$email2' 
                  AND r_city != '$location2' 
                  AND r_bus_name LIKE '%$title2%' 
             ORDER BY r_bus_name";

The r_bus_name LIKE '%$title2' is defined from above and is grabbing the TITLE of the EVENT from the POST. The title is usually two to three words...

How do I query r_bus_name LIKE (any of the words in the event title $title2)?

Because right now it is taking the whole value of $title2.. I need to split them up or explode them in words, so if the title is something like "Test title here" then "Tester title here" would match?


Solution

  • If you want to search on EACH of the words in the title, you would need to construct a query using OR operations to apply them to the same query.

    It might look something like this:

    // break apart the title using spaces
    $title2 = (isset($row_DetailRS1['r_bus_name']) ? $row_DetailRS1['r_bus_name'] : "");
    $title_keywords = explode(" ", $title2);
    
    // construct conditions (Note the ampersand causes pass-by-reference)
    foreach($title_keywords as &$keyword) {
        $keyword = "r_bus_name LIKE '%".mysql_real_escape_string($keyword)."%'";
    }
    $keyword_search = "(" . implode(" OR ", $title_keywords) . ")";
    
    $query_test = "SELECT * 
        FROM table 
        WHERE r_email = '".mysql_real_escape_string($email2)."' 
            AND r_city != '".mysql_real_escape_string($location2)."' 
            AND ".$keyword_search." 
        ORDER BY r_bus_name";
    
    // ...