Does that make sense?  Here…maybe showing is better than telling in this case:

If that helped and you want to see how it was done…read on!

So just to recap - in this tutorial I have a weblog containing business names.  The weblog has two category groups assigned to it - one category containing state names and the other business types.  On the site I want to allow users to navigate to a final list of entries in two ways:

• Select a state
• Get a list of valid (only those actually containing results) business types for that state
• Select a business type
• Get a list of businesses of that type doing business in that state

or

• Select a business type
• Get a list of valid states (only those actually containing results) having that business type in them
• Select a state
• Get a list of businesses of that type doing business in that state.

ExpressionEngine lets you assign multiple category groups to one weblog - which from a content entry and maintenance point of view gets me where I want to be.  However there is no built-in way of getting the categories to interact with each other as outlined above - where second-level category list is filtered by a choice from a first-level category group. 

I see this as navigation based on a very simplistic faceted classification or what I’ve seen called “multifaceted navigation”.  I’m not sure how far you could push this approach with more than two facets but this should provide a good jumping-off point.

Assumptions

• Your categories are only one layer deep - no nested subcategories
• Your category url titles are unique

Setup Requirements
Since this is a more advanced tutorial I’m going to assume you can get through the following without step by step instructions.

• Weblog named “businesses”. For the purposes of this tutorial I have no additional custom fields assigned - all I’m using is the default title field
• Two category groups - one for states and one for business types
• Some categories created within the category groups - again all with unique category_url_titles
• Knowledge of the category group IDs
• No other weblog using those category groups
• A number of entries posted into the weblog and assigned to various combinations of categories
• The EE Query Module (which is not available in the Core version)
• The Seg2Cat Extension from Lodewijk Schutte installed

The Index Template
I’ve included the templates used here as a downloadable zip file - but let’s walk through the logic here a template at a time. 

The index template has the following code:

The first clause in the if statement handles the “index” view of the template before any choice has been made.  All I’m doing there is using the standard weblog categories tag to return the top-level choices for each group.  Do note the links that I’m building are not traditional EE category links - I’m rolling my own here.  Businesses is the name of the template group I’m operating out of.  This is the index template, so everything else is virtual.  In the next segment I use either “in” or “type” keywords, then append on the category url title.  Clicking any of these links reloads this template (because there are no actual saved templates with the names of “in” or “type”).

When the template is re-loaded after clicking a top-level category link, one of the next two clauses in the if statement fires, and this is where the Seg2Cat extension kicks in by providing the {segment_3_category_name} variable, which looks at segment 3, sees the category_url_title and returns the category name for it.

Now EE needs to take the selected category, look through the entries in the businesses weblog in that category, and see what categories those entries are assigned to in the other category group.  That happens in the embedded “get_categories” template—and that template is made reusable by passing the ID of the category group we want results from.  Here’s the code in that template:

The Embedded Template

This query essentially:

• Takes a known category ID
• Takes a known category group ID
• Finds entries assigned to the known category ID
• Makes sure those entries have an “open” status
• Finds what other categories those entries have been assigned to—from the category group represented by the known category group ID
• Builds a list of the unique values it finds
• Creates links that contain url_titles from both categories
• Orders those results as per the order specified in the Control Panel

Note - for this example I’m not specifying a weblog.  If you need to narrow the results to a specific weblog you can do that by appending onto the queries WHERE clause and specify the weblog ID.  You could also pass that in as an embedded variable if you needed this embedded template to be flexible in what weblog it looked at.

Again the magic happening in this code is provided by the Seg2Cat extension that gives access to a category ID based on the category url title currently being displayed.  The extension comes in handy yet again on the final template - the list template:

The List Template

Seg2Cat is necessary here to get around another limitation of the native weblog:entries tag—in that while it has the category parameter which can find only entries that exist in two categories, it needs category ID’s rather than the more SEO-friendly category_url_titles.  Seg2Cat again steps in to provide the translation of category_url_titles to category IDs - so now I can have URLs like:

or

...and the weblog:entries tag can still function.  Note also that while these URLs are using category_url_titles they are not using the typical /category/ segment needed when using EE categories.

So there you have it!  While this tutorial is a bit more advanced, what with an extension, embedded template using an embedded variable, segment variables, and conditionals— it all comes together to create a nifty little front-end navigation approach with search-friendly URLs and easy client content maintenance.

Disclaimer
Methods used in this article directly manipulate the EE database and as such are somewhat risky.  They are best undertaken by people experienced with databases and SQL statements. By no means do this on a live site!  Make a backup or two before proceeding, and do your manipulations on a copy. And - proceed at your own risk.  I or Train-ee.com will not be held responsible for damage inflicted to your site by attempting this work on your own.

