How to compare two mysql tables in php

[semanticnotion]

i have a movie table and i want to compare the common movies of two users.

$array1=array();
$array2=array();
$query2="select name from movie where user_id='1'";
$result2=mysql_query($query2) or die(mysql_error());
while($rss = mysql_fetch_assoc ($result2))
{
    $array1[]=$rss;
}
print_r($array1);
 

This will print

Array ( [0] => Array ( [name] => Snatch ) [1] => Array ( [name] => The Social Network Movie )<br />[2] => Array ( [name] => Death Note ) [3] => Array ( [name] => Titanic ) <br />[4] => Array ( [name] => Once Upon a Time in the West ) ) 

And for second user

$query3="select name from movie where user_id=1";
$result3=  mysql_query($query3) or die(mysql_error());
while($rss1=  mysql_fetch_assoc($result3))
{
    $array2[]=$rss1;
}
print_r($array2);

This will print

Array ( [0] => Array ( [name] => The Lord of the Rings Trilogy ) [1] => Array ( [name] => Snatch ) <br />[2] => Array ( [name] => The Social Network Movie ) [3] => Array ( [name] => Scarface ) <br />[4] => Array ( [name] => Once Upon a Time in the West ) [5] => Array ( [name] => Legend of the Guardians: The Owls of Ga'Hoole ) [6] => Array ( [name] => Once Upon a Time in America ) <br />[7] => Array ( [name] => Butch Cassidy and the Sundance Kid ) [8] => Array ( [name] => Fracture ) <br />[9] => Array ( [name] => Invictus ) [10] => Array ( [name] => Pride and Glory ) [11] => Array ( [name] => Casablanca ) ) 

When i compare these two arrays it gives me the first array.

$match= array_intersect($array1, $array2);
print_r($match);

The result will is

Array ( [0] => Array ( [name] => Snatch ) [1] => Array ( [name] => The Social Network Movie )<br />[2] => Array ( [name] => Death Note ) [3] => Array ( [name] => Titanic ) <br />[4] => Array ( [name] => Once Upon a Time in the West ) ) 

But the Common movies are:

 Snatch , The social network movie , once upon a time in the west

[code green]

array_intersect() can only return values present in $array1.
Probably easiest thing to do is merge the two and compare.

array_intersect(array_merge($array1,$array2),$array1,$array2); 

[dgreenhouse]

Other than the fact that your tables should be normalized, you can just do a self-join on the table and get the results in one query.

Haven't tested, but I believe this should work:

select a.name from movie a
join movie b on b.name = a.name and b.user_id = 2
where a.user_id = 1

The above is one of a few was of creating the query and any common movies between users should be in the result set.