Page 1 of 1
search design
Posted: Mon Feb 09, 2004 8:14 pm
by bluesman333
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.
Posted: Mon Feb 09, 2004 8:21 pm
by penguinboy
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)';
Posted: Mon Feb 09, 2004 9:14 pm
by bluesman333
sorry not understanding
you said "....WHERE photo_id = 'some photo id';
where exactly does this value come from?
also,
i don't see how the other two tables relate to the third. they are not represented in the query.
Posted: Mon Feb 09, 2004 9:25 pm
by penguinboy
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']';
Posted: Tue Feb 10, 2004 6:37 pm
by bluesman333
still not understanding
i don't see how the three tables relate to each other.
AND,
couldn't i achieve the same thing using POST?
Posted: Tue Feb 10, 2004 7:57 pm
by McGruff
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.