使用“LIKE”匹配mysql查询中的多个单词

My application allows searching of articles. So when a user enters the string "articlex" the query works & results are show but when multiple words are entered "articlex articley" the query shows no results.

Cuurently I'm using this query

$querySub = 'SELECT * FROM table WHERE (col1 LIKE "%'.$search_string.'%") OR (col2 LIKE "%'.$search_string.'%") OR (col3 LIKE "%'.$search_string.'%")';

Where $search_string only contains the input by user

How can I make the query applicable for multiple words as well?

You can use REGEXP for this perpose .....or if you want multiple word search then you need to explode search string by space and then search this string from table using LIKE

       if you search "articlex articley" then it will search whole word in LIKE operator

While using variables for like query, you could use

Select * from table where keyword LIKE '%{$to_be_searched}%'

use only one "%" at the end. like

$querySub = SELECT * FROM table WHERE (col1 LIKE '$search_string%'");

This will search for both "articlex" and "articley"

Example for PDO

/* Assume $pdo is already prepared as PDO instance. */

// search query split by spaces
$user_input = 'foo bar baz';

// create string for PDO::prepare()
$sql = 'SELECT * FROM testTable WHERE ';
$wheres = $values = array();
foreach (array_filter(explode(' ', $user_input), 'strlen') as $keyword) {
    $wheres[] = 'col1 LIKE ?';
    $values[] = '%' . addcslashes($keyword, '%_\\') . '%'; // this is escape for LIKE search
}
$sql .= $wheres ? implode(' OR ', $wheres) : '1';

// bind values and execute
$stmt = $pdo->prepare($sql);
$stmt->execute($values);

Example for mysql_** functions (deprecated)

/* Assume $link is already prepared as MySQL resource. */

// search query split by spaces
$user_input = 'foo bar baz';

// create string for mysql_auery()
$sql = 'SELECT * FROM testTable WHERE ';
foreach (array_filter(explode(' ', $user_input), 'strlen') as $keyword) {
    $wheres[] = 'col1 LIKE ' . mysql_real_escape_string(
        '%' . addcslashes($keyword, '%_\\') . '%',
        $link
    );
}
$sql .= !empty($wheres) ? implode(' OR ', $wheres) : '1';

// execute
$result = mysql_query($sql, $link);

delimiter // CREATE PROCEDURE proc_advancesearch(IN p_data VARCHAR(80) , IN act_mode VARCHAR(10))

begin

declare p_s_data varchar(250); SET sql_mode='NO_ENGINE_SUBSTITUTION';

set p_s_data= REPLACE(p_data,' ','% '); set p_s_data=concat('% ',p_s_data,'%');

select prodtmappid,ProId, ProductName,FinalPrice, fn_image(prodtmappid) as image from tblproductmapforlisting where ProStatus='A' and ( concat(ProductName,' ',brandname,' ',CatName) like (p_s_data)

)

or ProductName like concat(p_data,'%')) group by tblproductmapforlisting.ProId order by propriority asc ;

end