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
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]