Keyword Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GameMusic
Forum Newbie
Posts: 24
Joined: Fri Oct 28, 2005 8:33 pm

Keyword Search

Post by GameMusic »

Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I'm trying to make a keyword search system for images.  The concept is that each image can fit multiple keywords.

So here's my concept:

table keywords = keywordID, title
table keywordinstances = keywordID, picID
table pics = picID, picData

I can select by keyword like this:
[syntax="sql"]
SELECT p.* FROM pics AS p, keywordinstances AS w WHERE w.keywordID = 'key' AND p.picID = w.picID
But how would I select images that match a specific keyword but NOT another keyword? Say, matching 'foo' but not 'bar'.

I could use text fields to store lists of keyword IDs, which would get rid of the join, but it would use slower text search, and I'm not sure how much slower it would be, especially when searching a ton of keywords. The idea of a text field containing multiple IDs might also make it harder to use SQL variables as IDs, or to optimize with indexes. How would I search for keywords LIKE keywordtable.keywordID, for example?

table keywords = keywordID, title
table pics = picID, picData, keywords (,1,2,3,4,)

Code: Select all

SELECT p.* FROM pics AS p WHERE keywords LIKE '%4%'

Weirdan | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You could always use a joiner table (keywordID, picID) or you could index your search field to improve speed. There are a number of solutions available to you. It is just a matter of finding the one that is right for you.
GameMusic
Forum Newbie
Posts: 24
Joined: Fri Oct 28, 2005 8:33 pm

Post by GameMusic »

Ok searching on Google for joiner table I think I have the lingo. This is a many-to-many relationship. Does anybody know how to select objects that match multiple keywords (AND) and certain keywords and not others with a joiner table?
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Code: Select all

SELECT * FROM pics WHERE keywords LIKE '%foo%' AND keywords NOT LIKE '%bar%'
or ...

Code: Select all

SELECT * FROM pics WHERE keyword IN ('foo', 'john', 'jack') AND keyword NOT IN ('bar', 'jill')
Post Reply