SELECT query for checking duplicates without input

Question

It is the first time I´m using SELECT queries without having an input. I would like to check a table for duplicates. As a result I only need to count the result.

So I have this:

SELECT 
       `field a`, `field b`, `field c` 
FROM 
        table 
WHERE 
        `a` AND `b` AND `c` 
GROUP BY
    duplicates
HAVING
    COUNT(duplicates) > 1

would this check the table for duplicates when the structure is like:

field a |  field b  | field c
  a          b          c
  a          b          c
  d          e          f

And how can I get the result? Would it be just num_rows or do I get an integer as a result back from the query?

Normally I use num_rows to fetch the result from the query like that:

$query = $db->query("SELECT ...");
$result = $query->num_rows;
if ($result !== 0 ){do something}

There usually should be a difference between num_rows and the result because if I would count by using num_rows it will return 2 because 2 have the condition a AND b AND cso I would think that the query from above would return 1 as a result for 1 duplicate found. Or am I wrong?

Thanks alot.

I think the `WHERE` clause there is useless. – Mark Apr 16 '13 at 07:42 — Mark, Apr 16 '13 at 07:42

score 2 · Accepted Answer · edited May 23 '17 at 11:43

2

If you define as duplicate two records having the same A,B, and C, then you could use a query like this:

SELECT A, B, C, COUNT(*) counter
FROM `MYTABLE`
GROUP BY A,B,C
HAVING COUNT(*) > 1

Considering your table has 3 items like in your question, your result would be:

 A  |  B  |  C  | counter
 ---+-----+-----+--------
'a' | 'b' | 'c' | 2

To fetch the results in PHP:

while ($row = mysql_fetch_assoc($result)) {
    $a = $row['A'];
    $b = $row['B'];
    $c = $row['C'];
    $counter = $row['counter'];
    // Do something with your values
}

Please consider to use mysqli or PDO. A good starting point: mysqli or PDO

edited May 23 '17 at 11:43

Community

1
1

answered Apr 16 '13 at 07:43

agim

1,841
12
19

hello and thanks. sorry for this but how can i fetch the result? so that i can work with the result? normally i use num_rows to the query but would this be correct because the query has already the condition > 1. – bonny Apr 16 '13 at 07:59
You mean how do you fetch results from PHP or from mysql? – agim Apr 16 '13 at 08:00
hello, this is still not what i´m looking for. i'm sorry. language difficulties. have a look at my table. (a AND b AND c) is exactly two times in that db. so the query should count duplicate = yes (as a result -> 1). your table has all different values because of (a1 OR a2 AND b AND c). hope its gettin clear now. thanks alot. – bonny Apr 16 '13 at 08:15
It is clear what you mean. Group by allows only different results. I updated the result – agim Apr 16 '13 at 08:20

score 1 · Answer 2 · answered Apr 16 '13 at 07:45

1

I'm not sure that I quite understand the question, but if you want to display values and counts where more than one row exists with the same values of all three fields you may require something like...

SELECT FIELD_A, FIELD_B, FIELD_C, COUNT(*)
    FROM MY_TABLE
    GROUP BY FIELD_A, FIELD_B, FIELD_C
    HAVING COUNT(*) > 1;

answered Apr 16 '13 at 07:45

Brian Hooper

21,544
24
88
139

hello and thanks. sorry for this but how can i fetch the result? so that i can work with the result? normally i use num_rows to the query but would this be correct because the query has already the condition > 1. – bonny Apr 16 '13 at 07:53
@bonny, I don't have a working example to hand, but if I rightly recall you will need to call `mysqli_fetch_array` on your `$query` object. – Brian Hooper Apr 16 '13 at 08:16
hello, this is still not what i´m looking for. i'm sorry. language difficulties. have a look at my table. (a AND b AND c) is exactly two times in that db. so the query should count duplicate = yes (as a result -> 1). – bonny Apr 16 '13 at 08:18

SELECT query for checking duplicates without input

2 Answers2