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

问题描述

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

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查询类似->多个关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！