List Only Duplicate Rcords

11 posts by 3 authors in: Forums > CMS Builder
Last Post: July 4, 2019   (RSS)

By nmsinc - June 26, 2019

Is there a way to list only records that are exactly the same (duplicates) in the load records code below?

Each record consists of three number point fields! $a, $b, $c

<?php

// load records from 'points'

list($pointsRecords, $pointsMetaData) = getRecords(array(

'tableName'=>'points',

'loadUploads'=>false,

'allowSearch'=>false,

));

?>

Thanks - nmsinc

nmsinc

By nmsinc - June 29, 2019

The query you showed is correct!

The fields are imported from multiple CSV files. Once imported, I need to located duplicate records so that I can isolate them and remove or make corrections as needed!

thanks - nmsinc

nmsinc

By daniel - July 1, 2019

Hi nmsinc,

For this sort of task, it's easiest to use a direct MySQL query for access to some advanced query features. We can use the mysql_select_query() function for this, which would look something like this:

$query = "SELECT *
          FROM   table_name tba
          WHERE  EXISTS
          (
            SELECT 1
            FROM   table_name tbb
            WHERE  tba.field_a = tbb.field_a
            AND    tba.field_b = tbb.field_b
            AND    tba.field_c = tbb.field_c
            LIMIT  1,1
          )
          ORDER BY field_a, field_b, field_c;";

$queryRecords = mysql_select_query($query);

foreach ($queryRecords as $queryRecord) {
 
  showme($queryRecord);

}

You'll need to update the table and field names to match your database. Hopefully this does the trick for you - let me know if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By gkornbluth - July 1, 2019

Pretty elegant Daniel,

I've been thinking about needing something similar and this is a great start. 

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By nmsinc - July 3, 2019

Hi Daniel,

I created a new table to test your query - the table name is 'table_1' the fields are the same as in your query example!

I receive the following error code when I run the query: any ideas what I did wrong?

#19650 - E_USER_NOTICE: MySQL Error: Table &apos;aprnews.table_1&apos; doesn&apos;t exist
- in mysql_functions.php on line 255 by dieAsCaller()
/home/aprnews/public_html/cmsAdmin/lib/common.php (line 297)

My code:

$query = "SELECT *
          FROM   table_1 tba
          WHERE  EXISTS
          (
            SELECT 1
            FROM   table_1 tbb
            WHERE  tba.field_a = tbb.field_a
            AND    tba.field_b = tbb.field_b
            AND    tba.field_c = tbb.field_c
            LIMIT  1,1
          )
          ORDER BY field_a, field_b, field_c;";

$queryRecords = mysql_select_query($query); ?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=windows-1252" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head><body>

<?php foreach ($queryRecords as $queryRecord) {  showme($queryRecord);  } ?>

</body></html>

nmsinc

By daniel - July 3, 2019

Hi nmsinc,

Did you create this table through the Section Editor? If so, you'll also need to add your table prefix to the table name. You can try something like this:

$tableName = getTableNameWithPrefix('table_1');
$query = "SELECT *
          FROM   $tableName tba
          WHERE  EXISTS
          (
            SELECT 1
            FROM   $tableName tbb
            WHERE  tba.field_a = tbb.field_a
            AND    tba.field_b = tbb.field_b
            AND    tba.field_c = tbb.field_c
            LIMIT  1,1
          )
          ORDER BY field_a, field_b, field_c;";

Let me know if this helps!

Thanks,

Daniel
Technical Lead
interactivetools.com

By nmsinc - July 3, 2019

That did the trick - thanks Daniel

nmsinc

nmsinc

By gkornbluth - July 3, 2019

Hi Daniel,

I guess that I could list all of the fields in the table that I want to compare, but is there an easy way that I could compare all fields in a record except for a few (like createdDate, UpdatedDate and username in the accounts table for example) and list those records that come up as duplicates?

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By daniel - July 4, 2019

Hey Jerry,

Unfortunately, I can't think of a way that this could be done with a query other than explicitly listing every field to compare. If that's something you'd like to be able to do regularly, the easiest route may be something like a custom script that would generate and run a query based on a table and some excluded field names. Do you think that would be useful to you? Feel free to send us an estimate request (https://www.interactivetools.com/estimate/) with some more details about your use case if you're interested in getting this built.

Thanks!

Daniel
Technical Lead
interactivetools.com