exact words in a query

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
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

exact words in a query

Post by phpScott »

I currently have a table set up with a field for keywords extracted from some images, all the keywords are stored in a signle field called keywords (suprise).

Now I need to search through that field looking for exact words that a client will be able to type in.
id

client types in mountian
I want to search through the keyword field to see if the word mountian is in there.

Any ideas on how to best approach this?

Or would it be better to store every keyword seperately with the picId attached?

phpScott
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

$result = strpos($keywords,$keyword);

If $result != "" then a keyword has been found.

Personally though I would put the keywords individually into the DB. But that is just me...
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Here's another vote for a keywords table -- ie picId, keyword -- one row per unique keyword/picID combo...

However if you don't do that you can also check if your database supports a "like" operator for full text search. In PostGreSQL, I would do

Code: Select all

SELECT "picId" FROM tablename WHERE keywords LIKE '%mountain%';
The %'s are a wildcard.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

thanks guys

Post by phpScott »

That's what I thought too but I'm sort of taking over this project so it is probally worth the effor to re-engineer it a bit.

phpScott
Post Reply