Select from table where columns are similar

[franwpd]

I have a table with a column that has a filename and 7 columns which contain keywords. I would like to compare all 7 columns and get a list of the filenames that have similar keywords. Then I would like to select 12 random filenames. Can this be done and how?

[johanafm]

Normalize the db schema or you'll be going through hell. If I input 3 keywords to search for, you'd need to write 21 comparisons in the where clause...

table file
id
filename
...

table keyword
id
keyword

table file_has_keyword
file_id
keyword_id

SELECT filename
FROM keyword AS k
INNER JOIN file_has_keyword AS fhk ON k.id = keyword_id
INNER JOIN file AS f ON f.id = fhk.file_id
WHERE keyword = 'someword'

For limited result sets, you could pull all the data, then use generate 12 random numbers between 0 and num_result and use data_seek to retrieve each of those records.

But you might want to have a look at http://phpbuilder.com/board/showthread.php?t=10338930 and http://www.phpbuilder.com/board/showthread.php?t=10365821