The Situation:
OK - so here’s what I had:

• Three sites total, with the target site being #2
• A whole bunch of registered user accounts in multiple member groups
• All templates stored in the database (vs external flat files)
• Site hosted at EngineHosting (who do, in fact, rock)

Starting Out
I did what anyone in this situation might do - searched the EE Forums to see if anyone else had done this and posted any tips or gotchas.  I came up with this thread which served to lay out my basic workflow:

• Make a backup of the entire site
• Download the database locally for manipulation
• Delete Site #3 entirely
• Clean out all weblogs, field groups, status groups, member accounts, template groups and templates from Site #1
• Go through the database and change all site_id’s currently storing Site #2 to store Site #1 instead (effectively moving all EE backend objects from Site #2 to Site #1)
• Once Site #2 objects have been moved over to Site #1, delete Site #2
• Once done/successful upload single site to new host

I had a feeling this would make a good article for Train-ee so kept notes during the process.  Below are those notes, prettied up a bit, but still including the stops and starts that I ran into as I felt it was important to note what kind of issues can come up in this type of work.

Loading the Database
I logged into the EngineHosting Control Panel and did a databse backup of Train-ee.com using PHPMySQL.  So far so good.

Once I had the db backup saved locally I fired up my WAMP setup and set about to load the database locally.  Attempting to import the database using the PHPMySQL interface I got a PHP file size limit error.  I’ve run into this before trying to load tables back into the database and found this Staggered MySQL Dump Importer Script named BigDump that works a treat.  I configured that and ran it, but then ran into an error about the database backup containing Extended Inserts.  I went back to the Train-ee hosting control panel and re-ran the export with Complete Inserts only.  I ran BigDump again and the database imported without error.

EE Control Panel
I loaded ExpressionEngine but found that the Control Panel wasn’t loading any styles. I updated paths under Admin / System Preferences to no avail.  I then also noticed that my local EE install was 1.6.6 yet - and worried about bringing in 1.6.7 data to an 1.6.6 install.  I updated ExpressionEngine to 1.6.7 successfully but still had no Control Panel styles.

I found that the Theme Folder Path still wasn’t correct (I looked at the WAMP/EE setup on another system for direction).  I specified a full path for the theme folder starting from C:\ and once I updated the Control Panel styles returned.  I also had to remember to update these same paths in the configuration of the other MSM sites.

Re-arranging the Furniture
I deleted Site #3 using the CP interface - easy peasey there.

I then set about deleting all the objects within Site #1:

• Weblogs
• Field Groups
• Category Groups
• Custom Status Groups
• Template Groups
• Member Groups

Member Accounts
Remember - deleting members also deletes their entries and comments.  This was fine in my case but may not be for you if your site has user-generated content that you wish to move over.

While I was deleting member groups, ExpressionEngine put all the members into Banned group - which you can’t delete.  I still wanted all these accounts gone, so at first started deleting members via the Control Panel.  This only deletes 30 or so at a time, however so I started to wonder if a SQL-based approach might work faster. I turned on Display SQL Queries in hopes of being able to copy and modify the query that EE generates and delete all the members at once, but the queries are relatively complex with ID’s getting specified everywhere so that approach looked too hard.  I ended up just turning the queries back off and used interface controls to delete all member accounts except for my SuperAdmin login.

Database Updates
When you install the MSM Expansion, EE makes use of a Site ID contained in many of the tables.  The value in this field tells EE which site a particular database record is associated with.  The task at hand, then, is to go through the database, find those records that have a site_id of “2”, and change those records to instead have a site_id of “1”.  This effectively moves all backend objects from Site #2 to Site #1.  I recommend doing this table by table, as I ran into a few that I choose not to touch. 

Here is the query statement I used:

I kept this query in a Notepad document where I could change the table name as needed. 

There are two spots in the EE interface you want to be in during this process - so have two tabs or two browser windows available. Both of these are found under Admin > Utilities > SQL Manager.  The first one you want is the SQL Query Form.  The other one is the Manage Database Tables interface.  Note that you could do this all within PHPMySQL as well, I just chose to stay in ExpressionEngine.

The workflow is:

• Go through the Manage Database Tables interface one table at a time
• Look for tables that have data
• Click the Browse link to take a look at the data
• If the table has the site_id field (usually it’s one of the first couple of fields) then the update query needs to run on that table
• Edit the SQL query to use the name of the table you’re looking at
• Paste that query into the SQL Query Form
• Click Submit - You should get a message about the number of rows affected

Here’s my list - if a table is mentioned with no notes I ran the query against it.  Tables not mentioned either had no data or no site_id field.  Tables with notes had the data but I chose to leave them alone for reasons indicated:

