SQL/PHP: returning results where 2 people's fields match.

[dxdolar]

I'm trying to show only the fields where 2 users share a common interest.

 

let's say my database looks like this:

 

user_id | interest

  1  |  cats

  1  |  dogs

  1  |  fish

  2  |  dogs

  2  |  fish

  2  |  birds

 

I'm trying to return the interests where user 1 and user 2 match (dogs and fish) and then display them

 

I started writing out this :

 	$q = "SELECT interests FROM table WHERE user_id='$id1'";	
$q2 = "SELECT interests FROM table WHERE user_id='$id2'";
$r = mysqli_query ($dbc, $q); 
$r2 = mysqli_query ($dbc, $q2);

$row1 = mysqli_fetch_array ($q, MYSQLI_ASSOC);
$row2 = mysqli_fetch_array ($q, MYSQLI_ASSOC);

so I have 2 values of data, then I would compare them using a PHP function (that I don't know) and set that to a array and return the array? I just know that's SUPER inefficient and not elegant at all.

 

Is there a way to do this in a single MYSQL query? or can someone tell me the PHP function in order to compare the results of 2 queries? I know I could also use:

$q="SELECT interests FROM table WHERE user_id='1' AND user_id='2'; 

but I don't really know how I'd compare a single array of values against itself.

 

Thanks oh wise teachers of the web, any help would be appreciated.

[thepip3r]

I've thought about this for some time and there are ways to get the information by combining a SQL join and and some PHP loops with multi-dimensional arrays but I'm pretty sure that you CAN get what you're looking for in a single SQL join using the MATCH statement.  Without testing out different variants on a live database, I'm afraid me supplying that SQL statement would be horribly incorrect.  Although, here is an article talking about SQL Matches:

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

HTH

[bluejay002]

if what you are trying to fetch the common interests of different users and given that users are some anonymous, here's a simple work around:

 

SELECT t1.user_id as user1, t2.user_id as user2, interest
FROM table_name as t1
INNER JOIN table_name as t2 ON t1.interest = t2.interest AND t1.user_id <> t2.user_id

 

if user_id's are given, then just add them by inserting a WHERE clause.

 

cheers,

 

Jay