以任意顺序查询多个单词 PHP+MySQL [英] Query with multiple words in any order PHP+MySQL

问题描述

我有一个包含多个搜索字段的表单.其中之一是关键字.在结果页面中，我想构建一个查询，允许以任何顺序获取多个单词(即word1"、word2"、word3"或word3"、word1"、word2"等).我知道我应该使用数组，我已经尝试过了，但我还没有解决！下面是我的代码.

谢谢！

解决方案

你不能用你的查询构造.你正在建设

... field LIKE '%foo bar baz%'

它将按顺序搜索文字文本 foo bar baz.

如果你想要任何"订单，你必须按摩文本:

... field LIKE '%foo%' AND field LIKE '%bar%' AND field LIKE '%baz%'

对于这种情况，您最好切换到全文索引并使用 match against 代替.它专为这种搜索而设计，其中 LIKE '%...%' 效率极低.

此外，您很容易受到sql注入攻击的攻击.<​​/p>

I've a form with multiple searchfields. One of them is for the keywords. In the results page I'd like to build a query that allows to get multiple words in any order (i.e. "word1", "word2", "word3" OR "word3", "word1", "word2", etc.). I know I should use an array, and I've tried, but I haven't worked it out! Here below my code.

Thanks!

    <?php
    $dbcnx = @mysql_connect('xxx', 'xxx', 'xxx');
    if (!$dbcnx) {
    exit('<p>Non posso connettermi al database.</p>');
    }

    if (!@mysql_select_db('xxx')) {
    exit('<p>Non posso connettermi al database.</p>');
    }

    // The basic SELECT statement
    $select = 'SELECT DISTINCT id_articolo, titolo, testo_articolo, data_articolo, categoria_id, abstract_articolo, id_categoria, categoria, link_categoria';
    $from   = ' FROM Articoli, Categoria';
    $where  = ' WHERE 1=1 AND categoria_id=id_categoria';
    $order  = ' ORDER BY data_articolo DESC';
    $aid = $_POST['aid'];
    if ($aid != '') { // An author is selected
    $where .= " AND id_autore='$aid'";
    }
    // HERE I'D LIKE TO OBTAIN A QUERY USING MULTIPLE WORDS IN ANY ORDER
    $searchtext = $_POST['searchtext'];
    if ($searchtext != '') { // Some search text was specified
    $where .= " AND testo_articolo LIKE '%$searchtext%'";
    }

    $results = @mysql_query($select . $from . $where . $order);
    if (!$results) {
    echo '</table>';
    exit('<p>Error retrieving results from database!<br />'.
    'Error: ' . mysql_error() . '</p>');
    }
while ($result = mysql_fetch_array($results)) {
  echo "<tr valign='top'>\n";
  $id = $result['id_articolo'];
  $resulttext = htmlspecialchars($result['testo_articolo']);
  $titolo = $result['titolo'];
  $cat = htmlspecialchars($result['categoria']);
  $link_cat = htmlspecialchars($result['link_categoria']);
  $abstract = accorcia($result['abstract_articolo']);
  $data = convertiDataSql($result['data_articolo']);

  echo "<tr bgcolor=$color><td><a href='aticolo.php?recordID=$id'><strong style='text-transform:uppercase' class='CapitalizeSmall'>$titolo</strong></a><div style='padding-left:8px'><em>$abstract</em></div><span align='center' style='padding-left:8px' class='generale'><strong>Inserito il $data</strong> | Categoria: <a href='$link_cat' class='stile26'>$cat<a/></span><br /><br /></td></tr>\n";

}
?>

解决方案

You can't with your query construction. You're building

... field LIKE '%foo bar baz%'

which will search for the literal text foo bar baz in that order.

if you want "any" order, you'll have to massage the text:

... field LIKE '%foo%' AND field LIKE '%bar%' AND field LIKE '%baz%'

For this sort of thing, you'd be better off switching to a full-text index and using match against instead. It's designed for this kind of searching, where LIKE '%...%' is highly inefficient.

Plus, you're vulnerable to sql injection attacks.

这篇关于以任意顺序查询多个单词 PHP+MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！