keyword searching

Posted on 2005-04-06
Medium Priority
Last Modified: 2008-02-20
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?
Question by:qwertq
  • 5
  • 4

Expert Comment

ID: 13717054
add spaces (or any other separator) before and after $keyword
like '%_$keyword_%'

also, make sure that first and last character in keywords column is space character:


However, Maybe you should consider creating an index of keywords as separate table. It will be much faster than LIKE statement.

Author Comment

ID: 13717066
what do you mean an index?

Accepted Solution

peyox earned 200 total points
ID: 13717172
I mean - table which stores all keywords:

KeyIndx table:
recordId - record id from bookmarks table
keyword - keyword assigned to the record

Sample table
recordId, keyword
1            apple
1            banana
3            computer
3            apple
4            monitor

Sample search:
select recordId from KeyIndx where keyword='apple'

This query will return records: 1,3 (matching your keyword)

Since you are searching for whole words this would be better approach than LIKE.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 13717227
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?

Expert Comment

ID: 13717650
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.

Author Comment

ID: 13717785
even if there would be the same keyword in there multiple times with different recordId numbers?

Expert Comment

ID: 13717938
yes, you can still do fast search on this. give it a try.

Author Comment

ID: 13718019
alright, let me try it

Author Comment

ID: 13771950

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.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question