Databases
StackOverflow2013
Postsdbo
POHow to find similarity between mySQL rows?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to find similarity between mySQL rows?
    primarykey
    data
    text
    <p>I am trying to create a script that finds a matching percentage between my table rows. For example my mySQL database in the table <em>products</em> contains the field <em>name</em> (indexed, FULLTEXT) with values like</p> <pre><code>LG 50PK350 PLASMA TV 50" Plasma TV Full HD 600Hz LG TV 50PK350 PLASMA 50" LG S24AW 24000 BTU Aircondition LG S24AW 24000 BTU Inverter </code></pre> <p>As you may see all of them have some same keyword. But the 1st name and 2nd name are more similar. Additionally, 3rd and 4th have more similar keywords between them than 1st and 2nd.</p> <p>My mySQL DB has thousands of product names. What I want is to find those names that have more than a percentage (let's say 60%) of similarity.</p> <p>For example, as I said, 1st, 2nd (and any other name) that match between them with more than 60%, will be echoed in a group-style-format to let me know that those products are similar. 3rd and 4th and any other with more than 60% matching will be echoed after in another group, telling me that those products match.</p> <p>If it is possible, it would be great to echo the keywords that satisfy all the grouped matching names. For example <em>LG S24AW 24000 BTU</em> is the keyword that is contained in 3rd and 4th name.</p> <p>At the end I will create a list of all those keywords.</p> <p>What I have now is the following query (as Jitamaro suggested)</p> <pre><code>Select t1.name, t2.name From products t1, products t2 </code></pre> <p>that creates a new name field next to all other names. Excuse me that I don't know how to explain it right but this is what it does: (The real values are product names like above)</p> <p>Before the query</p> <pre><code>-name- A B C D E </code></pre> <p>After the query</p> <pre><code>-name- -name- A A B A C A D A E A A B B B C B D B E B . . . </code></pre> <p>Is there a way either with mySQL or PHP that will find me the matching names and extract the keywords as I described above? Please share code examples.</p> <p>Thank you community.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USEnexoOnoma
    1. USEnexoOnoma
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to find similarity between mySQL rows?
      1. USianbarker
      1. VTFavorite
    2. VO
      singulars
      1. POHow to find similarity between mySQL rows?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to find similarity between mySQL rows?
      1. USEnexoOnoma
      1. VTBountyStart
    1. COI don't think you will get the code here. You will get an algorithm. It's not us making your homework.
      singulars
      1. POHow to find similarity between mySQL rows?
      1. USBytemain
    2. COI know the rules thank you.
      singulars
      1. POHow to find similarity between mySQL rows?
      1. USEnexoOnoma
    3. COupto how many different shops you have selling same product and do all (or most) of the product names contain model number like `50PK350`?
      singulars
      1. POHow to find similarity between mySQL rows?
      1. USImre L
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload