June 22nd, 2011
posted by Brian Peddle
I recently wanted the capability to add all new users to a mailing list in Expression Engine. There are some plugins out there that help with this if you are using MailChimp or Campaign Monitor but I am not using these. Looking for the easiest solution I decided to just build a trigger that will do it for me on new member creation.
CREATE TRIGGER after_insert_new_member AFTER INSERT ON exp_members FOR EACH ROW BEGIN INSERT INTO exp_mailing_list (list_id, authcode, email, ip_address) VALUES (2, (SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 10)), NEW.email, '127.0.0.1'); END
mcp.mailinglist.php inserts the data like this
$data = array( 'list_id' => $list_id, 'authcode' => random_string('alnum', 10), 'email' => $addr, 'ip_address' => $this->EE->input->ip_address() );
I replaced random_string(‘alnum’, 10) with SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 10 and just used 127.0.0.1 as the default ip address. Make sure you grab the proper list_id you are using, in my example it is 2.
If you have any improvements on this please share.