Worship Techie

This is an update to this post now that WordPress 3 has come out.

I haven’t thoroughly tested this, but it’s working for me now. Also, this is for a multisite installation, so there are some tables that won’t exist if you have a single site.

SET @oldURL = 'oldsite.com';
SET @newURL = 'newsite.com';
update wp_comments set comment_content = replace(comment_content, @oldURL, @newURL);
update wp_comments set comment_author_url = replace(comment_author_url, @oldURL, @newURL);
update wp_postmeta set meta_value = replace(meta_value, @oldURL, @newURL);
update wp_posts set post_content = replace(post_content, @oldURL, @newURL);
update wp_options set option_value = replace(option_value, @oldURL, @newURL);
update wp_links set link_url = replace(link_url, @oldURL, @newURL);
update wp_blogs set domain = replace(domain, @oldURL, @newURL);
update wp_site set domain = replace(domain, @oldURL, @newURL);
update wp_sitemeta set meta_value = replace(meta_value, @oldURL, @newURL);
update wp_usermeta set meta_value = replace(meta_value, @oldURL, @newURL);

Just be sure to change the @oldURL and @newURL variables to your domains. Also, in your wp-config.php, don’t forget to change the line that says:

define( 'DOMAIN_CURRENT_SITE', 'yourdomain.com' );

to the new domain.

Hope this helps. Drop a comment if I missed anything.

This is a follow-up post to this.

First off, if you read that post and immediately stopped doing anything other than praying to get volunteers, I apologize. I think I was only half right. I was just being lazy for the Lord.

As great as that sounds, I was missing something. God causes the rain, but we still have to sow the seeds.

So lately I’ve added to my prayers a very targeted ad in the bulletin (“Looking for people with Adobe Creative Suite experience…”), and we’ve brought on three or four new volunteers in the last few weeks.

Let’s say you are moving your WordPress site from ‘example.com/blog’ to ‘example.com’. In your options panel, you can change your base URL and your site will more or less work, but any internal links inside of posts (including images) will be broken.

Here’s a quick SQL snippet I use for moving my WordPress sites to a new URL.

SET @oldURL = 'example.org/blog';
SET @newURL = 'example.org';
update wp_comments set comment_content = replace(comment_content, @oldURL, @newURL);
update wp_comments set comment_author_url = replace(comment_author_url, @oldURL, @newURL);
update wp_postmeta set meta_value = replace(meta_value, @oldURL, @newURL);
update wp_posts set post_content = replace(post_content, @oldURL, @newURL);
update wp_options set option_value = replace(option_value, @oldURL, @newURL);

Change the URLs on the first two lines, then copy and past that into PHPMyAdmin and you’ll be good to go!

PS- If I’m missing anything, let me know in the comments.

This tweet sparked a lot of conversation this morning:

I got a lot of feedback, and it all seemed to land in two camps: either you love your avioms or you hate them.

Here’s some questions that would be great to have answered in the comments:

1) Which camp are you in? Love ‘em or Hate ‘em?

2) Does your entire band use them, or just some musicians?

3) How big is your room?

4) Analog or digital console?

5) How often does your input list change?

6) How do you connect your avioms? In-ears plugged into the back? Plugged direct into a powered wedge? Sent back to FOH console to route to IEMs or wedges? Something else?

I don’t remember who told me this, but it struck me. You never see people in the Bible doing volunteer fairs or anything like that to try to drum up volunteers. They prayed, and God provided what they needed.

While I can’t say that I’ve never done a bulletin announcement, I can say that every time I’ve had a real need for a volunteer (or an entire volunteer team), God has always provided them. My general philosophy is this: Once God stirs in a persons heart for them to volunteer, it’s my responsibility to train them, schedule them, and try to create an environment that makes them want to stick around (team-building, clear-communication, building friendships etc…). But I don’t even want a volunteer who has decided to help at his church for any reason other then God pushed them into it.

I’m sometimes afraid that this just comes across as an excuse to not spend time doing recruitment. What do you think?

I geeked out and bought the Motorola Droid last Friday. Overall it’s awesome. Here’s my thoughts on it, over my Posterous blog.

As the last post talked about, there’s several reasons that Alex and I decided to start over on the website. The second big factor was an entire new branding for Rancho.

Alex has been trying to update us from the old oak tree logo since the first day he was hired. Most of what he played with was iterations of the tree, but nothing ever really felt right. A logo is supposed to be a representation of who you are, and after months of working with our staff and pastors, Alex was able to capture that in a logo.

