Displaying a Resultset from a Custom SQL Query in Drupal 7

A 2 minute read written by Kim May 29, 2014

Drupal figure sketched in blue

In my last Drupal blog post  I talked about how you can alter an existing view query with hook_views_query_alter, but what if you want to display a result set (your own “view”) from a custom SQL query?

Well, here’s how.

Background

One of our clients requested a waiting list for when products were sold out. They didn’t want anything too fancy, just some information collection. So I figured, heck, I’ll leverage Webforms, which we already used in various places around the site.

I created my webform, and just needed to create a view of webform submissions with a contextual filter for the product page, and I’d be done. Great!

Only…

webform submissions - view 01

webform submissions - view 02

No data values? What?!

Webforms does add this view's functionality, but only in a later version. I didn’t want to update from v3 to v4 since various bits and pieces on the site rely on Webforms and I didn’t want to spend the week hunting down potentially broken functionality.

So I created my own module.

The Form

Using hook_menu I created a page with a page callback to my form. The form calls the query to combine the webform components and submissions to pull in the data values I needed. When the form is submitted the SQL table looks roughly like:

	sid | cid | data
	123 |   1 | 2827
	123 |   2 | Kim
	123 |   3 | Beaudin
	123 |   4 | kim@yellowpencil.com

I wanted to pull in the data for a specific page (component 1). So the query gets the submitted rows, joins the components, finds where the component for page_id is equal to the value of the product node page we want.

I set my webform id as a custom variable in my module install file so that I wouldn’t have to hardcode the value in place and it could then be edited through the variable editor UI.

Then I simply pass the result to the form, and theme it as a table. Surprisingly simple!

function waitinglist_build_form($form) {
  $page_id = arg(1);
  if( !is_numeric($page_id))
    return;

  $webform_id = variable_get(‘waitinglist_webform_id);
  $sql = "SELECT data.sid, comp.name, data.data
    FROM `webform_submitted_data` AS data
    INNER JOIN webform_component AS comp ON data.nid = comp.nid
    AND data.cid = comp.cid
    WHERE data.nid = :webform_id
    AND comp.type <> 'hidden'
    AND sid IN (
        SELECT sid
        FROM webform_submitted_data
        WHERE data = :page_id
    )
    ORDER BY data.sid";

   $args = array(':page_id' => $page_id, ':webform_id' => $webform_id);
   // fetch
   $result = db_query($sql, $args);

  // webform submissions are not nicely organized, so lets push them onto 
  // an array by sid so we can access all the properties to build our rows much more easily
  // and grab the names of the cols at the same time and push onto header
  $rowdata = array();
  $header = array();
  foreach($result as $row) {
    $rowdata[$row->sid][$row->name] = $row->data;
    $header[$row->name] = $row->name;
  }

  // build the table for the nice output.
  $form['webform_results'] = array(
    '#theme' => 'table',
    '#rows' => $rowdata,
    '#header' =>$header
  );

  return $form;
}

The Result

webform result