abuse search

an efficient way?

[bleak26]

i have 2 tables, one called abusive_terms and one called user_posts. User_posts contains many fields which i wish to search for the abusive terms. the problem is that i can either write a dynamic query in php which will add an extra
OR (user_posts.title LIKE '%$swear_word%') to my query
for every swear word in the abusive term table. The other option is to repeatedly search each record of user_posts as many times as there are swear words. neither option looks or feels efficient. can you recommend another more efficient method to do this.

thanks guys.

[vincevincevince]

I'd do it as the posts are entered. Make a new column 'abusive' and if there are abusive terms in the post, set that column to 1.

That way in future searches you just do "WHERE blah AND blah AND `abusive` "

To do what you are asking however, try this:

SELECT DISTINCT `user_posts`.`postid` FROM `abusive_terms`,`user_posts` WHERE `user_posts`.`title` LIKE CONCAT('%',`abusive_terms`.`swear_word`,'%')