Deleting Multiple emails

[brown2005]

I have a table

 

member

 

with the field

 

members_email

 

what i want to do is delete additional records where there is more than 1 for the members_email..

 

[Cory94bailly]

Show us what you have now..

[revraz]

And show the table structure, your description doesn't make a whole lot of sense unless we can see it.

[brown2005]

CREATE TABLE `members` (

  `members_email` varchar(255) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

then in it i have... say

 

[email protected]

[email protected]

[email protected]

[email protected]

[email protected]

[email protected]

 

and i want to delete the additonal emails.. to just have 1 of each

 

[email protected]

[email protected]

[email protected]

 

[brown2005]

can anybody help with the above please?

[jonsjava]

this won't help you deleting them, but it will help you so you won't get duplicates. 

ALTER TABLE `members` ADD UNIQUE(`members_email`);

This will make it so you can't dupes any more.

 

[stylusrose]

I'm not to hot with mysql, but you could do this:

 

$data_array; //Put e-mail contents into this array

 

$erase_array = array();

 

for ($index = 0; index < $data_array.length(); index++) {

 

  $erase_array[ $data_array[index] ] = 1;

 

}

 

$end_array = array_keys( $erase_array );

 

//put end array back into database

 

This essentially turns all the emails into a data key for the array each with a value of one.

And then you use array_keys to turn the data keys into an array.

 

And then you should have no repeats of any emails.

[cooldude832]

so you want to delete all but the latest message for people with more than 1 message?  (Good to actually right out what you want like this)

if so try something like  *(assuming primary key is UserID change if needed

<?php
$q = "Delete from `members_email` where members_email.UserID IN(Select UserID from `members_email` where COUNT(MessageID) > 1) and members_email.MessageID NOT IN (select MessageID from `members_email` where UserID IN(Select UserID from `members`) Order By Date_Posted Limit 1)";
?>

just throwin it out there probably a mistake but an idea.

 

to explain it the first IN will get the userIDs of the user with more than 1 message and attempt to delete all those emails

the second NOT IN will preserve the last message for each user based on the field date_posted