Page 1 of 1

Keyword Search

Posted: Tue Jul 04, 2006 6:09 pm
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]

Posted: Wed Jul 05, 2006 6:11 pm
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.

Posted: Sun Jul 09, 2006 1:23 am
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?

Posted: Sun Jul 09, 2006 4:26 am
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')