Check for similar entries in DB

[therealwesfoster]

What is an efficient way to check a database for similar entries? For example:

 

One user posts "what is your favorite kind of dog?". So when user #2 comes along and posts "what is your most favorite breed of dog?", how can I alert user #2 of the similar entry already posted?

 

Digg has this feature as well when digging something. What's the most efficient way of doing this?

[abazoskib]

you can use the LIKE keyword in your queries. I would suggest using an index on the topics.

[therealwesfoster]

Thanks for the reply

 

LIKE would be good, but how would I implement that? Replace the spaces with %? That wouldn't work too well. What's you idea?

[micah1701]

I don't know the "best" way to do it -- but I would think it would involve keeping a seperate table of keywords then devising an algorithm to weight various words and phrases and make matches based on their score.

 

... or install a google box on your server http://www.google.com/enterprise/search/gsa.html

[abazoskib]

Thanks for the reply

 

LIKE would be good, but how would I implement that? Replace the spaces with %? That wouldn't work too well. What's you idea?

 

based on your first post i would create i string parser which removes words like "in" "the" "a" etc from topic titles. then i wouls create queries like this:

select * from topic_table where title like '%$parsedString%' limit 10;

 

you obviously will not want to list all the possible rows, so i limited it to 10.

[micah1701]

here's what'd i do though.

 

1) explode() the query string into individual words.

2) remove common words like ("the," "what," "when," "is," "a," etc...) from your array using preg_match or eregi

3) build a query that will check through your table for questions that contain each word: LIKE '% $word %'

4) ta-da - show the results to the user.

[therealwesfoster]

here's what'd i do though.

 

1) explode() the query string into individual words.

2) remove common words like ("the," "what," "when," "is," "a," etc...) from your array using preg_match or eregi

3) build a query that will check through your table for questions that contain each word: LIKE '% $word %'

4) ta-da - show the results to the user.

 

That's definitely a start (and the reason I originally posted this in the php section...). But that would also bring up questions like "how bad do you hate dogs?" and "dogs or cats?". So there are still a few more functions that need to take place, and this is the tricky part

[abazoskib]

here's what'd i do though.

 

1) explode() the query string into individual words.

2) remove common words like ("the," "what," "when," "is," "a," etc...) from your array using preg_match or eregi

3) build a query that will check through your table for questions that contain each word: LIKE '% $word %'

4) ta-da - show the results to the user.

 

didnt i say that?