Three positive things, day fourteen


(this should have been posted last night)

Another short one today.

The first positive thing was hearing from an old friend. We didn't get to chat for long, because I had to have dinner and leave for a class, but we'll chat more again soon.

The second positive thing was attending my first ukulele class with the extremely talented, and patient, Stuart Fuchs. He had a six week freebie class at the local library during June and the start of July, this was the first full class and it went rather well. Stuart is a great teacher and I look forward to future classes.

The final positive thing was updating a client's website to the latest PHP. It was a bit of a juggle to get it all working, and it felt at one point like I had thrown the oranges into the air but nothing was coming down again, but it all worked in the end. And the odd part was that it didn't work when I tried it the previous night. I dunno, but it's working now.

Fixing update_sql() to accept parameters


A pretty simple yet cool feature of Drupal's db_query() function is that you can pass in parameters that will make it automatically adjust the query to correctly escape the arguments. This is one of the simple security features in Drupal as it will properly escape the string to avoid SQL injection attacks, and just simply safe you hassle. Good stuff!

When it comes to module development you'll occasionally have to update the database schema for one reason or another, so you'd figure the corresponding update_sql() function, which has some extra purpose for update scripts, would also handle parameters. Not so, unfortunately. In Drupal 5 and 6 the only argument that update_sql() accepts is the query itself, so there's no direct way of doing the parameter auto-magic gravy. Thankfully, all is not lost.

If you look at the code in update_sql(), it's actually very simple - it just runs the standard db_query() string and compiles a nice array to let the update script give a reasonable return message. So, simply put, to do the parameter processing in Drupal 5 and 6 all you have to do is replicate the update_sql() function inline, e.g.:

 * Change the primary color from blue to green.
function funkychicken_update_6001() {
  $ret = array();
  $sql = "UPDATE {funkychicken} SET color='%s' WHERE color='blue'";
  $result = db_query($sql, 'green');
  $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql));

  return $ret;

So that's all there is to it!

Going a step further, here's an alternative to update_sql() to make it even easier. This can be added to your module's update file, e.g.:

 * Change the primary color from blue to green.
function funkychicken_update_6001() {
  $ret = array();
  $ret[] = update_sql2("UPDATE {funkychicken} SET color='%s' WHERE color='blue'", 'green');

  return $ret;

 * An alternative to update_sql() that accepts parameters.
 * Params:
 *   $sql - The query to execute, as a string.
 *   $args - The parameters to pass to the query.
 * Return:
 *   An array containing the success status (boolean) and the query.
if (!function_exists('update_sql2')) {
  function update_sql2($sql, $args = NULL) {
    $result = db_query($sql, $args);
    return array('success' => $result !== FALSE, 'query' => check_plain($sql));

FYI there's a patch available for Drupal 6 that might eventually make it into a release, it just needs to be worked on a bit more. Either way, hopefully a fix for this will work its way into Drupal 7.

Managing Drupal Views, the proper way


One of the most powerful and most useful modules on Drupal is Views. With one screen you can build custom pages & blocks based around your content, select the exact fields you need, add filters and arguments, and relatively easily customize the display, and that's just scratching the surface. In fact, Views is so flexible that I've built sites which have 90% of their architecture based solely around taxonomies and Views.

The main trick to making using Views manageable is to save them out as files and load them through a module. It sounds really easy, and it is really, but there's a few steps to it.

The first step is to have a per-site module specially for holding these kinds of things, a general bucket for your random chaos.


The second step is to go to each View in turn and export it to a file in your new module's directory. To do that, go to your site's Views list page and for each of the custom ones you've built or modified, click the Export link next to it to bring you to a page with the view's code listed out:


What you need to do next is save this text out to a file. By default all of the text is selected, but you're not ready for that yet. You first need to create the file to store the text. I like to use the following filename structure:


This both groups all views together and makes them easy to identify at a quick glance. Going by this, the above view would be saved with the filename:

Now, just copy all of the code from the Export page into the newly created file. Simple, huh? Well, there's a little gotcha - you need to add "<?php" to the top of the file otherwise the next steps won't work right, e.g.:


Well, we're not done yet, we still have to tell the site that there are views in this new module. This involves two further steps..

The first part is to tell the system that this module uses the Views API by adding some lines to your module:


The last part is to load the views into the system. To do this you must add another file to your module directory named:


Then add the following to the file:


And now you're done!

Well, almost. Your site is still going to load the view that's already in your database rather than the one in your module. To fix this just go back to the main Views list page, find the view you just exported and click the "Revert" link to revert it from the database-stored version to what was in your file; don't be afraid when it says "Are you sure you want to revert the view?" because yes, that's what you want to do. So do it.

And now you really are done. Enjoy. And make sure you save it into your site's SVN server promptly.

Learning Exercise:

To make more of this, and to test your PHP skills, try changing funkychicken_views_default_views() to automatically load all files named 'view.[something].inc' instead of having to manually list each one.

Make a special module for each Drupal site


One thing I've learned from my Drupal development time is that every site should have a new module created for it to house all of the small custom functions and things you'll use. Common things I've added to these per-site modules include:

  • Default views (more on those later),
  • Odd string modifications, e.g. shortening a node summary to fit a certain amount of space,
  • Hacks to modify forms in unusual ways, e.g. pre-assign taxonomy terms to simplify the editorial process,

I've found it much easier to manage these in one single module than e.g. hacking blocks with funky PHP code, etc, and you also gain the benefit of having it all in code, so you can track changes in a code versioning system like svn or git.

Bonus Tip

One final tidbit on this is to create a module "package" for all of the modules you create, i.e. add the following line to your file:

package = My Stuff

Now when you view the module admin page they'll all be grouped together, making them easier to find.

menu_link_save doesn't like aliases (drupal)


A quick tip.. while working on an install profile in Drupal I discovered that the menu_link_save() function requires an internal URL, e.g. "node/123" instead of "my-cool-page". Once I tracked down the issue I was able to very easily create lots of menu items as needed, but it wasn't entirely obvious this was needed. I was using install_profile_api to create the menu items and figured it was going to make things easier for the end user, but alas no, so I threw together a quick patch to save others the headache, and wasted hours of development time.


Subscribe to PHP