I wrote up a post over at tech.ranchocommunity.org talking about what the new brand meas

At ranchocommunity.org, which is currently being converted from a Drupal site to a WordPress site, all of our sermon content is stored in a custom content type created with CCK. PodsCMS is a plugin for WordPress that provides similar functionality. The trick was how would I convert the data? I certainly don’t want to re-input a couple year’s worth of sermons by hand. But since all that data is stored in MySQL databases, it is accessible. It’s just a matter of putting the right data in the right places. Here’s how I did it.

Note: I need to make a disclaimer here. This post is mostly a walkthrough of how I managed to do this, along with some bits of information that I learned along the way. If you plan on doing this yourself, you’re going to need to adapt just about everything to fit your needs. I’m going to walk you through the information that you’ll need to know first, then the script I used is available at the bottom of this post for you to use a starting point.

First, I used the Drupal Node Export module to export all of the sermons from Drupal. That gave me an array that looked something like this:

array(
  array(
  // *snip* Lots of data
  )
);

I assigned that to a variable that became the source of all our my Drupal content.

$nodes = array(
  array(
  // *snip* Lots of data
  )
);

My new pod type is called a ‘sermon’ and it looks like this:

In my WordPress database, there is a corresponding table called wp_pod_tbl_sermons:

See all of my fields (besides the picks, which we’ll get to in a bit) are there. The script takes the appropriate fields from $nodes and inserts them into the table.

There’s another table that we have to worry about called wp_pod. This is more of a master list of all of your pods, and it points back to a wp_pod_tbl_podtype table.

This table takes a little more work to fill in because a good chunk of the data isn’t in $nodes. First, tbl_row_id comes from the id of the corresponding row in wp_pod_tbl_sermons. I found that by selecting the newest row from that table.

$sql = "SELECT id FROM wp_pod_tbl_sermons ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql) or die (mysql_error());
$id = mysql_fetch_array($result);

datatype comes from the table wp_pod_types. Since I am only importing one type of pod, I just hardcoded this value to be set to the id of sermons, which was 3.

name and created come from $nodes, id and modified set themselves.

Now on to those picks that I mentioned earlier. My sermons link to two other pod types: series, and speakers. Pods stores all of the information for all of the picks, regardless of what pod type they’re in, in the table wp_pod_rel.

pod_id is a reference to a row in wp_pod. I got this information the same way I got the id from wp_pod_tbl_sermons:

$sql = "SELECT id FROM wp_pods ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql) or die (mysql_error());
$id = mysql_fetch_array($result);

field_id is a reference to which type of pick field we’re talking about, and the values are in the table wp_pod_fields. In my case there are two types, series and speakers, which have the ids 11 and 14 in wp_pod_fields. tbl_row_id references the row in the wp_pod_tbl_podtype that this sermon has “picked.”

Here’s what I did with this: The script looks at the current row in $node and finds the series for that sermon. Then there is a simple switch statement to decide what value to use for tbl_row_id based on what the series was. After it has those two bits of information, it adds another row to wp_pod_rel with field_id set to 11 and tbl_row_id set based off the switch statement.

That last process is then repeated for speakers, but this time field_id is set to 14, and tbl_row_id reference a row in wp_pod_tbl_sermons.

I’m not sure what sister_pod is all about. It was 0 on every one of my pods. It must be for some feature I haven’t found yet.

Here’s the cheat sheet I made of all the tables and fields I had to worry about.

And finally, the actual script. I hope this all made sense and is helpful. This could even be the ground work for a  more universally useful migration-script if someone (me?) gets around to writing it. Feel free to leave questions in the comments.

<?php

/*  Drupal CCK -> WordPress PodsCMS migration tool.
 *
 *	This is the script I used for my specific install. If you plan on using this,
 *	you will need to customize the heck out of it.
 *
 *	You can email me questions at danielmurphy02@gmail.com or leave a comment at
 *	worshiptechie.com . I'm also on Twitter: @worshiptechie.
 *
 */
?>
<pre>

<?php

// The drupal node export is being assigned to $node in a separate file.
include('migrate_data.php');

$con = mysql_connect('localhost:8889','root','root');
if (!$con) die ('could not connect: '. mysql_error());
mysql_select_db("wordpress");

