SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to compare two mysql tables in php

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


    PHP Code:
    $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
    Code:
    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
    PHP Code:
    $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
    Code:
    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.
    PHP Code:
    $matcharray_intersect($array1$array2);
    print_r($match); 
    The result will is
    Code:
    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:
    Code:
     Snatch , The social network movie , once upon a time in the west

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You would be better performing this filtering in MySQL. My SQL isn't the best, but assuming each user can only add the same movie name once, this may do it:-
    Code MySQL:
    SELECT
        name
      , COUNT(name) AS occurrences
    FROM
      movie
    WHERE
      user_id IN (1, 2)
    GROUP BY
      name HAVING(occurrences) > 1
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    HAVING(occurrences) > 1 should be HAVING occurrences > 1

    the (unnecessary (proliferation) of (parentheses) is unnecessary)

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ha, thanks Rudy.

    I seem to remember you chastising me for doing the same with DISTINCT a year or so ago.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •