Web Analytics Made Easy -
StatCounter display list if more than once in database - CodingForum


No announcement yet.

display list if more than once in database

  • Filter
  • Time
  • Show
Clear All
new posts

  • display list if more than once in database

    i'm trying to create a script that - when a visitors visits the page- shows all duplicate entries of a certain field (field is called 'ip').
    The script should check the database (column = users) and should list all accounts a ip address has (if it has more than one).
    If there is only one account for an IP it shouldn't be shown. Only when multiple accounts are made by the same IP i would like it to be listed in a table like : [ip] [accountname1,accountname2,...]

    Does anybody know how to automaticly check for multiple accounts for ips?

  • #2
    If you are using MySQL (you didn't specify) and if the version you are using is 4.1 or greater, you can use a subselect like this:
    SELECT *
    FROM table
    WHERE id = (
    SELECT id
    FROM table
    GROUP BY ip
    HAVING count(*) > 1
    If you are using MySQL version less than 4.1, you'll have to split that up into two queries.


    • #3
      thank you for trying to help me but it gives me an error : syntax error
      don't know what to do as i neer used links that were like : BY ip HAVING...


      • #4
        SELECT * FROM users WHERE id = (SELECT id FROM users ORDER BY ip HAVING count(*) > 1)

        = what i'm using atm


        • #5
          tested some more and found it ! (well, almost !)

          NOW USE:
          PHP Code:
          $select="SELECT * FROM users WHERE id = (SELECT id FROM users GROUP BY ip HAVING count(*) > 1)";

          When i insert the query in PHPMYADMIN i get this error:
          #1242 - Subquery returns more than 1 row 


          • #6
            ok searched up the error and found that i have to put ANY before the brackets, did that, it know doesn't return me an error, but it doesn't do what i want it to do.

            CURRENTLY USING :
            PHP Code:
            SELECT *
            FROM users
            WHERE id 

            SELECT id
            FROM users
            GROUP BY ip
            HAVING count
            ( * ) >1
            ORDER BY `ipASC
            LIMIT 30 
            which is correct according to phpmyadmin

            The weird thing is that when i look at the result, i don't see any IP in the ip field twice, how come?


            • #7
              aha i see ! It gives me only 1 result but when you look up the IP in a query (like : where ip = IPHERE
              than it shows me all the accounts, so it is working because it is displaying one result of every IP that has multies.

              IS there any way to make the script print all accounts ?
              THANK YOU


              • #8
                Yeah that query I suggested is no good. With a GROUP BY you can only select the column you are grouping by and things like max(), min() and count() and get reliable results.

                You can do one query that selects all duplicate IPs and then do another query that works the results of the first query to dump account info, but I'm at a loss how to get it done in one query.

                PHP Code:
                $query "
                SELECT ip
                FROM table
                GROUP BY ip
                HAVING count(*) > 1
                $result mysql_query($query) or die(mysql_error());

                for (
                $i 0$i mysql_num_rows($result); $i++) {
                $dupIPArray[$i] = mysql_result($result0);

                Then the query to get account info:
                PHP Code:
                foreach($dupIPArray as $dupIP) {
                $query "SELECT * FROM table WHERE ip = $dupIP";
                $result mysql_query($query) or die(mysql_error());
                    for (
                $i 0$i mysql_num_rows($result); $i++) {
                $accountInfo[$i] = mysql_fetch_assoc($result);