// $wp_pod_tbl_sermons gets populated, then inserted as a new row into
// wp_pod_tbl_sermons.
foreach ($nodes as $row) {
$wp_pod_tbl_sermons['name'] = $row['title'];
$wp_pod_tbl_sermons['body'] = ($row['body'] != "") ? $row['body'] : $row['title'];
$wp_pod_tbl_sermons['mp3']  = ($row['field_sermon_mp3'][0]['filename'] != "")
? "/wp-content/messages/audio/".$row['field_sermon_mp3'][0]['filename']
: "";
$wp_pod_tbl_sermons['m4a']  = ($row['field_sermon_m4a'][0]['filename'] != "")
? "/wp-content/messages/audio/".$row['field_sermon_m4a'][0]['filename']
: "";
$wp_pod_tbl_sermons['video']= ($row['field_sermon_video_link'][0]['value'] != "")
? $row['field_sermon_video_link'][0]['value']
: "";
$wp_pod_tbl_sermons['notes']= ($row['field_sermon_notes'][0]['filename'] != "")
? "/wp-content/messages/notes/".$row['field_sermon_notes'][0]['filename']
: "";

print "pw_pod_tbl_sermons<br />";
print_r($wp_pod_tbl_sermons);

$sql = "INSERT INTO wp_pod_tbl_sermons (name, body, mp3, m4a, video, notes)
VALUES('" . mysql_real_escape_string($wp_pod_tbl_sermons['name'])."',
'" . mysql_real_escape_string($wp_pod_tbl_sermons['body']) . "',
'" . $wp_pod_tbl_sermons['mp3'] . "',
'" . $wp_pod_tbl_sermons['m4a'] . "',
'" . $wp_pod_tbl_sermons['video'] . "',
'" . $wp_pod_tbl_sermons['notes'] . "'
)";

mysql_query($sql) or die (mysql_error());

// Get the id of the row that we just inserted, and assign it to $id.
$sql = "SELECT id FROM wp_pod_tbl_sermons ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql) or die (mysql_error());
$id = mysql_fetch_array($result);

// $wp_pod get populated and inserted into wp_pod.
$wp_pod['tbl_row_id'] = $id['id'];
$wp_pod['datatype'] = '3';
$wp_pod['name'] = $wp_pod_tbl_sermons['name'];
$wp_pod['created'] = date("Y-m-d H:i:s", $row['created'] - 3600);

print "<br />wp_pod<br />";
print_r($wp_pod);

$sql = "INSERT INTO wp_pod (tbl_row_id, datatype, name, created)
VALUES('" . $wp_pod['tbl_row_id']."',
'" . $wp_pod['datatype']. "',
'" . mysql_real_escape_string($wp_pod['name'])."',
'" . $wp_pod['created']."'
)";

mysql_query($sql) or die (mysql_error());

// Get the id of the row that we just inserted, and assign it to $id.
$sql = "SELECT id FROM wp_pod ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql) or die (mysql_error());
$id = mysql_fetch_array($result);

// $wp_rel get populated and inserted into wp_rel.
$wp_pod_rel['pod_id'] = $id['id'];
$wp_pod_rel['sister_pod_id'] = 0;
$wp_pod_rel['field_id'] = 11;
switch ($row['field_sermon_series'][0]['value']) {
case 'Isaiah':
$wp_pod_rel['tbl_row_id'] = 2;
break;
case 'Thriving in the Storm':
$wp_pod_rel['tbl_row_id'] = 3;
break;
case 'Grace Works':
$wp_pod_rel['tbl_row_id'] = 4;
break;
case 'Joy in Tough Times':
$wp_pod_rel['tbl_row_id'] = 5;
break;
case 'The Glory of God':
$wp_pod_rel['tbl_row_id'] = 6;
break;
case 'A Life of Greatest Value':
$wp_pod_rel['tbl_row_id'] = 7;
break;
default:
$wp_pod_rel['tbl_row_id'] = 1;
break;
}

print "<br />wp_pod_rel<br />";
print_r($wp_pod_rel);

$sql = "INSERT INTO wp_pod_rel (pod_id, sister_pod_id, field_id, tbl_row_id)
VALUES('" . $wp_pod_rel['pod_id']."',
'" . $wp_pod_rel['sister_pod_id']. "',
'" . $wp_pod_rel['field_id']."',
'" . $wp_pod_rel['tbl_row_id']."'
)";
mysql_query($sql) or die (mysql_error());

// repeat the proces for the second pick field.
$wp_pod_rel['field_id'] = 14;
switch ($row['name']) {
case 'Scott Treadway':
$wp_pod_rel['tbl_row_id'] = 1;
break;
case 'Sabo Cortez':
$wp_pod_rel['tbl_row_id'] = 2;
break;
case 'Steve Salomon':
$wp_pod_rel['tbl_row_id'] = 3;
break;
default:
$wp_pod_rel['tbl_row_id'] = 4;
break;
}

print "<br />wp_pod_rel<br />";
print_r($wp_pod_rel);
print "<hr />";

$sql = "INSERT INTO wp_pod_rel (pod_id, sister_pod_id, field_id, tbl_row_id)
VALUES('" . $wp_pod_rel['pod_id']."',
'" . $wp_pod_rel['sister_pod_id']. "',
'" . $wp_pod_rel['field_id']."',
'" . $wp_pod_rel['tbl_row_id']."'
)";
mysql_query($sql) or die (mysql_error());

}
?>

