SQL | Developer of Toronto's Open Source Web

22nd Mar2012

Newsletter subscription in CIVICRM 4 – Drupal 7 combo

by admin

LeviaIT recently got me working on a very exciting project, modifying CIVICRM – Drupal combo to work for a certain niche market. And while i heard and read about CIVICRM before this was the first time working with it. And while everyone talks about CIVICRM and Drupal working nicely together, that it up to the point of them actually “working” together…

So one of the issues i came upon was lack of subscription form for the newsletter module built in in the CIVICRM. The inherent issue was more then just a missing form for integration, since CIVICRM newsletter module is meant to track who you send the newsletters to and what happens after that… that means that if anonymous user wants to signup to the newsletter, he would / should force him to fill out the full registration form first, which would be doable, but really annoying. So i wanted to share our solution:

Add a custom field in user, with a yes / no selection (could be done by going to yoursite.com/civicrm/admin/custom/group?reset=1 )
Next step is to create a smart group (smart groups automatically add users to a group based on a search criteria), based on if the value for “Receive the Newsletter” is “Yes”.
Create a new webform with an email field. (check what the webform id is you will need it for the next step)
wright a custom drupal module to do the rest of the magic. (explained below)

Now lets get to the module part:

As usual the first step is to create the hooks.info file:

name = Hooks
description = Implement CiviCRM and Drupal hooks specific to hineni
package = CiviCRM
dependencies[] = civicrm
dependencies[] = webform
core = 7.x
version = 0.1

name = Hooks

description = Implement CiviCRM and Drupal hooks specific to hineni

package = CiviCRM

dependencies[] = civicrm

dependencies[] = webform

core = 7.x

version = 0.1

Next is the hooks.module file:

<!--?php</p-->

/**
* Implementation of hook_form_alter().
*/
function hooks_form_alter(&amp;$form, &amp;$form_state, $form_id) {
// Add validation for a particular Webform node:
if ($form_id == 'webform_client_form_8') {
// Add the submit handler after the existing Webform submit handler,
// but before the second Webform handler. Pop off the first one and add
// ours second.
$first = array_shift($form['#submit']);
array_unshift($form['#submit'], $first, 'hooks_submit_8');
}
}