• exp_categories
• exp_category_field_data
• exp_category_groups
• exp_comments
• exp_cp_log
• exp_field_groups
• exp_html_buttons
• exp_member_groups: I left this one alone as I didn’t want to mess up my own login.  I figured appropriate member groups will be deleted when site #2 is deleted.
• exp_online_users
• exp_referrers
• exp_search_log
• exp_sites - I’m going to leave this one alone as well as I expect it’ll get updated when I delete Site #2
• exp_specialty_templates - going to leave this one alone assuming deletions will happen here when I delete Site #2
• exp_stats: I’m also leaving this one alone as it’s not mission critical and I expect it to be updated when I delete Site #2.
• exp_status_groups
• exp_template_groups
• exp_templates
• exp_upload_prefs
• exp_weblog_data
• exp_weblog_fields
• exp_weblog_titles
• exp_weblogs

With the database updates all done I went back through the EE Control Panel - and it appeared to have worked.  All template groups, weblogs, posts, field groups, category groups etc that were all previously in Site #2 were now showing in Site #1. I made a few quick posts, saving then deleting them with no error. I then went into the Sites menu and deleted Site #2.

The Rest of the Site
I then moved all ancillary files from server to local - images, JavaScript files, etc. Once these were pulled down I tried to load the site in my browser.  I forgot a couple of plugin files and got an error to that extent.  Once I installed the missing plugins the site loaded and functioned normally.

Success!  At this point the site is running well locally - and I foresee no real issues with loading it up to a new hosting account where it can stand on it’s own feet. 

The Last Step
There’s no real way to “completely uninstall” MSM - the site_id field will remain in the database The last step - to remove the MSM related options from the CP - would be to edit the config.php file to include:

(Thanks to the EE Tech Support staff for input on that one).

I’d say this process took roughly 2.5 to 3 hours—and while it’s always a bit unnerving to go muck about at the database level it seemed to come off fairly easily.  If you undertake this process and encounter other issues or gotchas be sure to leave a comment!

I know - if you’re reading this you’re probably not a fan of anything email based anymore.  RSS feeds, twitter, Facebook - surely there must be a better way for a church to communicate with its members than plain old email? 

I agree - but the plain and simple truth of the matter is that for many people email is their internet experience.  If your church has a variety of demographic groups you’ll probably need to still use email in order to reach all of them.

Luckily, with EE the implementation is straightforward, the list is double opt-in, and recipients can unsubscribe themselves.  Now - creating and sending out regular email missives is still no small task, but at least the list management is handled by ExpressionEngine.

Caveats
The EE Mailing List Module is overall a pretty simplistic tool - I’d say it’s a good one to use if your list is in the 100’s rather than the 1000’s and you’re sending out a few emails a month.  You don’t get to track metrics like open rates, etc - and while you can send HTML email there’s no direct way to use an existing EE template as the basis (although there is a 3rd Party Extension that adds that functionality). 

If your mailing list needs are more advanced then I’d recommend checking out Campaign Monitor and the CM Extension for EE.  As a sidenote, the Campaign Monitor site is itself an ExpressionEngine site, built by Newism - who is writing a series of blog posts about how the job was done.

OK-  back to the Church site.  Here are the steps required for the task at hand:

• Make Sure the List is Enabled
• Create the mailing list
• Create a new template
• Add the necessary tags to the new template
• Test

Make Sure the List is Enabled
In the Control Panel under Admin ›  System Preferences ›  Mailing List Preferences - make sure the “Mailing List is Enabled” option is set to Yes.  This is also where you can set notification preferences such that an admin will be notified of new sign-ups to the mailing list.  This is nice if you want to send a more personal welcome message or just track the popularity of the list in general.

Create the Mailing List
The mailing list functionality of ExpressionEngine is a module, so to interact with it go to the EE Control Panel and select the Modules tab, then the Mailing List name.  From there:

• Click Create New Mailing List
• Fill in the Short Name - I specified “church_newsletter” - this value will be necessary later so make a note of it
• Fill in the Full Name - I specified “Church Newsletter”
• Click “Create”

Note that you can tweak some aspects of the emails that will get sent out - if you want a standard greeting, or each email to always have the church address, service times, and phone/fax number.  You can also change the language of the unsubscribe links etc.  To do this just find your mailing list in the list and click “Edit Template”.

Create a New Template
OK - back in the Templates area of EE let’s move forward with getting the subscription form on the site. 

While on some sites you may implement this subscription form as a sidebar or footer element, for this site it will just live on a dedicated template.  I created a “subscribe” template in the default “site” template group, choosing to copy page-comps/one-column-content as it include a newsletter subscription form.

