CODEWITHSUNDEEP
phpsql
Milan Milosevic
Milan Milosevic

Reputation: 433

Find Duplicate Rows/Records from Table

Here is my table structure,

enter image description here

Im try run query

$sql = mysql_query("SELECT content,niche, COUNT(content) TotalCount FROM table_name GROUP by content HAVING COUNT(content)>=2");

I i think is give me corect result, but have problem to list result with php and make delete button to delete one of duplicated rows

Im get result in php

Content ID - Niche ID - TotalCount
208 - 2 - 2
210 - 32 - 3

But result should be

Content ID - Niche ID - TotalCount
208 - 2 - 2
208 - 2 - 2
210 - 32 - 3
210 - 32 - 3
210 - 32 - 3

im try result display with php

while($row = mysql_fetch_assoc($sql)) {            
    $array[] = $row;
}
foreach($array as $row) {
    echo $row['content']." - ".$row['niche']." - ".$row['TotalCount']."<br>";
}

Upvotes: 1

Views: 939

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

I think this is what you're asking for, all duplicate rows (with row_id) and how many times they are duplicated;

SELECT a.row_id, a.content, a.niche, cnt
FROM table_name a
JOIN (
  SELECT MIN(row_id) m, COUNT(*) cnt, niche,content
  FROM table_name
  GROUP BY content,niche
  HAVING COUNT(*)>1
) b
  ON a.niche=b.niche
 AND a.content=b.content

An SQLfiddle to test with.

Upvotes: 1

Dhaval Bharadva
Dhaval Bharadva

Reputation: 3083

Make the following change in sql: 

$sql = mysql_query("SELECT content,niche, COUNT(content) TotalCount FROM table_name HAVING COUNT(content)>=2");

Upvotes: 0

newfurniturey
newfurniturey

Reputation: 38436

GROUP BY will collapse the results on the field you're grouping, in this case content - hence why you only see two results.

If you want to keep the GROUP BY technique, you can also use GROUP_CONCAT(niche) to pull a comma-separated list of each niche for a given content value:

SELECT
    content,
    GROUP_CONCAT(niche) AS niche,
    COUNT(content) TotalCount
FROM
    table_name
GROUP BY
    content
HAVING
    COUNT(content)>=2;

You can then use PHP's explode(',', $row['niche']) to get each distinct value and then use those to determine which one you want to delete.

foreach($array as $row) {
    $niches = explode(',', $row['niche']);
    foreach ($niches as $niche) {
        echo $row['content'] . " - " . $niche . " - " . $row['TotalCount'] . "<br />";
    }
}

Upvotes: 2

Related Questions