SQL LIKE query to match any single word in a string

I am t开发者_Python百科rying 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?

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";

// ...

Assuming the table is MyISAM, you could use the native Full Text Search (FTS) functionality:

$query = sprintf("SELECT t.* 
                    FROM YOUR_TABLE t
                   WHERE t.r_email = '%s' 
                     AND t.r_city != '%s' 
                     AND MATCH(t.r_bus_name) AGAINST('%s')
                ORDER BY t.r_bus_name",
                 mysql_real_escape_string($email2),
                 mysql_real_escape_string($location2),
                 mysql_real_escape_string($title2) );

$result = mysql_query($query);

Sadly, MySQL doesn't support FTS on the InnoDB engine.

Addendum

I recommend using sprintf if you aren't using PDO/etc for prepared statements, to protect against SQL injection attacks.

Split title2 on spaces and do multiple likes:

$titleArray = split(" ", $title2);

$query_test = "SELECT * 
                 FROM table 
                WHERE r_email = '$email2' 
                 AND r_city != '$location2'
                 AND ("
    foreach ($title as titleArray)
        $query_test .= "OR r_bus_name LIKE '%$title%'" 
$query_test .= "ORDER BY r_bus_name";