</pre>

What’s that smell? It’s the smell of weeks of work being cleared out to make way for something better.

For a whole list of reasons, Alex and I decided we needed to do a reboot on the website. Not only on the design that we had done, but the way we want to communicate with our congregation. And while we’re at it, I ditched Drupal for this project and switched to WordPress.

A Better Way to Communicate to the Congregation

There are only two types of people who visit a church website: visitors who are looking for general information about service times, worship styles etc., and attenders who want news or specific information like upcoming events or what their kid needs to pack for camp. Looking at the Google Analytics for ranchocommunity.org, over 40% of our visitors fall into the first category.

When a person is looking for a new church, typically they’ll hop online and visit six to ten different church websites. From that information, they’ll choose only one or two to actually visit on Sunday morning. Their first, and possibly only, impression of your church comes from that first 30 seconds of looking at your website. They need to very quickly have an idea of what kind of church you are, what your church is about, what your worship is like, your service times, your location and if child care is available. (I’m sure this list is debatable, let me know what you think in the comments.) If they don’t find that quickly and easily, you’ll never even get a chance for a greeter to shake their hand.

The other type of person wants totally different information. They’re regular attenders who need to download a permission slip, or find out about the Women’s Tea… the kind of things a visitor couldn’t care less about.

This has always caused a problem when trying to design one site for both types of visitors, especially at a very active church like ours. Trying to put all that information in one place makes it hard to get to any of it. Drop down menus with submenus of submenus just makes it hard to find anything.

So our solution: split the website. Leave ranchocommunity.org as the outward facing website, and create a new domain with all of the constantly changing information that the congregation will want. We’re calling it RCC Live. It’s mostly up and running, you can check it out at rcclive.org. Now each one of these sites can focus on one type of visitor, which means we’ll be able to serve that visitor better.

There’s two more big reasons for the change. Look for those in an upcoming post.

Update: Mike Sessler expanded on this concept to be able to record in Audacity, and with a little tweaking, other applications as well. Check it out.

One time I forgot to push record in Quicktime before a funeral. So right after the incredibly awkward conversation with the grieving widow, I decided to figure out a way to never forget again. Here’s the solution I came up with to get Quicktime to automatically start and stop an audio or video recording at a certain time.

Basically it involves two Automator workflows- one to start the recording, and another to stop and rename it. Then they are triggered with iCal events. If you have used Automator at all, this is a pretty easy workflow to figure out. But if you’d like an introduction to the goodness of Automator, keep reading.

I’ve saved the workflows as applications and made them available on the downloads page. But keep reading for info on how they work and how to use them.

The first workflow consists of two actions: Create a New Video Capture, and Start Recording. Here’s a screenshot:

In the File > Save As… box there is an option to save the workflow as an application. That’s what I’ve done here.

Next is the Stop Record and Rename workflow. Here’s what it looks like:

The next thing is to have iCal run these apps at predetermined times. To make things look a little nicer, I created 2 new calendars, one is called “Start” and the label color is set to green. The other is called “Stop” and the color is, of course, red.

Now I can create events with alarms that trigger the appropriate workflow application. Here’s what the settings look like:

So that’s really all there is to it. If you need to change your quicktime record settings, you set those up in Quicktime. You can also use this method to record audio only if you want- just change the automator actions to New Audio Recording and Stop Audio Recording.

So hopefully with this method you’ll avoid the incredibly painful conversation with a grieving widow that I had. Just don’t forget to turn the computer on.