[dsumpter]

Please help!!! I am trying to now clean up my database of customers that contains duplicates. I would like to be able to display all records that have the same email address fieldname "targetEmail" and client ID "clientID"... then delete certain ones that I select.

Does anyone know how I can do the display portion of this? Thanks in advance for your help...

Dan

[ChristGuy]

Greetingz...

Please could you post your table structure Including Indexes...

My Solution isn't PHP but MySQL Based... (Basically Tightening your Indexes to not allow duplicates...)

[ermau]

Yes, you should have clientID as primary, indexed and auto_increment so it doesn't duplicate.

[dsumpter]

Thanks for your reply's guys... I know how to designate fields as unique in MySQL, but my problem is that there are currently duplicate records and I can't just eleminate based upon one field in the table.

I need to be able to allow duplicates of the targetEmail field, because the same person can be registered with multiple clientID's.

What do you think about a way to display all records that have both the same targetEmail and clientID?

I found one idea on a message board, but cant get it to work. Let me know what you think... Thanks again for your help guys!!!

http://www.15seconds.com/issue/011009.htm


----------------------------
My Table structure is below...

# phpMyAdmin MySQL-Dump
# version 2.2.6
# http://phpwizard.net/phpMyAdmin/
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Aug 28, 2003 at 08:37 AM
# Server version: 3.23.55
# PHP Version: 4.2.2
# Database : `aci`
# --------------------------------------------------------

#
# Table structure for table `target`
#

CREATE TABLE target (
targetID int(11) NOT NULL auto_increment,
targetEnteredDate varchar(8) NOT NULL default '0',
targetEmail varchar(50) default NULL,
targetFirstName text NOT NULL,
targetLastName text,
targetStreetAddress varchar(50) default NULL,
targetCity text,
targetState text,
targetZip varchar(11) default '0',
targetFirstTimeCustomer text NOT NULL,
targetBirthday1 varchar(4) default NULL,
targetBirthday1Name text,
targetBirthday2 varchar(4) default NULL,
targetBirthday2Name text,
targetBirthday3 varchar(4) default NULL,
targetBirthday3Name text,
targetBirthday4 varchar(4) default NULL,
targetBirthday4Name text NOT NULL,
targetBirthday5 varchar(4) default NULL,
targetBirthday5Name text NOT NULL,
targetAnniversary varchar(4) default NULL,
clientID int(11) NOT NULL default '0',
targetAdType text,
targetSpecialInterest1 text,
targetSpecialInterest2 text,
targetSpecialInterest3 text,
targetSpecialInterest4 text,
targetSpecialInterest5 text,
targetSpecialInterest6 text,
targetOther1 text,
targetOther2 text,
targetBillAmount int(11) default NULL,
targetServer varchar(25) default NULL,
targetFTEmailSent char(1) default NULL,
PRIMARY KEY (targetID),
KEY clientID (clientID)
) TYPE=MyISAM;

[ChristGuy]

Greetingz...

I've been unable to get to that site: http://www.15seconds.com/issue/011009.htm

If you could could you please e-mail it to me...

also from your last message... Indexes won't solve your problem... unless you only have one entry per person...

not more than one like you said you'd need?

[dsumpter]

go to http://dsumpter.com/15seconds.pdf

I don't fully understand what the guy in this article is doing, but it sounds like the end result is kinda like what I want to do.

My biggest thing is that I just want to be able to display all records that have the same targetEmail, and clientID.

What do you think?

Thanks man...
Dan

[ChristGuy]

Greetingz...

Thanx for the PDF...

Because MySQL < 4.1 can't do Sub Selects We'll haveto try something different...
(This hasn't been tersted but should work)

PHP Code:

  $dbConn = MySQL_Connect( "localhost", "root", "" ) or

    Die( "Error connecting to MySQL: " . MySQL_Error() );



  MySQL_Select_DB( "aci" ) or

    Die( "Error selecting Database: " . MySQL_Error() );



  $sSQL = "SELECT DISTINCT clientID, targetEmail FROM target";  //  Select Unique values



  $resUnique = MySQL_Query( $sSQL ) or

    Die( "Error executing query: " . MySQL_Error() );



  while ( $row = MySQL_Fetch_Array( $recUnique ) )

  {

    $sSQL = "SELECT * FROM target WHERE clientID=" . $row["clientID"] . " AND targetEmail=" . $row["targetEmail"];



    $recDuplicate = MySQL_Query( $sSQL ) or

      Die( "Error executing query: " . MySQL_Error() );



    while ( $duplicates = MySQL_Fetch_Array( $recDuplicate ) )

    {

      print( "Duplicate found: " . $duplicates["clientID"] . "<BR>\n" );

    }



    MySQL_Free_Result( $recDuplicate );

  }



  MySQL_Free_Result( $recUnique );

  MySQL_Close( $dbConn ); 

Like I said I havn't tested the above... But I'm quite sure it will work as is...