Double content check

Discussion in 'Databases' started by Fracisc, Dec 29, 2008.

  1. #1
    Hello

    How can I check if in my IP table two or more IP addresses are the same?
     
    Fracisc, Dec 29, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Use a query like this:

    SELECT ip_address, count(*) AS n
    FROM my_table
    GROUP_BY ip_address
    HAVING n > 1
     
    jestep, Dec 29, 2008 IP
  3. Fracisc

    Fracisc Well-Known Member

    Messages:
    3,670
    Likes Received:
    10
    Best Answers:
    1
    Trophy Points:
    195
    #3
    Doesn't seem to work... I have it like this:

    $error = mysql_query("SELECT ip, count(*) AS n FROM results GROUP_BY ip HAVING n > 1");
    echo $error;
    Code (markup):
     
    Fracisc, Dec 29, 2008 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    Try:

    
    
    $error = mysql_query("SELECT ip, count(*) AS n FROM results GROUP_BY ip HAVING n > 1");
    
    while($array = mysql_fetch_assoc($error)):
    
    echo $array['ip'];
    
    endwhile;
    
    
    PHP:
     
    jestep, Dec 29, 2008 IP
  5. Fracisc

    Fracisc Well-Known Member

    Messages:
    3,670
    Likes Received:
    10
    Best Answers:
    1
    Trophy Points:
    195
    #5
    I managed to do it like this:
    $double_ips = mysql_query('SELECT * FROM `results` where `ip` having COUNT(*) >= 2');
    Code (markup):
    But that will return only the IP. I need all the results with double IPs.

    So, if we have ip1 and ip2 as double ips, I need to return like this:

    name1 ip1
    name2 ip1
    namen ip1

    name1 ip2
    name2 ip2
    namen ip2
     
    Fracisc, Dec 29, 2008 IP
  6. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #6
    For that you will need to use a double query.

    
    
    $double_ips = mysql_query('
    SELECT * FROM `results` WHERE `ip` IN (
    SELECT `ip` FROM `results` WHERE `ip` HAVING COUNT(*) > 1)
    ');
    
    
    PHP:
     
    jestep, Dec 29, 2008 IP