PHP PDO MySQL 查询 LIKE ->多个关键词 [英] PHP PDO MySQL query LIKE -> multiple keywords

问题描述

我在 MySQL 中有一个用户表，想按名称进行搜索.现在我有以下代码:

I have a users table in MySQL and would like a search by name. Right now I have the following code:

<?php
$search = @$_GET['q'];
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$query = $con->prepare('SELECT * FROM `users` WHERE name LIKE ?');
$query->execute(array('%'.$search.'%'));

$result = $query->rowCount();
echo $result;
?>

问题是我想要多个关键字.假设有人输入这里应该是一个人的好名字"，然后它会搜索这里"、应该"、是"等，并显示姓名"列中有单词的每一行的结果.我在网上搜索并了解到可以执行OR name LIKE ?"与关键字一样多，但我无法真正让它工作，我不确定它是否用大约 20 个词进行了足够的优化(以防他们用那么多词进行搜索).如果应该使用它，您能帮我更改我的代码，使其独立搜索每个单词吗?

The problem is that I want to have multiple keywords. Let's say someone types "Here should be a nice name of a person" then it would search for "here", "should", "be" etc. and display results for every row where there words are in 'name' column. I searched on the web and read that it is possible to do "OR name LIKE ?" as many times as the keywords, but I could not really get it working and I'm not sure if it is optimized enough with ~20 words (in case they search with that many words). If it should be used, can you help me change my code so it would search for every word independently?

谢谢！

我能够通过在此线程中发帖的一个人解决此问题.以下解决方案对我有用:

I was able to fix this issue by one guy who posted in this thread. The following solution works for me:

<?php
$search = isset($_POST['q']) ? $_POST['q'] : '';
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$split_words = explode(" ", $search);

if(count($split_words) > 0) {

    $sql = "SELECT * FROM `users` WHERE ";

    for($i=0 ; $i < count($split_words); $i++){
        $sql .= " name LIKE ? OR";
    }

    $sql = substr($sql , 0, -3); //Remove last 3 characters OR with space
    array_walk($split_words, "addPercentage");


    $query = $con->prepare($sql);
    $query->execute($split_words);
}

function addPercentage(&$value, $key) {
    $value = '%'.$value.'%';
}
?>

推荐答案

您不应该使用 @ 来消除错误，这是一种不好的做法，请检查该值是否已设置.下面的示例应该可以工作，但结果可能并不完全相关.

You shouldn't use @ to silence errors it is a bad practice, check if the value is set. The example below should work, but the results might not be all that relevant.

$search = isset($_GET['q']) ? $_GET['q'] : ''; 
$search = strtoupper($search);
$search = strip_tags($search); 
$search = trim($search);
$words = explode(' ', $search);
$words_condition = array();
$arguments = array();
foreach ($words as $word) {
    $words_condition[] = 'name LIKE ?';
    $arguments[] = '%'.$word.'%';
}

$query = $con->prepare('SELECT * FROM `users` WHERE '.implode(' OR ', $words_condition));
$query->execute($arguments);

$result = $query->rowCount();
echo $result;

这篇关于PHP PDO MySQL 查询 LIKE ->多个关键词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！