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.
Have you compared the results from
SELECT COUNT(primary key) FROM table
to
SELECT COUNT(DISTINCT field) FROM table
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));
?>
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.
[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…
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.
[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?
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.
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?
Sorry, you’re right I did. It would be the column sale
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
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
thank you
Yes, I know, except my current server that use is stil PHP 5.3. Once it’s moved I’ll migrate
PDO has been around since version 5.1 of PHP
Honestly, PHP is not my strong point. Do you have a good recommendation of tutorials for PDO or mySqli?
That article covers the migration of code from the old mysql_* extension over to PDO
thanks, I’ll take a look
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.