Error message

The spam filter installed on this site is currently unavailable. Per site policy, we are unable to accept new submissions until that problem is resolved. Please try resubmitting the form in a couple of minutes.

Drupal - Save MySQL Data to Cache then Load it from Cache

Category: 

Say for example you wanted to grab a list of all of your Drupal Content Types with a Node Count for each Content Type and display it as an html item list. That is easy enough with your own custom module and a bit of MySQL. However, on a site with thousands upon thousands (even millions) of nodes, this operation can be a little expensive on the server.

For expensive MySQL calls like this, it is usually best to store the fetched results in Drupal's cache the first time the data is gathered, then next time the data is asked for, check to see if it is in Drupal's cache, load it from cache if it is present, otherwise make the MySQL call, then save it to cache. Alright? Enough talk, let's see some code:

Drupal 7

// Get a list of node content types and the number of nodes in each. Try
// loading the data from cache first, if it isn't there, load it up via mysql
// then cache it.
$results = null;
$cache_key = "my_content_types_list";
$cache = cache_get($cache_key);
if ($cache) {
  drupal_set_message("Content Type List Loaded from Cache: " . date("Y-m-d H:i:s", $cache->created));
  $results = $cache->data;
}
else {
  $sql = "SELECT COUNT(nid) as node_count, type FROM {node} GROUP BY type ORDER BY type ASC";
  $result = db_query($sql);
  $results = $result->fetchAll();
  cache_set($cache_key, $results, 'cache', CACHE_TEMPORARY);
  drupal_set_message("Content Type List Saved to Cache");
}
$list = array('items' => array());
foreach ($results as $data) {
  $list['items'][] = $data->type . " (" . $data->node_count . ")";
}
return theme('item_list', $list);

Drupal 6

To do this in D6, it is exactly the same as the D7 snippet above, except when loading via MySQL for the first time, the $results need to be assembled a little differently before calling cache_set.

...
else {
  $sql = " SELECT COUNT(nid) as node_count, type FROM {node} GROUP BY type ORDER BY type ASC";
  $result = db_query($sql);
  $results = array();
  while ($data = db_fetch_object($result)) {
    $results[] = $data;
  }
  cache_set($cache_key, $results, 'cache', CACHE_TEMPORARY);
  drupal_set_message("Content Type List Saved to Cache");
}
...

In both of these examples, the data will be cached in Drupal's cache until the cache is flushed.

Add new comment