/**
* Submit handler for Webform ID #8.
*/
function hooks_submit_8(&amp;$form, &amp;$form_state) {
// Insert things into other database tables or modify properties.

$submitted_tree = $form_state['values']['submitted_tree'];
$submitted_email = $submitted_tree['e_mail'];

// see if user email already in table
$rs = db_query("SELECT contact_id
FROM 'civicrm_email'
WHERE 'contact_id' IS NOT NULL
AND 'email' LIKE '$submitted_email'");
$contact = $rs-&gt;fetchObject();
if($contact-&gt;contact_id != ''){ // if in table

// get account id
$account_id = $contact-&gt;contact_id;

// pull up subscription table
$subscrip_table = db_query("SELECT id,'entity_id','would_you_like_to_receive_our_ne_69'
FROM 'civicrm_value_subscribe_to_newsletter_18'
WHERE 'entity_id' = '$account_id'");
$subscription = $subscrip_table-&gt;fetchObject();
if($subscription-&gt;id == ""){
// if user not in table, add contact to subscriptions
db_query("INSERT INTO 'civicrm_value_subscribe_to_newsletter_18'
('entity_id', 'would_you_like_to_receive_our_ne_69')
VALUES ('$account_id','1')");
}
elseif($subscription-&gt;id &gt; 0 &amp;&amp; $subscription-&gt;civicrm_value_subscribe_to_newsletter_18 == 0){
// if is in table but not subscribed, enable subscription
db_query("UPDATE 'civicrm_value_subscribe_to_newsletter_18'
SET 'would_you_like_to_receive_our_ne_69'='1'
WHERE 'id' = '$subscription-&gt;id'");
}
}
else { // if not in table

// create empty contact, filling in 'email_greeting_display' temporarily with user's email
db_query("INSERT INTO 'civicrm_contact'('contact_type','email_greeting_display') VALUES ('Individual','$submitted_email')");

// retrieving id of contact just created using email from 'email_greeting_display' column
$rs = db_query("SELECT id
FROM 'civicrm_contact'
WHERE 'email_greeting_display'
LIKE '$submitted_email'");
$contact = $rs-&gt;fetchObject();
$account_id = $contact-&gt;id;

// removed temporary storage of email address in 'email_greeting_display'
db_query("UPDATE 'civicrm_contact'
SET 'email_greeting_display' = NULL
WHERE 'id' = $account_id");

// add email address to email table
db_query("INSERT INTO 'civicrm_email'('contact_id', 'email', 'is_primary')
VALUES ('$account_id','$submitted_email','1')");

// add contact to subscription table
db_query("INSERT INTO 'civicrm_value_subscribe_to_newsletter_18'
('entity_id', 'would_you_like_to_receive_our_ne_69')
VALUES ('$account_id','1')");
}
}
?&gt;

/**

* Implementation of hook_form_alter().

function hooks_form_alter(&$form, &$form_state, $form_id) {

// Add validation for a particular Webform node:

if ($form_id == 'webform_client_form_8') {

// Add the submit handler after the existing Webform submit handler,

// but before the second Webform handler. Pop off the first one and add

// ours second.

$first = array_shift($form['#submit']);

array_unshift($form['#submit'], $first, 'hooks_submit_8');

}

/**

* Submit handler for Webform ID #8.

function hooks_submit_8(&$form, &$form_state) {

// Insert things into other database tables or modify properties.

$submitted_tree = $form_state['values']['submitted_tree'];

$submitted_email = $submitted_tree['e_mail'];

// see if user email already in table

$rs = db_query("SELECT contact_id

FROM 'civicrm_email'

WHERE 'contact_id' IS NOT NULL

AND 'email' LIKE '$submitted_email'");

$contact = $rs->fetchObject();

if($contact->contact_id != ''){ // if in table

// get account id

$account_id = $contact->contact_id;

// pull up subscription table

$subscrip_table = db_query("SELECT id,'entity_id','would_you_like_to_receive_our_ne_69'

FROM 'civicrm_value_subscribe_to_newsletter_18'

WHERE 'entity_id' = '$account_id'");

$subscription = $subscrip_table->fetchObject();

if($subscription->id == ""){

// if user not in table, add contact to subscriptions

db_query("INSERT INTO 'civicrm_value_subscribe_to_newsletter_18'

('entity_id', 'would_you_like_to_receive_our_ne_69')

VALUES ('$account_id','1')");

}

elseif($subscription->id > 0 && $subscription->civicrm_value_subscribe_to_newsletter_18 == 0){

// if is in table but not subscribed, enable subscription

db_query("UPDATE 'civicrm_value_subscribe_to_newsletter_18'

SET 'would_you_like_to_receive_our_ne_69'='1'

WHERE 'id' = '$subscription->id'");

}

else { // if not in table

// create empty contact, filling in 'email_greeting_display' temporarily with user's email

db_query("INSERT INTO 'civicrm_contact'('contact_type','email_greeting_display') VALUES ('Individual','$submitted_email')");

// retrieving id of contact just created using email from 'email_greeting_display' column

$rs = db_query("SELECT id

FROM 'civicrm_contact'

WHERE 'email_greeting_display'

LIKE '$submitted_email'");

$contact = $rs->fetchObject();

$account_id = $contact->id;

// removed temporary storage of email address in 'email_greeting_display'

db_query("UPDATE 'civicrm_contact'

SET 'email_greeting_display' = NULL

WHERE 'id' = $account_id");

// add email address to email table

db_query("INSERT INTO 'civicrm_email'('contact_id', 'email', 'is_primary')

VALUES ('$account_id','$submitted_email','1')");

// add contact to subscription table

db_query("INSERT INTO 'civicrm_value_subscribe_to_newsletter_18'

('entity_id', 'would_you_like_to_receive_our_ne_69')

VALUES ('$account_id','1')");

}

Please be aware though that there is a couple of variables that you will need to find in your civicrm database and substitute those values in the code the list is as following:

Webform ID as mentioned above – in my example the is was “webform_client_form_8″
Look into database to find out the name of the table where the values for our custom nesletter field are stored – in our example the table name is “civicrm_value_subscribe_to_newsletter_18″
In the above table the last collumn will be called something like this – “would_you_like_to_receive_our_ne_69″

Now all you need to do is substitute these values with values from your CIVICRM database, upload and activate the module. Your newsletter webform is ready!

Category : Blog, CiviCRM, Drupal, Drupal, Modules, Portfolio

Tags : CiviCRM, Content management systems, Drupal, HTML element, mysql, newsletter signup form, non for profit, Open source, SQL, webform

6 Comm

29th Dec2009

Couple of things I learned while creating sites in Drupal

by admin

I am now creating my 5-th site in Drupal, three of them being pretty big and totally custom. So using a free or even premium theme was not an option. Each section of the site looked so much different that if i would cover logo and name of the site, you would probably think twice before saying thats its the same site. Obviously i made a lot of mistakes, but as they say “The best way to learn is from your mistakes”. So let me share with you some of my mistakes and what i learned from them, plus some of the modules that i found really useful.

Starter Theme

Start with free blueprint theme this will give you a starting point so that you will have all the elements on the page but without actually having any design, so that when you start putting in your design you don’t have to fight with legacy code from the theme.

Admin

Next very important step is going to be to install Admin Menu. This module is so important that it was included in core of Drupal 7.

Javascript

As i mentioned earlier the sites i was working on were very custom they were using: jQuery, Cufon, Accordion, Ajax, Thickbox, In-Field Labels… The version of jQuery that comes prepackaged with Drupal is old. So what i did is just put the latest version in the theme… I got it to work but not without having multiple versions of jQuery being included on every page, which in turn forced me to use “jQuery no conflict mode”. Even though we got it to work eventually, every time you add any module that uses jQuery i have to go through the javascript and substitute “$” sign with “jQuery”. So obviously there must be a better way. An indeed there is, what i could, and should, have done is install jQuery Update module, which would update the version in a “Drupal” way. Also there is a jQuery UI Module which contains:

Accordion
Datepicker
Dialog
Progressbar
Slider
Tabs

There is also a Cufon Module, Thickbox Module etc. So whatever you trying to do try to find a Drupal way of doing it.

Theming the site

There are few must have tools (Modules) in this area. You will need Devel Module to see whats going on under the hood of your drupal site what templates and what functions are used and which names you could use to override those. Another module that i find myself using a lot is Views. I use it for two entirely different purposes, one to create a custom block or a page. Another use is not so “Drupal”, sometimes you have no other choice but to stick in a SQL query into your theme, but the problem you will encounter is Drupal is so flexible and generic that sometimes to get one entry from the database you will need to connect and join three, four or even five tables, so to write the SQL query by hand will take you a while, here is where my second use of Views comes into play, there is an option to show you the SQL query the view runs while generating content you requested, so you could just copy it and stick it into your theme.

Other Modules i use:

Token – Used mostly by other modules
imageapi – Used by other modules
Disqus – substitutes the default comment system with the one from Disqus which lets your readers comment by login in with their Twitter, Facebook login or others, plus it lets you have the conversation all across the web, i find it truly amazing.
CCK – Lets you add custom fields aside from title and body
- filefield – File type of CCK Field
- imagefield – Image type of CCK Field
imagecache – Lets you show the same image in variety of sizes, based on presets
Poormanscron – runs the cron jobs for you (will be included in core of Drupal 7)
pathauto – automatically generates aliases
globalredirect – makes sure that node/10 takes you to node10alias, so that there is only one URL to unique content
webform – If you are planning to have any forms on your site
- webformblock – If you want to show the form in a block, versus a whole page

As i continue to make more mistakes (read learn) i will continue to share more lessons i learned. If there is anything you would like to share or ask, feel free to do so in the comments.

If you liked this post you might want to read the part 2.