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.