Deleting repetitive items not with same key
Posted: Mon Nov 03, 2008 12:29 pm
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:
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.
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 != 0I 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.