Menu
▾
▴
[Slashcode-general] Unsubscribing users off the daily newsletters
|
From: Alexandre L. <ale...@ec...> - 2008-11-26 14:45:34
|
Hi list, For my small slash site (1,800 registered users, about 30-40,000 daily hits), I want to unsubscribe users that are subscribed to the daily headlines or newsletter but which have their email addresses bouncing back every night. I'm a SQL novice, so I'd simply like a confirmation that this command below is the right way to do it. DELETE FROM users_messages WHERE uid IN (SELECT uid FROM users WHERE realemail = 'enter-real-email-here') AND code <= 1; Some more details below if you need it. Thanks a lot for the help! Alex for Slashgeo.org -- Alexandre Leroux, M.Sc., Ing. Environnement Canada / Environment Canada Centre météorologique canadien / Canadian Meteorological Centre Section de la réponse aux urgences environnementales / Environmental Emergency Response Section ale...@ec... >> I think I must find the 'uid' from the 'users' table with the >> 'realemail' field. For this 'uid', I must delete (right??) entries >> with code '0' and '1' from the 'users_messages' table. >> >> >> mysql> select * from message_codes; >> +------+----------------------------+---------+-------+------------+-----------+-------+-----------------+ >> >> | code | type | seclev | modes | send | >> subscribe | acl | delivery_bvalue | >> +------+----------------------------+---------+-------+------------+-----------+-------+-----------------+ >> >> | -2 | Registration Mail | 1 | 0 | now >> | 0 | | 0 | >> | -1 | Unknown Message | 1 | 0 | now >> | 0 | | 0 | >> | 0 | Daily Newsletter | 1 | 0 | now >> | 0 | | 1 | >> | 1 | Daily Headlines | 1 | 0 | now >> | 0 | | 1 | >> | 2 | Metamoderation Results | 1 | | collective >> | 0 | | 3 | >> | 3 | Comment Moderation | 1 | | collective >> | 0 | | 3 | >> | 4 | Comment Reply | 1 | | now >> | 0 | | 15 | >> | 5 | Journal Entry by Friend | 1 | | now >> | 0 | | 7 | >> | 6 | New Submission | 100 | 0 | now >> | 0 | | 1 | >> | 7 | Journal Reply | 1 | | now >> | 0 | | 7 | >> | 8 | New Comment | 1000000 | | now >> | 0 | | 0 | >> | 10 | Daily Site Stats | 100 | 0 | now >> | 0 | stats | 1 | >> | 11 | Email Story | 1 | 0 | now >> | 0 | | 1 | >> | 12 | Relationship Change | 1 | | collective >> | 1 | | 3 | >> | 13 | Bad login attempt warnings | 1 | 1 | now >> | 0 | | 2 | >> | 14 | Daily Moderation Stats | 100 | 0 | now >> | 0 | stats | 1 | >> | 18 | Invalid HTML Input | 100 | | now >> | 0 | | 3 | >> | 19 | System Messages | 100 | 0 | >> | 0 | | 4 | >> | 20 | New Story Posted | 1 | 0 | >> | 0 | | 12 | >> +------+----------------------------+---------+-------+------------+-----------+-------+-----------------+ >> >> 19 rows in set (0.00 sec) >> >> mysql> >> mysql> describe users_messages; >> +-------+-----------------------+------+-----+---------+----------------+ >> >> | Field | Type | Null | Key | Default | >> Extra | >> +-------+-----------------------+------+-----+---------+----------------+ >> >> | id | int(11) | | PRI | NULL | >> auto_increment | >> | uid | mediumint(8) unsigned | | MUL | 0 >> | | >> | code | int(11) | | | 0 >> | | >> | mode | tinyint(4) | | | 0 >> | | >> +-------+-----------------------+------+-----+---------+----------------+ >> >> 4 rows in set (0.00 sec) >> >> mysql> >> mysql> describe users; >> +-------------------------+-----------------------+------+-----+---------+----------------+ >> >> | Field | Type | Null | Key | >> Default | Extra | >> +-------------------------+-----------------------+------+-----+---------+----------------+ >> >> | uid | mediumint(8) unsigned | | PRI | >> NULL | auto_increment | >> | nickname | varchar(20) | | MUL >> | | | >> | realemail | varchar(50) | | MUL >> | | | >> | fakeemail | varchar(75) | YES | | >> NULL | | >> | homepage | varchar(100) | YES | | >> NULL | | >> | passwd | varchar(32) | | >> | | | >> | sig | varchar(200) | YES | | >> NULL | | >> | seclev | mediumint(8) unsigned | | | >> 0 | | >> | matchname | varchar(20) | YES | MUL | >> NULL | | >> | newpasswd | varchar(8) | YES | | >> NULL | | >> | journal_last_entry_date | datetime | YES | | >> NULL | | >> | author | tinyint(4) | | MUL | >> 0 | | >> +-------------------------+-----------------------+------+-----+---------+----------------+ >> >> 12 rows in set (0.00 sec) >> >> mysql> |
