searching an SQL table using keywords

Broadbandplacey · 28 Mar 2007 at 21:54

evenin all, hope someone can help me here,

im trying to search a field 'keywords' in a table called 'photos'

now the keywords field will have things like; "Landscape, water, beach, lighthouse" - but i just cant find an SQL query that will work when i search for the term 'landscape' and bring all results with the keyword landscape.

any pointers? cheers

Dj_Jestar · 28 Mar 2007 at 21:56

Code:

SELECT * FROM `table` WHERE `keywords` LIKE '%landscape%'

Also look into converting to a FULLTEXT column, a lot more efficient.

Broadbandplacey · 28 Mar 2007 at 22:44

how do the percentage marks difer from just searching 'landscapes' or is it just the way its done

Broadbandplacey · 28 Mar 2007 at 23:12

thank you muchly that works for what i need

how about searching more than one field? it doesnt seem to work on my description field which is not neat and ordered with comma's but a description about the photo?

SELECT *
FROM `photos`
WHERE `keywords` LIKE '$searchvalue'
AND `description` LIKE '$searchvalue'
ORDER BY `photos`.`ID` DESC
LIMIT 0 , 4

Dj_Jestar · 28 Mar 2007 at 23:25

% is the wildcard. It's also case sensitive (90% sure it is.)

JIMA · 28 Mar 2007 at 23:42

Hi,

Another approach would be to build up a table of all your keywords, give them all a numeric index, and then record the values of this index against your photos table. As each photo can have one or more keywords associated with it you'd have a table to resolve this many-to-many join such as:

photokeywordtable
photoid
keywordid

You might want to be careful using wildcards in your where clause (with no other exact key specified) as it could well cause no primary key/index to be used, resulting in the entire table being searched every time you run the query (probably different for different DBs). If your tables aren't going to be big then it probably won't be a problem though.

Using the "keywords" table would ensure keys would be used as you'd always be matching on an exact integer value. The integer value method would also allow you to solve any case-sensitivity issues, converting each keyword to either upper/lower case before doing your matches.

The keywords table would also be useful if you want to categorize your photos in your application. For example, you could easily get all the photos that matched against the keyword "landscape" by looking on the photokeyword table.

Finally, if you're using MySQL I believe (though I'm not 100% sure) you can incorporate regular expressions into Where clauses which might also be of use.

Hope that's of some help.

Jim

Broadbandplacey · 29 Mar 2007 at 00:30

thanks all,

i've been looking into the full text searching but havent had much look so far, i'll look into creating key word tables, lots and lots to think about

again cheers all