• Development

    Snippet: MySQL table sizes ordered by largest to smallest (in MB and Row Count)

    Show All Tables By Size in MB


    Show All Tables By Size in MB (if > 1 MB)


    Show All Tables By Number of Rows


  • Development

    Using GROUP_CONCAT to Combine Rows in a Drupal Query

    Recently I was working on a D7 to D8 migration. I was trying to import news items and their taxonomy terms (among many other things). To make things simple I wanted the query results to have (for each node) a single field that contained a comma-separated list of taxonomy terms which I could then explode during the row processing. Using GROUP_CONCAT we can achieve this! Let’s break it down:

    The Drupal 7 site has the following structure (focusing on the important bits for this blog post):

    The migration relies on the d7_node migrate source plugin, which basically queries for nodes of a specific type. The query object looks like this (simplified for this blog post):

  • Tech Tips

    Sorting ActiveCollab tasks

    Our company has been using ActiveCollab for years. It’s a great project management tool and has improved considerably over the years. We have managed hundreds of projects and more than 12,000 tasks using ActiveCollab. Occasionally we want a little more from the system than it offers out of the box. I’ve built an importer (shameless plug!) to make it simple to add tasks in bulk. I’ve written some automated task creation scripts. I’ve even tried my hand at writing a 3rd party integration module. Sometimes, though, you just need a simple tweak to tailor the system to your needs. That’s what this post is about.

  • Development

    Proximity by City or Zip Code in Drupal 6 with Location and Views

    The location module for Drupal 6 is a robust module. On most projects, it gives us 100 percent of what we need. Proximity searching based on zip code is built-in and fairly painless to setup. I’ve recently been tasked to also allow proximity searching based on City. As you might imagine, the request is very similar. Both methods require the use of latitude and longitude (decimals). The difference is that instead of querying the database for latitude and longitude based on zip code (it’s usually termed postal code in Views, Location, etc.), we’re asking for the coordinates of a city. You’ll find that many cities have multiple zip codes, each of which is a row in the location module’s zipcodes database table. In this example, I’m not giving a real honest attempt at solving this issue, but rather I just return the first coordinate that matches the city.

  • Development

    db_query() and db_placeholders() example #1

    Have you ever wondered how to properly build a query like this in Drupal:

    This requires the use of db_placeholders() to create the placeholder ‘ ‘, ‘ ‘, etc.

    where $node_types is an array of node types.

  • Development

    Views Taxonomy: Get Terms with Associated Nodes

    This example serves as both an example of how to alter a Views2 query, as well as how to use the get_terms_by_count() function I’ve written.

    Unfortunately there is not (at present) a Views2 taxonomy filter that lets you “Get only terms with at least X associated nodes.” We had a client request that terms without associated nodes be hidden. This was actually more complex than it sounds, but the solution led me to a whole new level of Views2 understanding. Views2 has a hook called hook_views_query_alter() that lets you alter a Views2 query before it is executed. This is exactly what we needed to do in order to only pull terms with associated nodes. Specifically, we needed to add an additional WHERE clause to the query.

  • Development

    Cache-related WSOD! Help!

    UPDATE: You can do this stuff with Drush (setting the variable with vset). It’s much faster!

    Well, you’ve probably all had a White Screen Of Death a time or two while dealing with Drupal or PHP. They can be very frustrating, but are usually easy enough to resolve. The following illustrates how to recover from a Drupal WSOD as a result of making changes to your cache settings in the Site Configuration -> Performance admin page. Again, this “fix” should only be applied if you have issues that you know are related to recently altering the cache/aggregation/compression settings.

    The fix requires a little bit of database work. Basically, you need to modify a few rows of the variable table. This table is where many of your Drupal settings are stored. Here’s a screenshot of the variables you need to set. The values you’re seeing are in the form of serialized data. To fix the WSOD, we need to disable all of the caching. This is accomplished by setting the values in between the quotes from 1 to 0 for all of these rows (except ‘clear’). If the rows don’t exist, don’t worry about it; this means the feature hasn’t been enabled. If you are uncomfortable modifying the values, you can actually delete the rows. Just make sure, when you re-gain access to your site, you visit the Performance settings page and re-save it. You don’t even have to enable anything. The act of saving this form should re-write all of the rows to the variable table.


  • Development

    Delete comments and disable commenting on all nodes of a particular content type

    Please TEST these queries before running them on a live site! I do not want to be held responsible if something goes wrong.

    Here is how a colleague explained his situation (the solution follows):

    The trap is that if you create a content type and don’t remember to set comments off, all the nodes you create have to be updated manually to turn it off, even after you change the default in the content type settings. In our case, I imported a ton of records, and only later realized comments were on.

    Part 1: Turn off commenting on all nodes of a particular content type (we’ll use ‘job’ as the content type in the examples below).

    Now, don’t forget to disable commenting on the content type itself (so that nodes (of this content type) created from this point forward do not allow comments).

    Part 2: Delete all comments left on nodes of a particular content type. First, take a look at what comments you’ll be deleting… just to be sure of what you’re getting into.

    Now, let’s delete those comments: