Integrating PHPLIST with SMF

[Phphelp]

Hey everyone,

     I'll just like to share how I integrated PHPList (Open Source Email Marketing Tool) with SMF.  I run PHPList and SMF off the same MYSQL Box (just 2 databases).  When a user signs up on my SMF Forum (phphelp.com), I automatically populate PHPList with that users information, so they are ready to be emailed about announcements, etc.

In order to do this...  in PHPList you have to create a list, so you can get a list id.

SELECT * FROM phplist.phplist_list;

Once you have your list ID, you can then create this TRIGGER on the SMF Database on the smf_members field, I made it so it only ads activated users.

Code Select Expand

DELIMITER $$
CREATE TRIGGER `phphelp`.`UpdatePhpList`
AFTER UPDATE ON `phphelp`.`smf_members`
FOR EACH ROW
begin
if new.is_activated = 1 then
  INSERT IGNORE INTO phplist.phplist_user_user(email, confirmed,entered,modified, uniqid, htmlemail, bouncecount,disabled) values (new.email_address,1,now(), now(), UUID(), 1,0,0);
  INSERT IGNORE INTO phplist.phplist_listuser set userid = (select id from phplist.phplist_user_user where email = new.email_address) , listid = 4, entered = now(), modified= now();
end if;
end$$

Now when a person signs up on your forum and are activated, they will be inserted into you phplist database as well.  So it will save you a lot of time when doing newsletters, announcements, etc.

[ziycon]

Nice one Phphelp, thanks for sharing this with the community

[jriani]

Awesome tool! I have implemented it, but I'm getting a warning in my syslog due to the trigger:

Code Select Expand


[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: UPDATE smf_members#015


I think the UUID() function is the culprit. Any ideas for a workaround?