qwertq
asked on
keyword searching
i am wondering if there is a better way to be handeling keyword searching.... i have a string of space seperated keywords in a db field and i am using this query:
$query = "SELECT * FROM bookmarks WHERE keywords like '%$keywordLookUp%'ORDER BY added DESC";
problem is if i search for 'apple' and there is a keyword 'applepie' it would return this result.... how should i do it to fix this?
$query = "SELECT * FROM bookmarks WHERE keywords like '%$keywordLookUp%'ORDER BY added DESC";
problem is if i search for 'apple' and there is a keyword 'applepie' it would return this result.... how should i do it to fix this?
ASKER
what do you mean an index?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so for each keyword of each record i have a new row in the keyword table? it would be faster even thou there would be alot more rows?
Yes it would be faster, because you can have an index created on keyword column in KeyIndx table.
LIKE have to search thru all records (every time) in bookmarks table and look for matching keywords. This operation cannot use index (even if you have one) to seed up the search.
LIKE have to search thru all records (every time) in bookmarks table and look for matching keywords. This operation cannot use index (even if you have one) to seed up the search.
ASKER
even if there would be the same keyword in there multiple times with different recordId numbers?
yes, you can still do fast search on this. give it a try.
ASKER
alright, let me try it
ASKER
peyox
just one other related question if i may.
i went with having an index of keywords in a seperate table and tieing them together by the bookmarkID
here is the query i am using now:
SELECT DISTINCT * FROM bookmarks b, bookmarks_keywords k WHERE k.markID = b.markID AND k.keyword = '$keywordLookUp' GROUP BY b.markID ORDER BY added DESC
this works fine for doing one keyword. but what if i only want to show only ones that match TWO keywords? i can not seem to get it to work it will bring up records which have EITHER keyword, not both.
just one other related question if i may.
i went with having an index of keywords in a seperate table and tieing them together by the bookmarkID
here is the query i am using now:
SELECT DISTINCT * FROM bookmarks b, bookmarks_keywords k WHERE k.markID = b.markID AND k.keyword = '$keywordLookUp' GROUP BY b.markID ORDER BY added DESC
this works fine for doing one keyword. but what if i only want to show only ones that match TWO keywords? i can not seem to get it to work it will bring up records which have EITHER keyword, not both.
like '%_$keyword_%'
also, make sure that first and last character in keywords column is space character:
_key1_key2_key3_
However, Maybe you should consider creating an index of keywords as separate table. It will be much faster than LIKE statement.