Search database using array of terms

[max4]

Hi,

I've created a very basic search engine which I hope to expand upon. Currently it searches through a database column, 'title' in my example, and displays results based on what information is stored. This is what the statement looks like:

$sql = mysql_query("SELECT * FROM post WHERE title LIKE '%$_POST[keywords]%' LIMIT 0,10");

The problem with this is that it is limited to exact phrases. For example, if a title contains the words "Best Post" and a user searches for Post, it will successfully display the title and "Best Post" will also display a match; even "bes" would give a successful hit. However, if there are two titles, one being "Best Post" and another being "Future Thread" and a user searches "Best Thread" nothing shows up. I would like both "Best Post" and "Future Thread" to be successful matches. So I have tried the following:

$string = $_POST['keywords'];
$separated = strtok($string, " ");
$sql = mysql_query("SELECT * FROM post WHERE title LIKE '%$separated%' LIMIT 0,10");

But this will only generate a successful hit on the first separated term rather than on all separated terms. Now, I knew it was a long shot for that to work, but I'm still very new these types of statements. Any help as to how I can display both titles, using keywords from both, would be much appreciated.

Thank you,
Max

[Alcoholico]

There are several ways to accomplish what you want, my personal favourite is using a "fulltext mysql index", bing it, google it, y! it, ask it, teoma it or cuil it :).

A quick and dirty way could be something like:

<?php 
$string = $_POST['keywords'];
$string = mysql_real_escape_string(trim(strip_tags($string))); //Basic sanitation, existent mysql connection required
$arr_q = explode(' ', $string);
foreach ($arr_q as $key=>$word) {
$arr_q[$key] = " title LIKE '%".$word."%' "; 
}
$query = "SELECT * FROM post WHERE " . implode(' OR ', $arr_q) . " LIMIT 0,10";
//echo $query;
$sql = mysql_query($query); 
?>

Keep in mind this is a logical "OR" search which means it will return every entry whose contents includes each of the given words, a stopwords filter is always useful when doing this sort of search.

[max4]

Ah Alcoholico! You beat me to the punch. Thank you for your response :). I've done something very similar which is working:

$title = explode(' ',$_POST['keywords']);
foreach($title as $value)
{
$title = $title."OR title LIKE '%".$value."%' ";
}
$title = substr($title,8);

$description = explode(' ',$_POST['keywords']);
foreach($description as $value)
{
$description = $description."OR description LIKE '%".$value."%' ";
}
$description = substr($description,5);

$query = "SELECT * FROM post WHERE ".$title.$description;
echo "{$query}<br />";

$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  echo "{$row['title']}<br />";
}

Your method actually works better than mine because with mine I have to remove the previous OR; otherwise keep it where relevant. Are there any serious problems that immediately pop up when using either method across multiple columns?

[max4]

I've created a few functions that generate a query string. A sample string looks like this:

SELECT * FROM post WHERE title LIKE '%sample%' AND categoryID='1' OR title LIKE '%database%' AND categoryID='1' OR title LIKE '%search%' AND categoryID='1' OR description LIKE '%sample%' AND categoryID='1' OR description LIKE '%database%' AND categoryID='1' OR description LIKE '%search%' AND categoryID='1' ORDER BY 'submission_time' DESC LIMIT 0,10

I don't know how efficient this would be, and in fact, I believe a query like this might be slow to execute over a large dataset. Is there a better way to write a search query without using FULLTEXT (A lot of this scripting is beyond me and I've already created my entire MySQL database in INNODB)?