Need help makig my search function less specific

[moleculo]

Hello. I've created a search function on my website that works by posting a "keyword" to a page that checks if the keyword matches similar keywords in my entire MySQL database of products.

 

The key part of that code in that page is:

$query = "SELECT category, subcategory, title, itemno, descrip
	from products WHERE descrip LIKE '%$keyword%' OR itemno LIKE '%$keyword%' OR title LIKE '%$keyword%' OR category LIKE '%$keyword%' OR subcategory LIKE '%$keyword%' ORDER BY no";

 

The problem I'm having is if someone enters a 2-word keyword, it searches for those 2 specific words together in that order. I would like to have search for the 2 words seperately.

 

I'm not sure if I explained that clear enough so here's an example:

 

If in the field "category," I have "Cytokeratin ELISA Kit".... When someone searches for the words "cytokeratin kit", it doesn't return any results because it reads "cytokeratin kit" as the entire keyword.

 

Anyone have any ideas?

[fnairb]

For part of your problem lower() is your friend.  The values are case sensitive, as you have found out, so just convert everything to lower case on your searches.  For example:

 

... WHERE lower(descrip) LIKE lower('%$keyword%')

 

I do need a little clarification on the multi-word search.  Do you want a match only where ALL words are present or ANY of the words are present?

[cooldude832]

what you can do is split the keyword phrase up into individual words chunk out the junk  (A, I, or, and, but, etc.)  then run a query that returns the results in order of the most number of matches.  It is a conditional query that is slightly complicated, but you can probably return a %realvence similar to wikipedia's

[moleculo]

For part of your problem lower() is your friend.  The values are case sensitive, as you have found out, so just convert everything to lower case on your searches.  For example:

 

... WHERE lower(descrip) LIKE lower('%$keyword%')

 

I do need a little clarification on the multi-word search.  Do you want a match only where ALL words are present or ANY of the words are present?

 

Thanks for the tip about "lower()".

 

For the 2nd part, I want a match where ALL words are present, but not necessarily present next to each other in the field. For example, if a field has the letters A B C in that order, if someone searches for A and C in the same query, the search should return the field A B C.

[moleculo]

what you can do is split the keyword phrase up into individual words chunk out the junk  (A, I, or, and, but, etc.)  then run a query that returns the results in order of the most number of matches.  It is a conditional query that is slightly complicated, but you can probably return a %realvence similar to wikipedia's

 

Ok, thanks. I'm not sure how to do that but I'll look into it.

 

Is that the easiest way that you know of?

[fnairb]

Since you want them all one approach would be to simply replace spaces with '% %'. I know that is hard to read so let m demonstrate using your earlier example.

 

Given: $keyword = 'Cytokeratin ELISA Kit'

your query would look for : '%Cytokeratin ELISA Kit%'

 

If you replaced ' ' with '% %' it would then look for: '%Cytokeratin% %ELISA% %Kit%'.

 

It may get you closer but there would still be an order dependency.  So it would match

'Cytokeratin ELISA Kit For dummies' but not 'Cytokeratin Kit For dummies  ELISA'

 

While you could use str_replace() I would recommend preg_replace() so you could colapse multiple spaces.  (If you want that explained just ask)

preg_replace() documentaiton: http://us.php.net/manual/en/function.preg-replace.php

 

$newKeyword = preg_replace('/\s+/', '% %', $keyword);

 

Then just replace $keyword in your query with $newKeyword