使用PHP和PDO进行SQL全文搜索 [英] SQL full text search with PHP and PDO

问题描述

我正在尝试使用PHP和PDO编写简单的全文本搜索.我不太确定最好的方法是通过SQL和PDO搜索数据库.我发现了这个此脚本，但这是旧的MySQL扩展.我写了这个函数，女巫应该计算搜索匹配项，但是SQL无法正常工作.输入的搜索字符串如下所示:23+more+people

I'm trying to write a simple, full text search with PHP and PDO. I'm not quite sure what the best method is to search a DB via SQL and PDO. I found this this script, but it's old MySQL extension. I wrote this function witch should count the search matches, but the SQL is not working. The incoming search string look like this: 23+more+people

function checkSearchResult ($searchterm) {
    //globals
    global $lang; global $dbh_pdo; global $db_prefix;
    $searchterm = trim($searchterm);
    $searchterm = explode('+', $searchterm);
    foreach ($searchterm as $value) {
        $sql = "SELECT COUNT(*), MATCH (article_title_".$lang.", article_text_".$lang.") AGINST (':queryString') AS score FROM ".$db_prefix."_base WHERE MATCH (article_title_".$lang.", article_text_".$lang.") AGAINST ('+:queryString')";
        $sth = $dbh_pdo->prepare($sql);
        $sql_data = array('queryString' => $value);
        $sth->execute($sql_data);
        echo $sth->queryString;
        $row = $sth->fetchColumn();
        if ($row < 1) {
            $sql = "SELECT * FROM article_title_".$lang." LIKE :queryString OR aricle_text_".$lang." LIKE :queryString";
            $sth = $dbh_pdo->prepare($sql);
            $sql_data = array('queryString' => $value);
            $sth->execute($sql_data);
            $row = $sth->fetchColumn(); 
        }
    }
    //$row stays empty - no idea what is wrong
    if ($row > 1) {
        return true;
    }
    else {
        return false;
    }
}

推荐答案

在准备$sql_data数组时，需要在参数名称前加上一个冒号:

When you prepare the $sql_data array, you need to prefix the parameter name with a colon:

array('queryString' => $value);

应为:

array(':queryString' => $value);

在您的第一个SELECT中，您使用的是AGINST而不是AGAINST.

In your first SELECT, you have AGINST instead of AGAINST.

您的第二个SELECT似乎缺少FROM之后的表名和WHERE子句. LIKE参数的格式也不正确.应该是这样的:

Your second SELECT appears to be missing a table name after FROM, and a WHERE clause. The LIKE parameters are also not correctly formatted. It should be something like:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE '%:queryString%' OR aricle_text_".$lang." LIKE '%:queryString%'";

更新1 >>

对于两个 SELECT语句，每个参数都需要唯一的标识符，并且LIKE通配符应放在值中，而不是语句中.因此，您的第二条语句应如下所示:

For both SELECT statements, you need unique identifiers for each parameter, and the LIKE wildcards should be placed in the value, not the statement. So your second statement should look like this:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE :queryString OR aricle_text_".$lang." LIKE :queryString2";

注意queryString1和queryString2，不带引号或%通配符.然后，您还需要更新数组:

Note queryString1 and queryString2, without quotes or % wildcards. You then need to update your array too:

$sql_data = array(':queryString1' => "%$value%", ':queryString2' => "%$value%");

请参见 PDOStatement-> execute 的 Parameters 部分有关使用多个具有相同值的参数的详细信息，请参见a>.因此，我倾向于使用问号作为占位符，而不是命名参数.我觉得它更简单，更整洁，但这是一个选择的问题.例如:

See the Parameters section of PDOStatement->execute for details on using multiple parameters with the same value. Because of this, I tend to use question marks as placeholders, instead of named parameters. I find it simpler and neater, but it's a matter of choice. For example:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE ? OR aricle_text_".$lang." LIKE ?";

$sql_data = array("%$value%", "%$value%");

<<更新1结束

我不确定第二个SELECT是做什么的，因为我会认为，如果第一个SELECT找不到查询值，第二个也不会找到查询值.但是我在MySQL全文搜索方面做得还不够，所以我可能会遗漏一些东西.

I'm not sure what the second SELECT is for, as I would have thought that if the first SELECT didn't find the query value, the second wouldn't find it either. But I've not done much with MySQL full text searches, so I might be missing something.

无论如何，您确实需要仔细检查SQL和所有错误.您可以通过打印 PDOStatement->errorCode :<<的结果来获取错误信息. /p>

Anyway, you really need to check the SQL, and any errors, carefully. You can get error information by printing the results of PDOStatement->errorCode:

$sth->execute($sql_data);
$arr = $sth->errorInfo();
print_r($arr);

更新2 >>

值得一提的另一点:确保在将变量插值到SQL语句中时，仅使用受信任的数据.也就是说，不允许将用户提供的数据用于表名或列名.使用预处理语句非常好，但是这些语句仅保护参数，而不保护SQL关键字，表名和列名.所以:

Another point worth mentioning: make sure that when you interpolate variables into your SQL statement, that you only use trusted data. That is, don't allow user supplied data to be used for table or column names. It's great that you are using prepared statements, but these only protect parameters, not SQL keywords, table names and column names. So:

"SELECT * FROM ".$db_prefix."_base"

...正在使用变量作为表名的一部分.确保此变量包含可信数据.如果来自用户输入，请先将其与白名单对照.

...is using a variable as part of the table name. Make very sure that this variable contains trusted data. If it comes from user input, check it against a whitelist first.

<<更新1结束

您应该阅读 MySQL全文搜索功能和字符串比较函数.您需要学习如何构造基本的SQL语句，否则编写甚至简单的搜索引擎也将变得极为困难.

You should read the MySQL Full-Text Search Functions, and the String Comparison Functions. You need to learn how to construct basic SQL statements, or else writing even a simple search engine will prove extremely difficult.

PHP站点上也有很多PDO示例.您可以从 PDOStatement-> execute 的文档开始，其中包含以下示例如何使用该功能.

There are plenty of PDO examples on the PHP site too. You could start with the documentation for PDOStatement->execute, which contains some examples of how to use the function.

如果您有权访问 MySQL CLI ，甚至是PHPMyAdmin ，您可以在没有所有PHP混乱的情况下试用SQL.如果您打算将任何数据库开发工作作为PHP应用程序的一部分进行，那么您将发现能够独立于PHP测试SQL是一个很大的帮助.

If you have access to the MySQL CLI, or even PHPMyAdmin, you can try out your SQL without all the PHP confusing things. If you are going to be doing any database development work as part of your PHP application, you will find being able to test SQL independently of PHP a great help.

这篇关于使用PHP和PDO进行SQL全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！