I was thinking about this. Bitfields would be nice, but to do it decently would require doing it in the database. Since I don't know MySQL's language for stored procedures (or even how many languages it has) - and doing it in the query would be simply hideous - it would have to be done in PHP; but then all rows would have to be returned from the query, and a bitfield constructed from each one for comparison. And the work involved in that would be better spent actually making the comparison.
I'm going to assume you have an array containing the ID numbers of the user-selected videos, and will call this array $selected_videos (comparing ID numbers is a lot easier than comparing strings).
Fortunately, to do the comparison, the link table contains all the information we need, i.e., who has what. Because the filtering is going to be done in PHP, there is nothing we can do in the query to identify any rows in particular. What would be nice is if the data were structured in an appropriate fashion: an array indexed by user ID in which each element is an array of the video IDs selected by that user.
$who_has_what_query = 'Select user_id,video_id from user_video_link';
$who_has_what_resultset = mysql_query($who_has_what_query);
$who_has_what = array();
while(list($user_id,$video_id) = mysql_fetch_row($who_has_what_resultset))
{
$who_has_what[$user_id][] = $video_id;
}
Right, that's done. Now we want those $who_has_what entries that contain exactly the same elements as $selected_videos. If $listA and $listB have "exactly the same elements", then neither of them contain elements that the other one doesnt; if you take all the elements that are in $listB out of $listA you're left with an empty list, and if you take all the elements that are in $listA out of $listB you're again left with an empty list. A function that makes this test:
function contains_exactly_the_same_elements($listA, $listB)
{
if(count(array_diff($listA, $listB))!=0) return false;
if(count(array_diff($listB, $listA))!=0) return false;
return true;
}
Now to filter $who_has_what by this criterion (on a techincal note, decent closures would be nice about now; I'd use array_filter, but the callback doesn't take additional arguments).
$users_with_same_selection = array();
foreach($who_has_what as $user=>$videos)
{
if(contains_exactly_the_same_elements($selected_videos, $videos))
$users_with_same_selection[] = $user;
}
As well as filtering, we kept in mind the fact that all we're really interested in is the list of users with matching selections. Which got put in $users_with_same_selection.
