Geek asks and answers

MySQL Multiple Interests Problem

I have a database in which users contribute their interests. I want to find people with related interests.

Interest table structure

interestid | username | hobby | location | level | matchinginterestids

Let’s take two users to be simple.

  • A Joe user can have 10 different accounts.
  • A greg user can have 10 different accounts.

I want to make the following algorithm

  • Take Joe's 1 percent record and find suitable hobbies and locations from the interest database. Put any matching interest identifier in the match box. Then go to concert 2 record, etc.

I guess I need some kind of for loop that will go through all the joe intersts and then do an update every time it finds a match in the database of interests. Is this possible in MySQL?

Further example:

I'm Dan. I have 3 interests. Each interest consists of 3 items:

  • Dan cats, food, hair
  • Dan superlens, dna, microses
  • Dan movie, slow motion, fightscenes

Other people may have other interests.

Joe:

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

Mo

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

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

Here are your interests:

---

, dna,

3,2,1 .

php , , . , , , , 3 , 3 , 2 1.

+3
2

, , MySQL, , . :

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

, , Interests, UserInterests. , UserInterests , , :

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

, , , , .

+5

daniel, , .

TABLE User (id, username, location )
TABLE Interests(id, hobby )
TABLE UserInterest(userId, interestId, level )

users : id.
- , - .

Interest ,
(ehm , , )

, users interests .
, ? ... user intrests intrests people. , :

TABLE users (id, username, location, intrest1, intrest2, intrest3)

, , :

  • 3 .
  • , 2, 1
  • , .

bad

SELECT * FROM user
INNER JOIN interests ON (user.intrest1 = interests.id) or
                        (user.intrest2 = interests.id) or
                        (user.intrest3 = interests.id);

, .
, .

" "

- .
" " " ".

A: 1 userinterest user
B: 1 userinterest interests

SELECT * FROM user
INNER JOIN userInterest ON (user.id = userInterest.userID)  //many-to-1
INNER JOIN interest ON (interest.id = userInterest.InterestID); //many-to-1

?

  • .
  • , , - .

, php - . , SQL , :

, Moe.

, . .

, user "Moe", user, , not "Moe".
, .
userinterest, , user interests.

SELECT i_Moe.hobby FROM interests AS i_Moe
INNER JOIN userInterests as ui2 ON (ui2.InterestID = i_Moe.id)
INNER JOIN user AS u_Moe ON (u_Moe.id = ui2.UserID)
WHERE u_Moe.username = 'Moe';

Moe.

SELECT u_Others.username FROM interests AS i_Others
INNER JOIN userinterests AS ui1 ON (ui1.interestID = i_Others.id)
INNER JOIN user AS u_Others ON (ui1.user_id = u_Others.id)
/*up to this point this query is a list of all interests of all users*/

INNER JOIN Interests AS i_Moe ON (i_Moe.Hobby = i_Others.hobby)
/*Here we link Moe hobbies to other people hobbies*/

INNER JOIN userInterests as ui2 ON (ui2.InterestID = i_Moe.id)
INNER JOIN user AS u_Moe ON (u_Moe.id = ui2.UserID)
/*And using the link table we link Moe hobbies to Moe*/

WHERE u_Moe.username = 'Moe'
/*We limited user-u_moe to only 'Moe'*/

AND u_Others.username <> 'Moe';
/*and the rest to everybody except 'Moe'*/

INNER JOIN , , . , .

, Moe, U_others.
Moe, U_Moe.
Moe , i_Moe
, i_Others
i_Others u_Moe
U_others, , Moe's

, .

+4

More articles:


All Articles