Find and store DB relationships in MySQL / PHP

Question

Scenario: many to many relationships.

table has 2 columns: UserID and UserChoiche

I am storing the user preferences one by one in each row. (Is this the most efficient way?)

Example:

UserID         UserChoiche

mark anna
mark paul
mark john
john mark

In this example mark chose 3 users, and john chose 1 user.

John and Mark have chosen each other and I need php to find this db relationship.

//php mysql query that selects all the choiches of a specified user (in this case mark)
SELECT UserChoiche from exampletable WHERE UserID mark

//find the matches
??
here is my problem: I am looking for the easiest and most efficent way to handle this!

Maybe like this? (It could work but I cannot write a mysql statement, is too complicated for me!)
If Userchoice of previous query (in this case it would return anna paul and john) EQUALS any Userid in the table (in this case john) AND that Userid has a Userchoiche with the value of the UserID from the previous query (in this example 4th row)
then we have a match

//if matches found, then take the UserID and UserChoiche and store them into variables which I will use to make some notifications (there can be many matches, so maybe use an array?)

It sounds like you're looking for a self join. Take a look at this question: http://stackoverflow.com/questions/1284441/how-does-a-mysql-self-join-work — Casey Kinsey, Feb 20 '12 at 22:23

score 5 · Accepted Answer · answered Feb 20 '12 at 22:20

5

SELECT t1.UserID, t2.UserID
FROM table t1, table t2
WHERE t1.UserID = t2.UserChoiche
   AND t2.UserID = t1.UserChoiche

That should spit out the two that chose each other. We are basically joining the table to itself to find the mutual relationship

answered Feb 20 '12 at 22:20

Matt Dodge

10,833
7
38
58

Marcus Adams · Answer 2 · 2012-05-09T12:30:36.243

2

Here's the query to return users that match mark. Given the above data, it would return john:

SELECT p2.UserID
FROM preferences p1
JOIN preferences p2
ON p2.UserID = p1.UserChoice
AND p1.UserChoice = p2.UserID
WHERE p1.UserID = 'mark'

edited May 09 '12 at 12:30

answered Feb 20 '12 at 22:23

Marcus Adams

53,009
9
91
143

I like it and it seems to work (testing still in progress), but could you please clarify what p1 and p2 are? – Mark Belli Feb 20 '12 at 22:33
ok now I figured out everything and it works perfectly! thanks! – Mark Belli Feb 21 '12 at 01:48
`p1` and `p2` are [table aliases](http://dev.mysql.com/doc/refman/5.0/en/select.html). Another way to write the query is `FROM preferences AS p1`, which makes it more obvious that it's an alias. Aliases allowed me to reference the same table by two different, abbreviated, names. – Marcus Adams Feb 21 '12 at 14:04
WARNING: I changed my answer to mattedgod. Your answer seemed to work, but when I tested on a more complicated environment --> IT GAVE ME FALSE RESULTS. Maybe if you look at mattedgod's answer you can understand why yours is not completely working, honestly I have no clue why! Thanks anyway.. – Mark Belli May 09 '12 at 10:29
@MarkBelli, you're right. There was an error in my query. There was a missing criteria, which I added (see the AND part). This is basically now nearly identical to mattedgod's answer except I am using ANSI standard joins. – Marcus Adams May 09 '12 at 12:32

Find and store DB relationships in MySQL / PHP

2 Answers2