PHP Search Using Multiple Words

Question

I am trying to create a search function where a user can input two words into a text field and it will split the words and construct a MySQL query.

This is what I have so far.

$search     = mysql_real_escape_string( $_POST['text_field']);
$search     = explode(" ", $search);

foreach($search as $word)
{
    $where = "";
    $where      .= "product_code LIKE '%". $word ."%'";
    $where      .= "OR description LIKE '%". $word ."%'";

    $query      = "SELECT * FROM customers WHERE $where";
    $result     = mysql_query($query) or die();

    if(mysql_num_rows($result))
    {
        while($row = mysql_fetch_assoc($result))
        {
            $customer['value']  = $row['id'];
            $customer['label']  = "{$row['id']}, {$row['name']} {$row['age']}";
            $matches[]          = $customer;
        }
    }
    else
    {
        $customer['value']  = "";
        $customer['label']  = "No matches found.";
        $matches[]          = $customer;
    }
}

$matches = array_slice($matches, 0, 5); //return only 5 results

It constructs and runs the query, but returns funny results.

Any help would be appreciated.

Any reason you can't use a fulltext index for this? Using like '%...%' precludes the use of indexes and performance will be horrible on big tables. And what's "funny results"? Did they make you laugh? As well, you should be escaping the individual words, not the string BEFORE you manipulate it — Marc B
– Marc B, Commented Nov 4, 2011 at 16:01

sascha · Accepted Answer · 2011-11-04 16:00:54Z

1

MySQL has something called LIMIT, so you last row would be needless.
Use Full-Text-Search for this: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html - It's faster and more elegant

answered Nov 4, 2011 at 16:00

sascha

4,7003 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mario Lurig Over a year ago

His last row is still valid, in that if he has 10 results total from the queries, he only wants to show 5 of them. LIMIT restricts the results from each query. Granted, just choosing the first 5 $matches isn't great results either, but just a clarification.

mobius · Accepted Answer · 2011-11-04 16:13:20Z

0

If your database is on MyISAM table format you could do a Fulltext search on the columns you are interested as Sn0opy mentioned already

Personally I believe that when it comes to mySQL if you actually want to create a great search engine use Sphinx (http://sphinxsearch.com/) or Solr (http://lucene.apache.org/solr/)

There may be a learning curve on both of them, but the results are professional.

answered Nov 4, 2011 at 16:13

mobius

5,1742 gold badges30 silver badges43 bronze badges

Comments

Blind Fish · Accepted Answer · 2011-11-04 16:17:07Z

Any chance of anything more specific than "funny results"? Off the cuff there are several possibilities but it really depends upon the results that are being returned. My PHP is a bit rusty so I will apologize up front if my brain throws in some java rules instead, but at first blush...

Name the array something other than $search. It probably isn't the problem, but it looks odd to have the array created by explode() carry the name of the string being exploded. Try something like $searched = explode(" ", $search); and then use $searched in the subsequent foreach() loop.

What if the user only puts in one search term? If there is no space in $text_field then explode will return an empty array, which should thoroughly jack up your query. You should at least verify that there is a space in $text_field before exploding $search. Likewise, what if the user enters two search terms, but one of the terms is two words separated by a space? Again you are going to get "funny results" because you will get results that you don't want along with duplicated results as the query extends itself to both of the words in a term individually.

Without knowing more of what you mean by "funny results" it is really difficult to trouble shoot this one.

Collectives™ on Stack Overflow

PHP Search Using Multiple Words

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related