I'm putting together a stock photo site with an image search.
The way I have it set up now is something like this:
$sql = "SELECT * FROM images WHERE column1 = '$_POST[keyword]' OR column2 = '$_POST[keyword]' OR description LIKE '%$_POST[keyword]%'";
column%'s are categories of the images such as photographer or medium
description is list of words describing the image
It seems to work ok now, but I'm wondering if there is a more advanced approach. Something that may work better with more keywords.
search design
Moderator: General Moderators
-
penguinboy
- Forum Contributor
- Posts: 171
- Joined: Thu Nov 07, 2002 11:25 am
IMO a better way to do it would be:
table1=picture
field=id
field=picture(image.gif or whatever)
field=discription
table2=keywords
field=id
field=name
table3=photo_keyword_relation
field=id
field=photo_id
field=keyword_id
Then you wouldn't limit your photos to only 2 keywords.
Your query would be
SELECT `photo_id` FROM `photo_keyword_relation` WHERE `keyword_id`='(some keyword id)';
table1=picture
field=id
field=picture(image.gif or whatever)
field=discription
table2=keywords
field=id
field=name
table3=photo_keyword_relation
field=id
field=photo_id
field=keyword_id
Then you wouldn't limit your photos to only 2 keywords.
Your query would be
SELECT `photo_id` FROM `photo_keyword_relation` WHERE `keyword_id`='(some keyword id)';
Last edited by penguinboy on Mon Feb 09, 2004 9:26 pm, edited 1 time in total.
- bluesman333
- Forum Commoner
- Posts: 52
- Joined: Wed Dec 31, 2003 9:47 am
-
penguinboy
- Forum Contributor
- Posts: 171
- Joined: Thu Nov 07, 2002 11:25 am
woops, edited it, had a brain fart
basically how it should work is
you have a page that lists the keywords
with urls like
{url=search_page.php?keyword=$keyword_id}Dogs{/url}
{url=search_page.php?keyword=$keyword_id}People{/url}
{url=search_page.php?keyword=$keyword_id}Cars{/url}
and then you search by
SELECT `photo_id` FROM `photo_keyword_relation` WHERE `keyword_id`='$_GET['keyword']';
basically how it should work is
you have a page that lists the keywords
with urls like
{url=search_page.php?keyword=$keyword_id}Dogs{/url}
{url=search_page.php?keyword=$keyword_id}People{/url}
{url=search_page.php?keyword=$keyword_id}Cars{/url}
and then you search by
SELECT `photo_id` FROM `photo_keyword_relation` WHERE `keyword_id`='$_GET['keyword']';
- bluesman333
- Forum Commoner
- Posts: 52
- Joined: Wed Dec 31, 2003 9:47 am
First, I think it might be useful to review the db design (it's not properly normalised). Image categories and photographers should probably have their own tables.
See this tutorial.
If you are using mysql 4.01+, the support for MATCH boolean options could be useful - see [mysql_man]match[/mysql_man].
Also check out the RLIKE in the manual. LIKE %'" . $string . "'% will return suffixes and prefixes (such as buffy and rebuffed if you search for buff) but " RLIKE '[[:<:]]" . $string . "[[:>:]]'" will not.
See this tutorial.
If you are using mysql 4.01+, the support for MATCH boolean options could be useful - see [mysql_man]match[/mysql_man].
Also check out the RLIKE in the manual. LIKE %'" . $string . "'% will return suffixes and prefixes (such as buffy and rebuffed if you search for buff) but " RLIKE '[[:<:]]" . $string . "[[:>:]]'" will not.