MS SQL to PHPList MySQL

Solutions for other advanced phplisters

MS SQL to PHPList MySQL

Postby dobseh » 9:24am, Wed 14 Apr, 2010

Hi All,

I work for a Not For Profit organisation based in the UK, we are mainly focused on membership services and have a largish(approx 29,000) member base. I am currently looking at PHPlist to provide newsletter type services to our members. We would need to "refresh" the data in the PHPList database on a regular basis using updated data from our CRM system database(running MS SQL 2000). Basically this refresh would involve truncating all the userdata, maillist(s) and attribute tables in MySQL and re-populating them from SQL files generated using DTS\SSIS from the SQL 2000 servers. This refresh would be carried out on a weekly(probably) basis and as much as possible it should be automated.

I wondered if anyone here had attempted this kind of thing before and if there were any pointers for someone attempting it?

Any help or information will be gratefully received!

Many thanks,
dobseh
dobseh
phpList newbie
 
Posts: 2
Joined: 7:03am, Wed 14 Apr, 2010

Re: MS SQL to PHPList MySQL

Postby Heritage » 12:20pm, Wed 14 Apr, 2010

I have 16 million customers in MS SQL and have put a million or two into different PHPlist installs on sites. I have over 300 million consumers so PHPlist is sometimes more for sizes that your speaking of so you are heading in the right direction. However, the issue of replication is a tough one. When you say refresh are you speaking about existing customer records or a simple import of new records? I would suggest updating PHPlist via an ODBC connection rather than just blowing away any info. Issue is people can update preferences and unsubscribe from PHPlist. So your CRM and PHPlist need to figure out who is updating who or merging. Something to think about. Might be easier to just keep it separate and see what happens. If the issue is just importing then you can run a stored procedure to do an import into PHPList via an ODBC connection. I manually import the updated info from a SQL stored procedure that dumps the new customer records. Most of the time people go to their preferences and update the info themselves or available staff update the record changes in both databases. Are you a 503 (c) non-profit?
Heritage
Moderator
 
Posts: 223
Joined: 3:25pm, Fri 23 Jun, 2006
Location: 800 720-7301

Re: MS SQL to PHPList MySQL

Postby dobseh » 3:53pm, Wed 14 Apr, 2010

Thanks for the response Heritage.

Good to know that I am going in roughly the correct direction. :)

To give you a better understanding of my thinking for a full refresh of the data I need to explain a bit more how the organisation works and the full use to which we would put PHPList. I'll try to condense this into as small a nutshell as I possibly can!

The organisations membership is divided into Regions, these are then sub divided into Branches. There are currently 15 regions and 180(ish) branches. We have officials(volunteers, essentially) who run the Regions and Branches. Our members are divided into different classes.

My thinking was that each member would have their Branch and membership class as User Attributes. Each Region would have a login for it's official that consisted of one list(all the branches in that region) they could then use Criteria to email the member class(es) and branch(es) they wished. There will be no opt-in\out done using PHPlist, the CRM will always win.

Members can change memberclass, region and branch. Branches can also merge and change region. From my limited understanding this would be quite difficult to achieve without truncating all the PHPList tables and recreating them?

With regard to 503 (c) non-profit, we are UK based so I'm not entirely sure what the equivalent is, but we are a trade union involved in educational advice.

dobseh
dobseh
phpList newbie
 
Posts: 2
Joined: 7:03am, Wed 14 Apr, 2010

Re: MS SQL to PHPList MySQL

Postby Heritage » 4:16pm, Wed 14 Apr, 2010

My first thought is maybe you just need MailMan where people subscribe to a list and everyone to that list gets the email.

However it your managing people dropping letters to different organizations . . . the way to manage managers is to have them as admins so they can separately log into their own lists. This way in the master Admin you can add or subtract what they essentially have access to. Also when some one is logged in they control who gets what by lists. There people can be subscribed to multiple lists.

No blowing away data needed.

Not sure if PHPlist is really over kill and simply having mailing lists would suffice for all of your communications. Most email servers have a mail list service built in.
Heritage
Moderator
 
Posts: 223
Joined: 3:25pm, Fri 23 Jun, 2006
Location: 800 720-7301


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 2 guests