Page 1 of 1

Deleting repetitive items not with same key

Posted: Mon Nov 03, 2008 12:29 pm
by crazycoders
Hey there,

I have a database driven massmailing tool for one of my client. A lot of users create dupe accounts and subscribe to the mailing list. When building a list of people to send to i select it based on the id of the account cause i wish to build stats about who the email was sent to and opened it.

In all cases, i don't want to refactor my database, nor my code except for the sql string i'm using, so here goes:

table mvg_users contains:
- id int auto pk
- contact_email varchar(??)

table massmail_sentto:
- id int auto pk
- massmailid int
- user int
- flags tinyint

I'm currently calling my transfer like this:

Code: Select all

INSERT INTO forum_massmails_sentto (massmail_id, user, sent) SELECT '.$massmail['massmail_id'].' AS massmail_id, id AS user, '.MASSMAIL_STATE_UNSENT.' AS sent FROM mvg_users WHERE contact_flagsemail != 0
This selects all users that want to receive an email in the system.

I want it to filter out dupes based on the email. The only way i saw this possible was to create a select statement with a groupby to detect dupes after the insert and then loop the result and DELETE FROM massmail_sentto WHERE userid = ?? where ?? = a list of id's built from the GROUP BY i just did.

Thats surely not the only way, i just don't know how i could do it apart from that.

Re: Deleting repetitive items not with same key

Posted: Tue Nov 04, 2008 3:41 pm
by Jade
Can't you just pull all the email's distinctly? That way you won't send to the same email twice. That won't make up for the idiots who are using two different emails, but hey, maybe they want it sent to both places:

http://www.sql-tutorial.com/sql-distinct-sql-tutorial/