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:

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.

bretticus

The easiest way to combine the queries would be to use GROUP_CONCAT.

$sql = "SELECT `user_id` , GROUP_CONCAT( `interests` )
FROM `table`
WHERE (
`user_id` =1
)
OR (
`user_id` =2
)
GROUP BY `user_id`";

Using this query, produces:

1 cats,dogs,cat,fish
2 dogs,fish,birds

dxdolar

I appreciate that code above, but I may not have been clear at what I was trying to return.

Since user 1 and user 2 have their own separate interests, I want to allow User 1 to click on User 2 and only see where they have shared interests. The return would be Dogs and Fish and the function would not return cats and birds since they are not shared.

hopefully that made my request a little more precise 😛

bretticus

I understood for the most part. I just didn't give you all the code (remember you asked about making just one query.) I try to give a hint and let ppl figure it out on your their own. You learn more in the long run. Besides, no offense, I do not like writing other peoples code for them. I prefer to fix it when they are experiencing problems. Don't get me wrong, I don't think I am being exploited here (you, at least, posted code in your original post after all.) However, you just assumed I didn't understand what you were after. And thus it seems like you didn't take much effort to even consider that what I gave you is a step in the right direction (when it was.) Okay, off my soapbox...

With that query you will have exactly 2 rows each time, one column will contain the user id and the other will contain a comma delimited list of all of that persons interests. If you split the interests lists into arrays, you can use array_intersect to find the common interests. You know both user ids. The person who is logged in has to maintain state somehow, so you can use a session variable or a cookie variable to know the first user id. You know the second because that person was clicked on. In any case, here's some untested code that should do nicely as an example.

<?php
$user_id1 = 1;
$user_id2 = 2;

$q = "SELECT `user_id` , GROUP_CONCAT( DISTINCT `interests` ) AS 'interests'
FROM `table`
WHERE (
`user_id` = $user_id1
)
OR (
`user_id` = $user_id2
)
GROUP BY `user_id`";

$rows = array();
$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
	$rows[$row['user_id']] = explode(",", $row['interests']);
}

$commons = array_intersect($rows[$user_id1], $rows[$user_id2]);
print_r($commons);
?>

There may be a way to use join syntax to just return the common interests from the query (probably a variation of the query I gave you earlier.) However, where this example is using just two records, it's not a tremendous amount of overhead.

Good luck!

dxdolar

Thank you so much! I completely agree with the sentiment about not writing other people's code. and I am very very thankful with you sharing your code with me.

I should have posted more of my full attempt at addressing the issue (which wasn't working). So I hope it didn't look like I wasn't trying to figure it out on my own 😛

Reading through the PHP manual for the right functions to accomplish certain things is something I've been trying to do more of recently, my instructional book has a limited number of functions it covers.

Thanks again, very very much!

tave

Here are my two cents. Like bretticus said, you can use join to achieve what you want.

<?php
$user_id1 = 1;
$user_id2 = 2; 


$q = "SELECT interest FROM interests as a INNER JOIN interests as b 

USING(interest) WHERE (a.user_id ='$user_id1' AND b.user_id='$user_id2') GROUP BY interest";

r$ = mysqli_query($dbc,$q);

echo "Common interest between $user_id1 and $user_id2";

while ($row = mysqli_fetch_row($r) ){
   echo "$row[0]";
}


?>

Hope that helps.