How to quickly find duplicate entries in db tables

Databases
1

How or what would be the best way to find duplicate entries in a database table? I know the obvious way to do this is just to look through it manually, but I was hoping there would quick and simple way to do this.

2

Have you compared the results from
SELECT COUNT(primary key) FROM table
to
SELECT COUNT(DISTINCT field) FROM table

3

No I have not, I’ll try that. Is it simple enough to stick anywhere on my update page?

Just to clarify, that would look something like this:

<?php

$result = mysql_query("SELECT COUNT (id) FROM Reports to SELECT COUNT (sale) FROM Report") or die(mysql_error));
?>
4

I would just run them from the CLI or write a simple script file.

No need to put that into any real file yet as that’s only a quick info finding thing, not the query you’ll need for anything permanent.

5

[quote=“Mittineague, post:4, topic:105922, full:true”]
I would just run them from the CLI or write a simple script file. [/quote]jeepers, man, you need a gui

heidisql, navicat, mysql workbench, toad…

6

Hi,

Just an example :

SELECT name, email, COUNT() FROM users GROUP BY name, email HAVING COUNT() > 1

This query will give us name which has the same e-mails. Simply group on the both columns

Note: the ANSI standard is to have all non aggregated columns in the GROUP BY. Check the resource find duplicate rows

Hope this guides you with sufficient information.

7

[quote=“lauren_olsen17, post:1, topic:105922, full:true”]
How or what would be the best way to find duplicate entries in a database table? [/quote]which column(s)?

usually when people say duplicates, they are referring to one or more specific columns

can’t be the PK (because it’s unique by definition), and it can’t be the entire row (because the row includes the PK)

so… what kind of duplicates are you looking for?

8

I was looking to find duplicate entries that were added by submission. into the database. For example if a entry was entered twice on a different occasion.

9

i don’t think you understood my question

“if a entry was entered twice on a different occasion” isn’t precise enough

which column(s) contain the values that are duplicated?

10

Sorry, you’re right I did. It would be the column sale

11

Semi-off-topic (PHP): You should be migrating away from the old mysql_* extension as that’s deprecated as of version 5.5 of php

12

okay, here ya go –

SELECT pk , col1 , col2 , sale FROM ( SELECT sale FROM daTable GROUP BY sale HAVING COUNT(*) > 1 ) AS dupes INNER JOIN daTable ON daTable.sale = dupes.sale

13

thank you

14

Yes, I know, except my current server that use is stil PHP 5.3. Once it’s moved I’ll migrate

15

PDO has been around since version 5.1 of PHP

16

Honestly, PHP is not my strong point. Do you have a good recommendation of tutorials for PDO or mySqli?

17

That article covers the migration of code from the old mysql_* extension over to PDO

18

thanks, I’ll take a look :slight_smile:

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.