Querying for Duplicates

I have a query that targets several database tables, with a lot of duplicate values. I just learned how to get around the duplicate problem by adding “LIMIT 1.”

However, I want to display a list of duplicate values only. It would be even better if I could determine how many instances of each value there are. For example, let’s say my combined database tables include six instances of ‘tree’ and two instances of ‘flower’, I’d like to write a query that displays this:

tree (6)
flower (2)

Can anyone tell me how to do that? If adding the number of instances is too complex, that’s fine; it would be really help if I could just display a simple list of duplicates. Thanks.

$stm = $pdo->prepare("SELECT *
FROM (
 SELECT URL, IDArea, IDParent, Name, 'landform' AS TypeX FROM  gw_geog_landforms WHERE Dupe = '0'
UNION ALL
SELECT URL, IDArea, IDParent, Name, 'uno' AS TypeX FROM gw_geog
) AS Combined
WHERE Combined.URL LIKE :XURL");
 $stm->execute(array(
  'XURL'=>$XURL
 ));

I would think it would be pretty easy if you made a data array, assuming you will need other fields from your query… untested.


$data = array();
$stm = $pdo->prepare("SELECT *
FROM (
 SELECT URL, IDArea, IDParent, Name, 'landform' AS TypeX FROM  gw_geog_landforms WHERE Dupe = '0'
UNION ALL
SELECT URL, IDArea, IDParent, Name, 'uno' AS TypeX FROM gw_geog
) AS Combined
WHERE Combined.URL LIKE :XURL");
 $stm->execute(array(
  'XURL'=>$XURL
 ));

while($row = $stm->fetch(PDO::FETCH_ASSOC)){
    $data[$row['TypeX']][] = $row;
}

foreach($data as $TypeX => $arr):
    $typecnt = count($data[$TypeX]);
    echo $TypeX.' ('.$typecnt.')';
endforeach; 

Stupid me, I should have taken out the where clause – WHERE Combined.URL LIKE :XURL

I want to query everything, not just things relating to a particular URL.

Anyway, I removed the WHERE clause from your script, but it isn’t displaying anything. I’ll play around with it some more, though. Thanks.

general way to find (simple) duplicates in SQL:

SELECT
    <key>,
    COUNT(<key>) as cnt
FROM
    <table>
GROUP BY
    <key>
HAVING
    cnt > 1

Thanks. I get it, but I’m having trouble writing it; I get errors no matter how I modify it.

This is where I’m at right now…

$stm = $pdo->prepare("SELECT *
FROM (
    SELECT URL, COUNT (URL) AS CNT FROM gw_geog_political
    UNION ALL 
    SELECT URL, COUNT (URL) AS CNT FROM gw_geog
) AS Combined
    GROUP BY URL HAVING CNT > 1");
 $stm->execute(array(
 ));

while ($row = $stm->fetch())
{
 $URL[] = $row['URL'];
}

echo join($URL, '');

what errors do you get?

SELECT URL, COUNT(URL) AS CNT
FROM (
    SELECT URL FROM gw_geog_political
    UNION ALL 
    SELECT URL FROM gw_geog
) AS Combined
GROUP BY URL 
HAVING COUNT(URL) > 1

Sorry for the late response; I don’t have a WiFi connection at home.

Anyway, that last script works perfect when I paste it into SQL. But when I try to display it on my webpage, I get the error Warning: join(): invalid arguments passed

Is there something wrong with my display code, or should I be using some other method to display it? Thanks.

 while ($row = $stm->fetch())
 {
  $URL = $row['URL'];
 }

echo join( $URL, '' );

Try adding this line immediately above the while

$URL = "nothing-fetched";
while ($row = $stm->fetch())
 {
  $URL = $row['URL'];
 }
echo join( $URL, '' );

If there is no row from the fetch you should see “nothing-fetched”, if there is, the value will be over-written

Cool tip! Thanks.

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