search design

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
User avatar
bluesman333
Forum Commoner
Posts: 52
Joined: Wed Dec 31, 2003 9:47 am

search design

Post 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.
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

Post 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)';
Last edited by penguinboy on Mon Feb 09, 2004 9:26 pm, edited 1 time in total.
User avatar
bluesman333
Forum Commoner
Posts: 52
Joined: Wed Dec 31, 2003 9:47 am

Post 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.
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

Post 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']';
User avatar
bluesman333
Forum Commoner
Posts: 52
Joined: Wed Dec 31, 2003 9:47 am

Post 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?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
Post Reply