Drupal to Processwire Notes and Snippets

vim-html-code

These notes are for a project converting a relatively complex drupal 6 site to processwire.  This particular site had a number of relatively complex interrelationships between the data that had to be accounted for. It had also come through multiple versions of drupal over the years had some cleaning up to do.

Because of the amount of data that needed to be cleaned wide use of csv files was more useful than a straight database to database transfer.  More will be added to this later.

SQL Snippets

  • Creating current drupal taxonomy links on pages to their processwire counterparts (some other work before this with views exports, csv imports and then creating a temporary file holding the relationships.  The excel commands =SPLIT(A1;”,”) and =TRANSPOSE(A1:Z1) to convert the views csv export to rows is helpful.
  • UPDATE conditions_patterns_temp INNER JOIN field_title ON conditions_patterns_temp.Conditions = field_title.data SET conditions_patterns_temp.cond_id = field_title.pages_id
  • Updating urls from a temp table in phpmyadmin
  • UPDATE pages INNER JOIN field_title ON pages.id = field_title.pages_id INNER JOIN temp_iherb_functions ON field_title.data = temp_iherb_functions.data SET pages.name = temp_iherb_functions.url
  • Build a table of page ids to create relationships later
  • UPDATE temp_fherb_ingredients INNER JOIN field_fherb_chinese_name ON temp_fherb_ingredients.formulaname = field_fherb_chinese_name.data SET temp_fherb_ingredients.formulaid = field_fherb_chinese_name.pages_id

Excel Snippets

  • Since this is an old drupal site (pre d7 fields) there are many times where you have to split a body into what will become multiple fields.  In the csv via google sheets the following may be useful:
  • A1 part1:part2
  • B1  =RIGHT(A1,LEN(A1)-Find(“:”,A1)) = part2
  • C1  =SUBSTITUTE(A1,B1,””) = part1:
  • A1 part1>part2
  • B1  =LEFT(A2,FIND(“>”,A2)-1) = part1
  • C1 = SUBSTITUTE(A2,B2,””) = >part2

PHP Snippets

  • This is a small script to enumerate through an array that has various mappings and set the sort order column so that you can create fields with multiple relationships in processwire:

  • $query = $database->prepare("SELECT formulaid, indherbid, sortorder FROM temp_fherb_ingredients");
    $query->execute();
    $result = $query->fetchAll();
    if (count($result)) {
    foreach($result as $row) {
    $formulaid = $row['formulaid'];
    $indherbid = $row['indherbid'];
    if ($justusedid != $formulaid) { $cnumber = 0; } else { $cnumber++; }
    $sortorder = $row['sortorder'];
    $justusedid = $formulaid;
    $query = $database->prepare("UPDATE temp_fherb_ingredients SET sortorder = $cnumber WHERE formulaid = $formulaid AND indherbid = $indherbid");
    $query->execute();
    }
    }

Postfix Relay Setup With Drupal and Basic Postfix Management

vim2-html

This article covers some basic steps to install the postfix mail server as a relay on Ubuntu, get Drupal to work properly in all mail environments, plus some general postfix management commands. These instructions may have to be adapted slightly to your environment and linux distribution, but they are generally applicable.

For more help in getting an intial server setup, please read Ubuntu Setup on a Linode VPS, and our PHP, Mysql, Apache Setup on Ubuntu articles.

  1. Postfix Installation
  2. Postfix Configuration Notes (As A Relay)
  3. Relaying Postfix Mail From Root – Localhost
  4. Drupal Email Configuration for Restrictive SMTP Servers
  5. Postfix Queue Management Commands

1. Postfix Installation

Below are the basic installation steps for postfix on an ubuntu server (many have it already installed). You can also use the drupal smtp module for this function but for high volume mailing and/or better control a local relay, such as postfix, is often helpful.

sudo apt-get install postfix If already installed you can reconfigure it by issuing the following command:
sudo dpkg-reconfigure postfix
Then enter the following for our purposes of setting up a relay:

Choose Satellite Configuration
System Mail Name: your maildomain name
SMTP relay host:mail.authsmtp.com (we often use authsmtp, but whatever your ISP smtp server is wold go here)
Root and Postmaster mail recipient: webmaster@yourdomain.com
Other destinations to accept mail for: localhost.localdomain,localhost (accept defaults)
Force synchronous updates on mail queue: No, unless you know what you are doing
Local Networks: defaults
Procmail for local delivery: Yes
Mailbox size limit: 0
Local address extension character: +
Internet Protocol:All

2. Postfix Configuration

If you ran the reconfigure command above you are most of the way there, but you should edit your config file anyhow and look over the following. It should look like this:

sudo nano /etc/postfix/main.cf

myhostname = www.mydomain.com
mydomain = mydomain.com
myorigin = localhost
smtpd_banner = $myhostname ESMTP $mail_name
biff = no
append_dot_mydomain = no
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
mydestination = localdomain, localhost, localhost.localdomain, localhost
mynetworks = 127.0.0.0/8
mailbox_size_limit = 0
recipient_delimiter = +
# SECURITY NOTE: Listening on all interfaces. Make sure your firewall is
# configured correctly
inet_interfaces = loopback-only
relayhost = mail.authsmtp.com ## whatever your is
smtp_connection_cache_destinations = mail.authsmtp.com
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = static:username:password
smtp_sasl_security_options = noanonymous
default_destination_concurrency_limit = 4 ## to stay within your ISP’s policy
soft_bounce = yes
inet_protocols = all

Then make sure your aliases are ok:

You then want edit your /etc/aliases file (sudo nano /etc/aliases) and fill it out for your needs:

# See man 5 aliases for format
postmaster:    root

After this you can run:

sudo newaliases

And finally restart postfix

sudo /etc/init.d/postfix restart

3. Relaying Postfix Mail From Root – Localhost

As we have setup postfix above to relay all mail to an external smtp server you will lose mail from root – or root@localhost accounts.  To remedy this you can setup an alias via the following steps:

# cd /etc/postfix
# touch canonical
# echo "sender_canonical_maps = hash:/etc/postfix/canonical" >> /etc/postfix/main.cf
# echo "root allowedsender@anotherdomain.com" > /etc/postfix/canonical
# echo "root@localhost allowedsender@anotherdomain.com >> /etc/postfix/canonical
# postmap /etc/postfix/canonical
# /etc/init.d/postfix restart

To Test:
# echo TEST | mail -s TEST testemail@anotherdomain.com

4. Drupal Email Configuration for Restrictive SMTP Servers

By default, drupal doesn’t work well with ISP that require appropriately formed From and Reply-To Headers. There are issues in the queue to have this work properly in version 7 but for 5 and 6 you will need to edit the core contact module (sites/modules/contact.module).

Below is a copy of the patch file for version 5 of drupal, but it is very close to 6. It is best to edit the file yourself rather than try to submit this as a patch as we have other edits in the file and the line numbers may not be correct for you.

+++ contact.module 2009-01-29 20:48:45.000000000 -0500
@@ -418,10 +418,17 @@ function contact_mail_user_submit($form_
// Prepare all fields:
$to = $account->mail;
if ($user->uid) {
–    $from = $user->mail;
+//    $from = $user->mail;
+// Changed To Set Proper From and Reply-To And Add New Variable For User Copy
+ $usercopy = $user->mail;
+ $from = variable_get(‘site_mail’,”);
+ $headers = array(‘Reply-To’ => $user->mail);
}
else {
–    $from = $form_values[‘mail’];
+//    $from = $form_values[‘mail’];
+// Changed To Set Proper From and Reply-To
+ $from = variable_get(‘site_mail’,”);
+ $headers = array(‘Reply-To’ => $form_values[‘mail’]);
}

// Format the subject:
@@ -431,11 +438,14 @@ function contact_mail_user_submit($form_
$body = implode(“nn”, $message);

// Send the e-mail:
–  drupal_mail(‘contact-user-mail’, $to, $subject, $body, $from);

+//  drupal_mail(‘contact-user-mail’, $to, $subject, $body, $from);
+//  Changed To Add Headers
+ drupal_mail(‘contact-user-mail’, $to, $subject, $body, $from, $headers);
// Send a copy if requested:
if ($form_values[‘copy’]) {
–    drupal_mail(‘contact-user-copy’, $from, $subject, $body, $from);
+//    drupal_mail(‘contact-user-copy’, $from, $subject, $body, $from);
+//  Changed To Add Headers
+    drupal_mail(‘contact-page-copy’, $from, $subject, $body, $from, $headers);
}

5. Postfix Queue Management Commands

Now that you have everything working correctly, here are some useful postfix commands

# mailq  (show items in the queue – when you are relaying this is generally failed items)
# postcat -q queueid (read the contents of a message in the queue)
# postqueue -f (try to deliver all items in the queue immediately)
# postsuper -d ALL (remove all items in the queue)

Helpful SQL Queries Commands For Drupal

vim2-html

Simplifying mass updates and changes to websites is one of the main reasons to use a database driven content management system like drupal.  While drupal has a variety of helpful tools for automating tasks there are many times where it is easier and some where it is your only option to make changes to the database directly.  Below is a list of mysql commands that we use occasionally to create changes and make updates to our sites.  They are here largely for reference, please do not use them without knowing what they mean and without a backup of your database.

  • General Search and Replace Function
  • UPDATE tablename SET tablefield = replace(tablefield,”findstring”,”replacestring”) WHERE type = “x”
  • Example:  Create mass changes in drupal node body fields

    UPDATE `node_revisions` SET `body` = replace(body,’texttofind’,’texttoreplacewith’)

 

  • Create a spreadsheet of your drupal users usernames, emails and uid (we use this at times to create email newsletter subscription lists as an example)
  • This should be done in phpmyadmin so you can easily export the results in a CSV file for import, say into Mailchimp…
  • Example:  Select the desired user information from users with a specific role
  • SELECT users.name, users.uid, users.mail FROM (users LEFT JOIN users_roles ON users.uid = users_roles.uid) WHERE users_roles.rid = “3” ORDER BY users.uid

Customizing Drupal User Profiles

vim-html-code

By default, the drupal content management system offers fairly plain looking user profiles. For people using drupal in a social networking setting and/or in a content publishing situation with numerous authors that you would like to highlight, most people will want to improve upon the standard user profile layout.

While there are a number of user profile related modules, this article will cover some php coded methods that will help to accomplish some of the more common uses.

The first step in customizing the user profile is to create a file called “user_profile.tpl.php” into your themes directory. Having this file will override drupal’s default layout. Within that file you can use a variety of php, sql, and html code to achieve what you are looking for. Once you get all of the data you want in the profile you can then customize it with CSS – adding rounded boxes around certain content, etc.

The example that I will be describing today is from an acupuncture information site that I created. An example of a detailed user profile is here.

First, I’ll list the code in general and then below I’ll break it apart and discuss certain sections in detail.

This site has different member roles, some are highlighted and certain functions are added to their profile based on what role they are in. For general users the profile is not particularly different from the standard drupal layout (with the addition of recent forum postings/comments). For other member roles, however, sections are added to their profile that don’t exist for other users.
<div class=”userlist”>

<?php

/* *** */

/* If the user has a picture picture, print it */

/* *** */

if($user->picture): ?>

<img src=”/<?php print $user->picture ?>” alt=”personal picture” align=”right” class=”pictureframe”>

<?php endif; ?>

<?php

/* *** */

/* If premium student members, say so */

/* *** */

if (in_array(‘student member’, $user->roles)) {

print ‘<div class=”practitionerbold”><p>I am a premium <a href=”/student_members/become_a_student_member”>Student

Member</a>.</p><div class=”item-list”><ul><li><strong>Read</strong> <a

href=”/student_members/eastern_medicine_contributions”>Student Member</a> Contributions</li><li><a

href=”/user/$userid/contact”>Contact Me</a></li></ul></div></div>’;

} ?>

<?php

/* *** */

/* If premium practitioner member, create a link to their individual details page */

/* This is a cck node that users can create in certain roles to describe more details about themselves and their work */

/* They are only allowed to create one */

/* *** */

$nlimit = 1;

$userid=$user->uid;

$result1 = pager_query(db_rewrite_sql(“SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 AND n.uid = $userid AND

n.type = ‘content_about_my_practice’ ORDER BY n.created DESC”), variable_get(‘default_nodes_main’, $nlimit));

while ($node = db_fetch_object($result1))

{$setpracpage .= node_view(node_load(array(‘nid’ => $node->nid)), 1);}

if (in_array(‘practitioner member’, $user->roles)):

print ‘<div class=”practitionerbold”><p>I am <a href=”/directory/get_listed”>listed</a> in the <a

href=”/directory”>Practitioner Directory</a><br></p><div class=”item-list”><ul>’;

if (isset($setpracpage)): ?>

/* *** */

/* Below are just some individual user accounts that we would like to highlight by adding fields to the profile */

/* *** */

<?php if ($userid == “2”) {

print ‘<li>I Am <strong>On Staff</strong> At The Yin Yang House <a href=”/clinic

/chattanooga_acupuncture_and_wellness_center”>Acupuncture &amp; Wellness Center<

/a> In <a href=”/clinic/yinyanghouse_directions_contacts”>Chattanooga, TN</a>.</

li>’;

} ?>

<?php if ($userid == “840”) {

print ‘<li>I Am A Massage Therapist <strong>On Staff</strong> At The Yin Yang Ho

use <a href=”/clinic/chattanooga_acupuncture_and_wellness_center”>Acupuncture &a

mp; Wellness Center</a> In <a href=”/clinic/yinyanghouse_directions_contacts”>Ch

attanooga, TN</a>.</li>’;

} ?>

<?php if ($userid == “841”) {

print ‘<li>I Am A Massage Therapist <strong>On Staff</strong> At The Yin Yang Ho

use <a href=”/clinic/chattanooga_acupuncture_and_wellness_center”>Acupuncture &a

mp; Wellness Center</a> In <a href=”/clinic/yinyanghouse_directions_contacts”>Chattanooga, TN</a>.</li>’;

} ?>

/* *** */

/* Add the individuals detail page if it exists */

/* *** */

<?php

print ‘<li>Read <strong>Details About</strong>&nbsp;’;

$node_type = “content_about_my_practice”;

$list_no =1;

$sql = “SELECT node.title, node.type, node.nid FROM node WHERE node.type = ‘$node_type’ AND node.uid = ‘$userid’ LIMIT

$list_no”;

$result2 = db_query($sql);

while ($anode = db_fetch_object($result2)) {

$output2 .= l(“My Practice”, “node/$anode->nid”);

}

print $output2;

?></li><?php endif; ?>

/* *** */

/* Add Contact Link */

/* *** */

<li><a href=”/user/<?php print $userid; ?>/contact”><strong>Contact Me</strong><

/a></li><?php if($user->profile_dirlisting): ?>

<li><strong>Return</strong> to the <?php print $user->profile_dirlisting ?> Dire

ctory</li><?php endif; ?></ul></div></div>

/* *** */

/* Customized News and Event Postings (a member role limited content type) */

/* Show 5 most recent for this user */

/* *** */

<br>

<div class=”clinicbold”>

<?php

$uid = arg(1);

$nlimit = 5;

$result = db_query(“SELECT n.created, n.title, n.nid, n.changed

FROM node n

WHERE n.uid = $uid

AND n.status = 1

AND n.type = ‘practitioner_member_news’

ORDER BY n.changed

DESC LIMIT $nlimit”);

$output4 .= “<div class=”item-list”><ul>n”;

$output4 .= “<li><strong>My Recent News and Event Postings:</strong></li>n”;

$output4 .= node_title_list($result);

$output4 .= “</ul></div>”;

print $output4;

?>

/* *** */

/* Customized Article Postings (a member role limited content type) */

/* Show 5 most recent for this user */

/* *** */

<?php

$uid = arg(1);

$nlimit = 5;

$result = db_query(“SELECT n.created, n.title, n.nid, n.changed

FROM node n

WHERE n.uid = $uid

AND n.status = 1

AND n.type = ‘practitionerarticle’

ORDER BY n.changed

DESC LIMIT $nlimit”);

$output5 .= “<div class=”item-list”><ul>n”;

$output5 .= “<li><strong>My Recent Article Submissions:</strong></li>n”;

$output5 .= node_title_list($result);

$output5 .= “</ul></div></div>”;

print $output5;

?>

<?php endif; ?>

/* *** */

/* Below is the standard membership information that will show up on every users account */

/* This will read in some profile fields that are available at signup and show their most */

/* recent forum postings and comments */

/* *** */

<br>

<div class=”clinicbold”>

<div class=”item-list”>

<ul>

<li><strong>Yin Yang House Membership Information:</strong><hr></li>

<li><strong>I Am:  </strong><?php print check_plain($user->profile_userlevel) ?><br><br></li>

<li><strong>From:  </strong><?php print check_plain($user->profile_usercountry) ?><br><br></li>

<li><strong>My Interests:  </strong><?php print check_plain($user->profile_userinterests) ?><br><br></li>

<li><strong>YYH Member for:  </strong><?php print (format_interval(time() – $user->created));?><br></li>

</ul></div>

<br><hr>

/* *** */

/* Most Recent Forum Postings and Site-Wide Comments */

/* *** */

<?php

$uid = arg(1);

$nlimit = 5;

$result = db_query(“SELECT n.created, n.title, n.nid, n.changed

FROM node n

WHERE n.uid = $uid

AND n.status = 1

AND n.type = ‘forum’

ORDER BY n.changed

DESC LIMIT $nlimit”);

$output3 .= “<div class=”item-list”><ul>n”;

$output3 .= “<li><strong>My Recent Comments and Forum Postings:</strong></li>n”;

$output3 .= node_title_list($result);

print $output3;

/* *** */

/* Most Recent User Comments that Have been Approved and are Published */

/* *** */

$output = “”; $nlimit = 5;

$userid=$user->uid;

/* *** */

/* ATTENTION: status=0 – approved, status=1 in queue. */

/* *** */

$query= “SELECT c.cid, c.nid, c.name, c.subject FROM {comments} c WHERE c.uid = %d AND c.status = 0

ORDER BY c.timestamp DESC”;

$result = db_query_range($query,$userid,0,$nlimit);

$no_comments = mysql_affected_rows ();

if ( $no_comments > 0 ) {

while ($obj = db_fetch_object($result)) {

$link = url(“node/$obj->nid”);

$link = $link.”#comment-“.$obj->cid;

$output6 .= “<li><a href=”$link”>$obj->subject</a>”;

}

}

$output6 .= “</ul></div></div>”;

print $output6;

?>

</div>

Install Or Move Drupal To A Linux VPS

vim2-html

This article covers some basic steps to move an existing installation of Drupal or to create a new installation of Drupal on a linux VPS – in this case one running Ubuntu hosted with Linode. These instructions assume some configurations that we have performed in previous articles, so your individual configuration may vary.

The related articles are our Ubuntu Setup on a Linode VPS, and our PHP, Mysql, Apache Setup on Ubuntu articles.

Your particular situation may vary, but these general steps are common to most situations. These steps are related more to creating databases, moving data around, etc. and do not cover the installation of Drupal in any depth. The Drupal guidebooks have detailed information on this, and the procedure is fairly straightforward (i.e. create the database, copy the files to your location, and edit the settings file with your db username and password).

1. Setup The MySQL Database For Your Site
sudo mysql -u root -p
CREATE DATABASE name;
GRANT ALL PRIVILEGES ON dbname.* TO "dbuser" IDENTIFIED BY "dbpass";
FLUSH PRIVILEGES;

2. Copy Your Database Files (if you are moving an existing site, otherwise the empty database is fine) and your Drupal files to your site:
scp -r -P yoursshport /home/myaccount/mysites/site1 username@yourip:~/webfiles

scp -r -P 30101 /home/myaccount/mysites/site1.sql username@yourip:~/webfiles

3. In case you don’t already have a database dump from a previous host and just for reference. Here is how you peform and database backup and restore for Drupal (and any other database)
mysqldump -u username -p database > file.sql
mysql -u username -p database < file.sql

4. Connect to your server and upload the database file you just copied over:

ssh -P yoursshport yourusername@yourip
mysql -u username -p databasename < /home/websites/site1.sql

5. Copy your web files to the appropriate directory:
cp -r /webfiles* /srv/hostname.com/public

6. Final Apache Changes for Drupal .htaccess to work
In /etc/apache2/domainname.com.conf make sure these sections match:

  <Directory /srv/domainname/public/>
                Options Indexes FollowSymLinks +Includes
                AllowOverride All
                Order allow,deny
                allow from all
  </Directory>

Drupal and other CMS’s require cron jobs to perform a range of maintenance tasks. The following sets up the crontab to run drupal cron application to perform the necessary system updated. The following lines will run it every 6 hours at 3 minutes past the hour. You can modify this according to your needs (hourly, daily, etc.).

7. Drupal Cron Setup for domain1

  sudo crontab -e
  3 */6 * * * /usr/bin/wget -O – -q http://domain1.com/cron.php
  sudo crontab -l