MySQL Multiple interests matching problem

Question

I have a database where users enter their interests. I want to find people with matching interests.

The structure of the interest table is

interestid | username | hobby | location | level | matchinginterestids

Let's take two users to keep it simple.

• User Joe may have 10 different interest records
• User greg may have 10 different interest records.

I want to do the following algorithm

• Take Joe's interest record 1 and look for matching hobbies and locations from the interest database. Put any matching interest id's in the matches field. Then go to joe's interest record 2 etc..

I guess what I need is some sort of for loop that will loop through all of joe's intersts and then do an update each time it finds a match in the interest database. Is that even possible in MySQL?

---

Further example:

I am Dan. I have 3 interests. Each interest is composed of 3 subjects:

• Dan cats,nutrition,hair
• Dan superlens,dna,microscopes
• Dan film,slowmotion,fightscenes

Other people may have other interests

Joe:

• Joe cats,nutrition,strength
• Joe superlens,dna,microscopes

Moe

• Moe mysql,queries,php
• Moe film,specialfx,cameras
• Moe superlens,dna,microscopes

Now I want the query to return the following when I log in as Dan:

Here are your interest matches:

--- is interested in cats nutrition hair
Joe is interested in cats and nutrition
Joe and Moe are interested in superlens, dna, microscopes
Moe is interested in film

The query needs to iterate through all Dan's interests, and compare 3,2,1 subject matches.

I could do this in php from a loop but it would be calling the database all the time to get the results. I was wondering if there's a crafty way to do it using a single query Or maybe 3 separate queries one looking for 3 matches, one for 2 and one for 1.

Answer 1

This is definitely possible with MySQL, but I think you may be going about it in an awkward way. I would begin by structuring the tables as follows:

TABLE Users ( userId, username, location )
TABLE Interests( interestId, hobby )
TABLE UserInterests( userId, interestId, level )

When a user adds an interest, if it hasn't been added before, you add it to the Interests table, and then add it to the UserInterests table. When you want to check for other nearby folks with similar interests, you can simply query the UserInterests table for other people who have similar interests, which has all that information for you already:

SELECT DISTINCT userId
  FROM UserInterests
  WHERE interestId IN (
     SELECT interestId
       FROM UserInterests
       WHERE userId = $JoesID
     )

This can probably be done in a more elegant fashion without subqueries, but it's what I thought of now.