About Chris Morrell
Other Sites/Clients
Contact Me
Galahad MailChimp Synchronizer
The Galahad MailChimp Synchronizer lets you synchronize an existing mailing list with MailChimp. The synchronization is one directional (from existing to MailChimp) so is most useful for keeping MailChimp synced with something like a membership database or customer list. A base class is provided, so that you can write any custom logic necessary for your particular database, and two implementations are also included (MySQLi and Array). The Array implementation lets you sync an array of e-mail addresses with MailChimp (mostly useful for testing, or very small lists). The MySQLi implementation lets you sync a MySQL database with MailChimp, assuming there is no major processing that has to happen outside of the database. Here’s a quick example of how you might use the MySQLi implementation:
require_once 'Galahad/MailChimp/Synchronizer/Mysqli.php';
require_once 'MailChimp/MCAPI.class.php';
$username = 'MAILCHIMPUSERNAME';
$password = 'MAILCHIMPPASSWORD';
$list = 'LISTID';
$db = array(
'host' => 'localhost',
'username' => 'DBUSERNAME',
'passwd' => 'DBPASSWORD',
'dbname' => 'demo',
);
$cols = array(
'email' => 'EMAIL',
'first_name' => 'FNAME',
'last_name' => 'LNAME',
);
$existsQuery = 'SELECT COUNT(user_id) FROM user WHERE email = ?';
$selectQuery = 'SELECT email, first_name, last_name FROM user WHERE mailinglist = 1';
try {
$synchronizer = new Galahad_MailChimp_Synchronizer_Mysqli($username, $password, $db);
$synchronizer->sync($list, $selectQuery, $existsQuery, $cols);
echo "\nSynced:\n";
// Print out log of sync actions
foreach ($synchronizer->getBatchLog() as $entry) {
echo "\n - {$entry}";
}
// Any individual errors
$errors = $synchronizer->getBatchErrorLog();
if ($errors) {
echo "\nErrors:\n";
foreach ($errors as $batch => $batchErrors) {
echo "\n{$batch}:";
foreach ($batchErrors as $error) {
echo "\n - {$error['message']}";
}
}
}
} catch (Galahad_MailChimp_Synchronizer_Exception $e) {
echo "\nError syncing:\n\n";
echo $e->getMessage();
}
Keep in mind that if you get any individual errors (such as an invalid e-mail address or trying to sync someone who manually unsubscribed) the MailChimp API console will show errors for the listBatchSubscribe and listBatchUnsubscribe operations.
This is a pretty early version of the code, so you should test it thoroughly before using it.
Galahad MailChimp Synchronizer [62.27 KB]14 Responses to “Galahad MailChimp Synchronizer”
Leave a Reply
Additional comments powered by BackType
Comments & Trackbacks
You can leave a response, or trackback from your own site. Follow any responses to this entry through the RSS 2.0 feed.
Thanks for the tip. We are looking for this solution to integrate in a ecommerce site. I will let you know how it goes. -T
Works great! Kudos
I get the following errors… suggestion?
————————————————-
Started. 1. Method: login 2. Method: listMembers
Warning: Invalid argument supplied for foreach() in /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer.php on line 170
Fatal error: Class ‘Galahad_MailChimp_Synchronizer_Exception’ not found in /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer/Mysqli.php on line 171
Good call. Add this to the top of the Mysqli.php file:
I’ll update the library in a bit.
Now I get this response… and I am 90% sure I wrote the MySQL in ‘example-mysqli.php’ correctly:
$existsQuery = ‘SELECT COUNT(id) FROM #__user WHERE email IS NOT NULL’;
$query = ‘SELECT email, name, username FROM #__user’;
$cols = array(
‘email’ => ‘EMAIL’,
‘name’ => ‘FNAME’,
‘username’ => ‘LNAME’,
——————————————–
Started. 1. Method: login 2. Method: listMembers
Fatal error: Uncaught exception ‘Galahad_MailChimp_Synchronizer_Exception’ with message ‘Error getting users from DB: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1′ in /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer/Mysqli.php:176 Stack trace: #0 /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer.php(210): Galahad_MailChimp_Synchronizer_Mysqli->getUsers(‘d1bea72165′, 0, 500) #1 /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer.php(108): Galahad_MailChimp_Synchronizer->processLocalUsers(‘d1bea72165′) #2 /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer/Mysqli.php(162): Galahad_MailChimp_Synchronizer->sync(‘d1bea72165′) #3 /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/example-mysqli.php(53): Galahad_MailChimp_Synchronizer_Mysqli->sync(‘d1bea72165′, ‘SELECT email, n…’ in /data/20/1/143/60/1958875/user/2138878/htdocs/mailchimp/Galahad/MailChimp/Synchronizer/Mysqli.php on line 176
I’m pretty sure that’s an error in your PHP query, but go ahead and try this getUsers function and let me know what error you get:
protected function getUsers($listId = null, $batchNumber) { $start = $batchNumber * $this->_batchSize; $result = $this->_db->query($this->_query . " LIMIT {$start}, {$this->_batchSize}"); if (false === $result) { throw new Galahad_MailChimp_Synchronizer_Exception('Error getting users from DB. MySQL said: "' . $this->_db->error . '". Query: "' . $this->_query . " LIMIT {$start}, {$this->_batchSize}\"."); } $return = array(); while ($row = $result->fetch_assoc()) { $user = array(); foreach ($this->_mergeColumns as $localColumn => $mcColumn) { if (isset($row[$localColumn])) { $user[$mcColumn] = $row[$localColumn]; } } $return[] = $user; } return $return; }The previous reply I made to your code was actually my fault and should be deleted! The ‘Galahad_MailChimp_Synchronizer_Exception’ works great now and it was my SQL statement that was the problem… not your code.
In fact I would like to not only apologize, but also extend a heartfelt and sincere gratitude for your development of this code. There is no one else offering this priceless code for the MailChimp API for free.
I am a broke, journalist/novice web designer and was able to get this code to work on my own. When I needed help, the support you gave me was very friendly and then you offering further support was above and beyond the “call of duty”.
If you have a donate button please let me know
Just a side note: the code for ‘getUsers’ listed above had the ‘>’ or ‘carrot’ sign replaced with html character ‘>’.
When a user already exists in my database as well as on my MailChimp list, and where updates as well as no-updates exist, I get the below WARNING. Is this okay (only indicating a user already exists) or is there something wrong? Everything seems to work okay, including ListUnsubscribe when a user has been removed from my database, but I am worried that as the list becomes bigger this may become a burden on the server. What do you think?:
———————————————–
Started. 1. Method: login 2. Method: listMembers
Warning: mysqli_stmt::bind_param() [mysqli-
stmt.bind-param]: Number of variables doesn’t
match number of parameters in prepared statement in /data/20/1/143/60/1958875/user/2138878/htdocs
/mailchimp_text/Galahad/MailChimp/Synchronizer
/Mysqli.php on line 208
3. Method: listBatchSubscribe Done.
Thanks for your script.
I’m looking into it now and have some questions.
My goal is to sync my db to mailchimp, including some fields and groups (1000 users first stage, 5000 second stage).
http://www.mailchimp.com/api/examples/sync-you-to-mailchimp.php
says: “Under NO circumstances should you setup a process that tries to sync EVERY on of your users to us on a regular basis!”
I understand their reasons for writing this, but I also see they linked to your script from their site. Do I understand them wrong, or isn’t that colliding with your script’s concept? (assuming once a day sync operation).
thanks again
This script actually doesn’t sync the entire db every time. It unsubscribes anyone that’s no longer in your local list and then subscribes everyone who is on your local list and is not on the MailChimp list, so you’re only transmitting NEW subscribers (and unsubscribing deleted users), not all of them.
Hi Chris
I am stuck and really appreciate you offering this script free!
I am not sure if I am missing something but I have changed all the fields suggested but I keep getting this error:
Fatal error: Call to a member function bind_param() on a non-object in /home/worldofn/public_html/ccc/Galahad/MailChimp/Synchronizer/Mysqli.php on line 203
What am I doing wrong?
Regards
Your userExistsQuery probably has a syntax error which is not letting Mysqli prepare it as a statement. Make sure that a) the query works if you manually replace the parameters, and b) it’s not using any functionality that cannot be used in a prepared statement (for example, you can’t do something like “LIMIT ?” and bind that).
Hi Chris,
I’m getting the following error but am not familiar with mysqli so don’t really understand it.
I’ve checked the $email passed to the function is valid. Do you have any ideas?
Fatal error: Call to a member function bind_param() on a non-object in /home/transpor/public_html/Galahad/MailChimp/Synchronizer/Mysqli.php on line 203
Your user exists statement probably either has a syntax error or is not compatible with prepared statements in it. Try debugging there (see above).