Improving my PHP search of a MySQL database?

Question

I'm building a website that has records that need to be searched through. I just realized that the search function is too precise. It's a recipe website, so for example, if the user types "keylime pie", the recipe named "Key Lime Pie" won't show up in the results. I'm not sure if there's a script that I can get for this, but I'd really appreciate some help.

Here's my current query:

SELECT * FROM `recipes` WHERE 
    `recipe_title` LIKE '%$search%' // Key Lime Pie
    OR `recipe_summary` LIKE '%$search%' // I love key lime pie
    OR `recipe_categories` LIKE '%$search%' //desserts, pies
//... etc

Thanks!

One thing you can do is replace each space of keyword with %, so: '%keylime%pie%' should return more results. — DontVoteMeDown
– DontVoteMeDown, Commented Sep 26, 2013 at 14:52
I suggest you to google for "FULLTEXT search MySQL". It seems to me it's what you're looking for — STT LCU
– STT LCU, Commented Sep 26, 2013 at 15:02
You can try soundex function : dev.mysql.com/doc/refman/5.0/en/… — kmas
– kmas, Commented Sep 26, 2013 at 15:04

codefreak · Accepted Answer · 2013-09-26 14:54:56Z

2

Like queries will soon take down your MySQL database.

If it's not too complicated for you, use Sphinx for searching on mysql it will give you nice results based on keyword density and keyword weight etc. And it's really really fast.

answered Sep 26, 2013 at 14:54

codefreak

7,1413 gold badges45 silver badges54 bronze badges

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

3 Comments

Alireza Fallah Over a year ago

Of course if you are not using UTF-8 encoding or non-English languages .

tadman Over a year ago

MySQL 5.6 supports full text searches on InnoDB tables so it's worth using that instead of Sphinx if you have the option. Otherwise, try Sphinx or Elastic Search.

codefreak Over a year ago

Sphinx has many more features than Full text of MySQL 5.6 has.

Shauna · Accepted Answer · 2013-09-26 15:01:12Z

2

Your best bet may be to look into full text searching. MySQL only supports full text search in MyISAM by default, but if you're running MySQL 5.6 or later, you can do it in InnoDB, at well.

Alternatively, you can run dedicated full text search tools such as Lucene or Sphinx. These are more sophisticated tools that include things like relevance ranking, and may even be able to handle spelling differences/errors (depending on the tool).

answered Sep 26, 2013 at 15:01

Shauna

9,5942 gold badges39 silver badges54 bronze badges

Comments

sskoko · Accepted Answer · 2013-09-26 14:54:31Z

0

Not sure about those double quotes, try this:

SELECT * FROM `recipes` WHERE (`recipe_title` LIKE '%$search%'
    OR `recipe_summary` LIKE '%$search%'
    OR `recipe_categories` LIKE '%$search%')

answered Sep 26, 2013 at 14:54

sskoko

8397 silver badges18 bronze badges

2 Comments

Caleb Over a year ago

oops-sorry, that's for inserting the PHP variable

Sébastien Over a year ago

To avoid this confusion you should include the double quotes around your statement.

Sébastien · Accepted Answer · 2013-09-26 15:07:23Z

I have found it useful to add a related table where you add all the keywords pertaining to a specific entry.

So if the name of your recipe is "Key Lime Pie" you could have one of the keywords be "KeyLime Pie". That allows for typos by the user or allows you to add obvious related terms that people might look for.

This very simple problem could also be solved by adding a column in your recipes table where you store the name (or other short fields) without spaces (and maybe without punctuation). So for instance the name "Key Lime Pie" would be also stored as "KeyLimePie".

Now you can find the recipe by searching for the name "keylime pie": first remove the spaces (and maybe also the punctuation) from the search term. "keylime pie" becomes "keylimepie". That way your query will return the "Key Lime Pie" entry.

You can also expand your search by adding partial LIKE specifically for the beginning or the end of the searched term.

Assuming you store the title without spaces in a column called recipe_title_no_space

"SELECT * FROM recipes
WHERE
recipe_title_no_space LIKE '%".$search."'
OR
recipe_title_no_space LIKE '".$search."%'"

That way you will fin a match for "keylime" and also for "pie keylime". Be careful that this does not return too many/poor results in some cases.

Edward · Accepted Answer · 2013-09-26 18:05:54Z

There are a few ways of customising search results as stated above, but if you wanted to experiment...

$searchArray = explode(' ', $search);
$idArray = array();

for($i=0; $i<count($searchArray) $i++) {
    $query = "SELECT * FROM table WHERE recipe_title LIKE '%$search%' 
    AND recipe_summary LIKE '%$search%' AND recipe_categories LIKE '%$search%'";
    $result = mysqli_query($link, $query);
    $row = mysqli_fetch_array($result);
    $idArray = array_merge($idArray, $row['id']);

}

$mostMatches = array_count_values($idArray);

foreach($mostMatches as $key => $value) {
    if($value > 1) {$popularResultId[] = $key;}
}

for($i=0; $i<count($popularResultId)$i++) {
    mysqli_data_seek($result, $popularResultId[$i]);
    $row = mysqli_fetch_array($result);
    echo "Title: {$row['recipe_title']}\n\r";
    echo "Summary: {$row['recipe_summary']}\r\n";
    echo "category: {$row['recipe_categories']}\r\n";
}

If my theory is correct you should be able to tune the results by changing the "if($value > 1)" up or down, you could even have it auto adjust depending on how big your database got if you wanted.

Collectives™ on Stack Overflow

Improving my PHP search of a MySQL database?

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related