Comparing one column of db table against itself

[franzy_pan]

Hi,

 

I'm not too sure if this belongs in MySQL or PHP (depends on how it is to be accomplished).

 

What i need to be able to do is pull a field out of a database table and search against it to find like entires within itself ... say smoking and smokig (these are search terms generated by users so are likely to have typos hence why i need to be able to filter out like terms) once i have filtered out all the *duplicates* i then need to get it to show the occurence (amount of times it was used as a search) which should be relatively easy.

 

I'm just not too sure as to how to go about the initial search with the db table column comparing itself to itself.

 

Any ideas would be greatfully recieved - also if you need a better explanation let me know (its kinda hard to explain).

 

Thank you in advance 

 

 

[franzy_pan]

Put a better way is there a way for comparing mysql query output against itself (SELECT search_term FROM searches) ... checking search_term for like results, either through mysql or php?

 

i just need to be able to filter out results that are obviously very similar and therefore cut down the amount output when used for data processing by another system.

 

i'm not too sure how to go about starting this.

 

again any help would be great.

 

Thanks

[fenway]

I'm not sure what you mean by "against itself".  You can use a derived table and join it back to the orignal table... but you still have to decide how to programmtically describe "obviously very similar".

[franzy_pan]

There will be a column of data (i.e. one field) in the database table, in this case search_terms ... i need to be able to find all the like terms within it to filter out the very similar to give one set of distinct search terms used.

 

I didn't explain it very well, but i need to find someway of limiting the results from that colum due to things like typos etc.

[fenway]

I'm sorry, I still don't understand... if I make a typo. don't you want to include more results, not less?

[franzy_pan]

Ok, i'm going for a slightly different approach to this and require a little help with my SQL.

 

Is there any way to get a limited number of results with a SELECT DISTINCT on only one field but using several in the query.  i.e. i need to be able to pull distinct search_terms out of the database only with several other fields like the user id, date and site.

 

Any help will be gratefully recieved.

 

Thanks

[Illusion]

You mean

 

SELECT search_term,COUNT(*) as no_of_similar_words FROM searches WHERE search_term LIKE 'searchterm' GROUP BY search_term;

[Barand]

Data

   term
+---------+
' smoking '
' smokig  '
' boots   '
' botts   '
' widgets '
' widegts '

 

Query

SELECT a.term, b.term FROM item a
INNER JOIN item b
ON SOUNDEX(a.term) = SOUNDEX(b.term)
WHERE a.term > b.term

 

Results

   a.term      b.term
+-----------+----------+
'  botts    ' boots    '
'  widgets  ' widegts  '

 

Unfortunately it pulled all but your smoking/smokig example

[fenway]

They added a SOUNDS LIKE that's the equivalent but reads better.