MySQL select records, count duplicates

Question

I have a table of posts, each post has an IP address, I want to see how many times that IP has posted by counting how many times it occurs in the database, and then putting the number of times it's appeared on the screen. Basically this:

MySQL Table:

id entry     ip
1   abc  19.123.14.5
2   cde  19.123.14.5
3   efg  12.231.22.9

I want the code to take that, count duplicates, and ouput the count like so:

id entry     ip       count
1   abc  19.123.14.5    2
2   efg  12.231.22.9    1

Here's my code (that doesn't work) so far:

  $result = mysql_query("SELECT ip, entry, id, COUNT(ip) AS A FROM table_name AS C GROUP BY ip HAVING COUNT A > 1 ORDER BY id DESC");
    $i = 0;
    while($row = mysql_fetch_array($result)) {
        $id = $row['id'];
        $entry = $row['entry'];
        $ip = $row['ip'];
        $count = ?????;
        $i++;
    ?>

    <tr width="100%" align="center">
        <td><?php echo $i; ?></td>
        <td><?php echo $id; ?></td>
        <td><?php echo $entry; ?></td>
        <td><?php echo $ip; ?></td>
        <td><?php echo $count ?></td>
        <td>
        <form style="display:inline;" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
            <input type="hidden" value="<?php echo $ip; ?>" name="ip" />
            <input type="hidden" value="<?php echo $id; ?>" name="id" />
            <input type="submit" value="Ban IP" name="submit" />
        </form>
        </td>
    </tr>

    <?php
    }

Any help would be much appreciated!

EDIT: Doesn't work: Well firstly, as obvious from my code, the variable $count has nothing assigned to it as I have no idea what to put there. Secondly, I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Have done, see edit at the bottom.

AviateX14
– AviateX14

2011-10-25 19:18:18 +00:00
Commented Oct 25, 2011 at 19:18 — AviateX14
– AviateX14, Commented Oct 25, 2011 at 19:18
$count = $row['A']; should work in your code

rackemup420
– rackemup420

2011-10-25 19:21:02 +00:00
Commented Oct 25, 2011 at 19:21 — rackemup420
– rackemup420, Commented Oct 25, 2011 at 19:21

Icarus · Accepted Answer · 2011-10-25 19:35:46Z

2

SELECT ip, min(entry), max(id) as id, COUNT(ip) AS A 
FROM table_name AS C 
GROUP BY ip
ORDER BY max(id) DESC

You were missing to do MAX(id) since you want to group by IP and entry. You can drop the Having>1 since you want to see counts =1 according to your sample output

Sample script:

declare @table table
(
id int,
entry varchar(20),
ip varchar(20)
)
    insert into @table
    values

(1,   'abc',  '19.123.14.5'),
(2,   'cde' , '19.123.14.5'),
(3 ,  'efg',  '12.231.22.9')


SELECT  max(id) as id, ip, max(entry), COUNT(ip) AS count 
FROM @table AS C 
GROUP BY ip
ORDER BY max(id) asc

Produces:

id          ip                                        count
----------- -------------------- -------------------- -----------
2           19.123.14.5          cde                  2
3           12.231.22.9          efg                  1

edited Oct 25, 2011 at 19:35

answered Oct 25, 2011 at 19:18

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Joe Stefanelli Over a year ago

min(entry) and max(id) could come from two different rows in the table. I don't think that would be desirable in the output. I would think that those two values should correspond to the same row.

Icarus Over a year ago

@JoeStefanelli good point but if that's the case his sample output is wrong. Look at column with id=2. Comes from a different record. I don't think it really matters to OP but that's for him to clarify.

AviateX14 Over a year ago

It doesn't matter to me, I just need to count the IP duplicates. At the moment (with this code and the code below) it returns an empty row in the table that it creates.

Joe Stefanelli Over a year ago

@Icarus The sample output must be wrong as that ip address (12.231.22.9) belongs to id 3, not id 2.

