php - 使用 "LIKE"匹配mysql查询中的多个单词

Question

我的应用程序允许搜索文章。因此，当用户输入字符串“articlex”时，查询有效并显示结果，但是当输入多个单词“articlexarticley”时，查询不会显示结果。

目前我正在使用此查询

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

其中$search_string仅包含用户输入

如何使查询也适用于多个单词？

Answer 1

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

mysql_** 函数示例(已弃用)

/* 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);