search a database

Anon

Hello.

I have a table in a databse that holds html tutorials. Each row has a collum called "related" which holds a list of "key words" that describe the partucular tutorial. values in the "related" feild are sepperated by commas. I'm writing a search engine to search that feild. Below is the code i use to search the table:

$query="SELECT id, title, abstract FROM $table WHERE related LIKE '%$search%'";

($search being the search the user typed in)

The problem comes in when the user tries to type in more than 1 word to the search engine. It dosen't come up with any results. Any ideas?

Thanks in advance.

shivers

This is something people get confused on a lot.

What sort of multiple word searching are you after? AND (all words required), OR (any word required), or some sort of qualitative matching scheme with confidence values?

For the first two, you can get around the problem to a degree by dynamically building your SQL query depending on how many keyowrds were specified by the user. Example pseudocode below:

$query = "SELECT blah FROM blahtable WHERE";
$query .= "related LIKE '%$word%'";
loop through keywords
$query .= " AND related LIKE '%$key%'";
end loop

very unclear code I know but you get the picture. It shows an AND match - just substitute the AND for OR to get 'any word' style matching.

Qualitative matching is a different kettle of fish altogether - you'd probably need to write an extension to your database server for that...

Anon

umm......sorry, i don't follow....Could you clairfy? thanks.

shivers

Sorry, I meant...

..when your users put more than one word into the search field, how do you want the engine to behave?

Do you want it to onlny return results where ALL of the words the user specified appear in the result? Or where ANY of the words appear in the result?

Or do you want to do more complex matching where you calculcate confidence values based on the frequency the words match and other things?

(ALL = AND in my previous post)

Anon

where ANY of the words occour in the result feild.

shivers

lovely, in that case use OR instead of AND in the dynamically generated SQL in my first post.

The idea of it is that your code loops through all the keywords supplied by your user and adds an extra "OR (field LIKE '%keyword%)" clause for each one.

That should do the job fine 🙂

Anon

So: if $search is the thing the user typed in...

would that be on the right track?

$key = explode(" ", $search);

$query="SELECT id, title, abstract FROM $table WHERE related LIKE '$search'";

while ($key[$i]) {
$query .=" OR related LIKE '%$key[$i]%'";

$i++
}

shivers

yeah that looks pretty good. don't forget to check for leading/trailing whitespace (kill it with trim()) and for multiple spaces between words (damn inconsiderate users... ..manual checks for empty array entries required)

oh, replace the $search part in $query with the first element in the array $key - and remember to put the % signs around it

good luck 😉

Anon

thank you SO much for all your help! It works extremely well. I'm gratefull. 🙂

shivers

no problem, glad I could help 🙂

[deleted]

Actually, getting qualitative matching is quite easy, there is even an article here that does it (is you tweak it a little), called "slapping together a search engine"

shivers

Oh yeah - I'd forgotten I'd read that one.

It is a quite nice way of doing it - I'd just discounted it as I tend to go completely off on one with free text searching - affecting confidence by frequency of words, closeness to phrase match, synonyms, etc.. (it was my dissertation, what can I say...)