how to mark duplicates value in table view?

[zkarwinkar]

how to mark duplicates or more than 2 times repeating value in table view for certain field, and filter it as same values ?

[jsetzer]

how to mark duplicates or more than 2 times repeating value in table view for certain field, and filter it as same values ?

For avoiding duplicates you should set unique-flag.

Anyway, if there is duplicate data already in your database table, personally, I would create an additional field is_duplicate on that table, then do the checks on serverside (SQL+PHP) and update that new field if you detect a duplicate. Having this additional field and having records tagged as "duplicates", in the second step there are many ways for filtering, sorting, searching and highlighting.

Given a table named customers having a column named email, this is the SQL command for finding all duplicate emails:

Code: Select all

SELECT `email`
FROM `customers`
WHERE `email` IS NOT NULL
group by `email`
having count(*)>1

(1) Now, for example, in PHP, iterate through all records and mark the duplicate records by setting is_duplicate = 1 (and all the others = 0).

(2) As an alternative, turn is_duplicate into a calculated field which automatically updates it's status.

This is the SQL command* for the calculated field in the given example

* this is an example for good readability. There are more efficient ways in SQL. Subselects are not very performant but easy to use and understand here.

Code: Select all

SELECT 
(SELECT count(*)>0 FROM customers WHERE A.email = email AND A.id != id)
FROM `customers` as A
WHERE A.id='%ID%'

chrome_BNxessqSPD.png (5.29 KiB) Viewed 1824 times

---
Personally I prefer (1) over (2) because I'm very flexible when doing this for example in TABLENAME_init hook. There I can change my SQL conditions and update records according to my business requirements without changing and re-generating the project. But this is only my personal strategy. Especially I can re-evaluate all records and not only the displayed records. Calculated fields only work on displayed records unless you trigger the re-calculation manually.

[zkarwinkar]

Thank you sir , it worked !!!
And yes i would also go for 1 instead of this , but i had requirement of matching and finding duplicates .