Add EE Tags to site/subscribe
The new template will need the basics configured - page name, body id (which sets the picture that shows), etc - but I trust you’ll figure that out.  I want to focus on the core content of the page - the subscription form.

The sample code in the EE docs is a good starting point - so copy that and paste it into your template to work with.  Essentially you need to meld together the styling from the template subscribe form with the code from the EE docs - and the result is this:

Note that the short name of the mailing list that I created earlier is specified as a parameter in the opening tag.  EE lets you have multiple mailing lists, so this is how you tell EE which list to assign this email address to.

Also note the variable being used in the email text field:

The email value is a Global Variable and used as is it will default the contents of the email box to the email address of the currently logged-in member. If your site doesn’t use the EE Membership module for more than a content administrator or three, then this is probably unnecessary—but also harmless if left in.

Test Subscribing
With that code in place you should be able to load the rendered page and test the form.  Enter in an email address that you can check and submit - you should get a notification message, and then an email with a confirmation link in it.  Clicking that link should add your address to the list (note that you can manually remove it again back in the Mailing List Module Interface).

Test Sending
With an address on the list you should now be able to send a message out. Do this by navigating to the Communicate tab in the EE Control Panel.  Enter the basics of a message, select the proper Mailing List in the right column, and click Send It.  You should get an email a few minutes later and be able to unsubscribe yourself from it.

If you have any issues with the list not working I’d suggest reviewing the Email-related configurations in the EE CP:  Admin ›  System Preferences ›  Email Configuration, and checking with your webhost to see if what you’ve specified should work.  Here on Train-ee I have it set to use Sendmail and it works well.

Is It Over?
Unbelieveably almost a year after beginning this series I believe I have found the end of it!  This proved to be a much broader and deeper site than the Small Business Series - and it’s been quite a ride.

What’s Next?
As I promised at the beginning of this series, the next step will be auctioning the completed site off on eBay, and donating the proceeds to a charity.  Schedule-wise this will be in a few weeks after the SXSW buzz is over. Then I’ll follow the same model as with the Small Business series in that I’ll use these blog posts as the basis for another book that will be sold here on Train-ee.com.  I’m not quite sure how to handle EE 2.0 in regards to this content - so will need to decide that yet.

But for now - chalk up another project as being complete!

When I last left the church site home page, the left column had only a list of upcoming events.  The left column wasn’t as long as the right column so there was a bit of free space in the left/lower portion of the page.  In this installment I’ll use that space to show the most recently uploaded photo to the photo gallery.

This is a pretty quick update, only made a bit longer because while I was at it I decided to undo the embedding out of the home page events listing.  Why?  I typically only embed code if it can be reused in another template or if creating an embed makes the parent template much easier to understand.  When I started this series I embedded out the events listing thinking I might use it in the sidebar of another template but as it turns out I didn’t.  The parent template isn’t that complex, so by leaving the embed in place I just have a slightly more complex template structure to come back to later if I want to make changes to the event listing.  By putting the code back into the parent template I’ll save myself a couple of clicks to find the code and it’ll also reinforce the fact that the code is only used in this one template.

Which actually brings up a good point—if you ever need to know which templates use a specific embedded template you can use the Control Panel search function (the search box is under the template group listing on the left-bottom of the templates tab).  Enter the name of the embedded template name and EE should filter the template groups and templates down to just those that contain the embed statement with that template name.  To get the full unfiltered list back again just click the templates tab.

So for the task at hand I’ll need to update three templates:

• site/index
• stylesheets/style
• embeds/html_header

Updating site/index
I first added the gallery code to embeds/events and then consolidated embeds/events back into site/index, but you could choose to do that in the reverse order.  I’ll provide the complete site/index templates in the companion files and just focus on the net change here with the new gallery code:

I’m using the exp:gallery:entries tag pair in single-entry mode here, which avoids having to worry about the rows and columns of multiple entries.  For the image itself since I wanted to also use the Lightbox effect on this image I just copied the code from photos/index.  With this code in place I could re-load the site home page and get the latest image popping in. 

Updating stylesheets/style
I wrapped the image code in a new div class so that I could do some styling - mainly just to add margins. 

Updating embeds/html_header
In order to get the Lightbox javascript to work on this image I had to update embeds/html_header and change the conditional that looks at the current url and loads the javascript files if the current page is in the photos section.

Since segment_1 should only be empty when the home page is being viewed this code:

Needed to change to:

Reloading the page and clicking the image thumbnail should now load the full-size image using the javascript Lightbox effect.

And that’s it!  I now have a home page that feels just a bit more balanced design-wise and features the latest photo uploaded.