Icarus Over a year ago

@AviateX14 I just posted a sample script (on SQL Server) but MySQL should return the same. If that's not the case something is really odd with MySQL.

|

Branko Dimitrijevic · Accepted Answer · 2011-10-25 19:41:57Z

1

You have different ids and entries for ip=19.123.14.5. Which one of them you wish to choose?

For just counting ips and ignoring id and entry altogether:

SELECT ip, COUNT(*)
FROM table_name
GROUP BY ip

12.231.22.9, 1
19.123.14.5, 2

For selecting a random row out of many that might be connected to the same ip (this is MySQL specific and would not work under other databases):

SELECT id, entry, ip, COUNT(*)
FROM table_name
GROUP BY ip

3, efg, 12.231.22.9, 1
1, abc, 19.123.14.5, 2 // Might also be: 2, cde, 19.123.14.5, 2

For selecting all rows, together with ip counts:

SELECT *, (SELECT COUNT(*) FROM table_name t2 WHERE t1.ip = t2.ip)
FROM
    table_name t1

1, abc, 19.123.14.5, 2
2, cde, 19.123.14.5, 2
3, efg, 12.231.22.9, 1

edited Oct 25, 2011 at 19:41

answered Oct 25, 2011 at 19:36

Branko Dimitrijevic

52.3k12 gold badges103 silver badges174 bronze badges

Comments

Joe Stefanelli · Accepted Answer · 2011-10-25 19:24:14Z

0

SELECT t.id, t.entry, t.ip, q.IpCount 
    FROM (SELECT ip, MIN(id) AS MinId, COUNT(*) AS IpCount
              FROM table_name
              GROUP BY ip) q
        INNER JOIN table_name t
            ON q.ip = t.ip
                AND q.MinId = t.id
    ORDER BY t.id DESC;

answered Oct 25, 2011 at 19:24

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

p.matsinopoulos · Accepted Answer · 2011-10-25 19:52:12Z

0

I believe that your result/example table that you want to build has something that is not that rational. You cannot combine ID and ENTRY with counter of IPs. For example, the first row in your result / example table says that IP 19.123.14.5 has been found 2 times, which is correct, but associates it to ID 1 and ENTRY abc. Why? What is the reason you do not associate it to ID 2 and ENTRY cde. There is also another mistake (? ... I cannot tell ...) on the row with ID 2 and ENTRY efg. This combination does not exist in the initial table that you give. The resulting table with all 4 columns says nothing.

Unless you want to display always the first (minimum) ID, ENTRY that the particular IP was found. Is that so?

If this is so then your SQL is not correct. The correct SQL is:

 select aa.min_id as id, 
           b.entry, 
           aa.ip as ip, 
           aa.count_ip as count     
 from table_name b     
 join (select min(a.id) as min_id, 
                 a.ip, 
                 count(a.ip) as count_ip 
          from table_name a 
          group by a.ip) aa 
      on aa.min_id = b.id;

answered Oct 25, 2011 at 19:52

p.matsinopoulos

7,8426 gold badges48 silver badges99 bronze badges

4 Comments

Joe Stefanelli Over a year ago

Looks a lot like the answer I posted 28 minutes earlier.

p.matsinopoulos Over a year ago

@Joe Stefanelli Sorry, but I was doing the solution off-line. Then I posted. BTW, your solution has a join q.ip=t.ip which is not necessary. But, yes, otherwise, they are the same.

Joe Stefanelli Over a year ago

It's probably overkill, but I included the ip in the join because I did not want to make any assumptions about id being unique.

p.matsinopoulos Over a year ago

@Joe Stefanelli Yes, you are right that you didn't make any assumption. But now this is irrelavant :-) since the person who asked picked up as an answer the wrong answer according to my rational. Hence, so long...

Collectives™ on Stack Overflow

MySQL select records, count duplicates

4 Answers 4

9 Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related