Navigation

Displaying a Resultset from a Custom SQL Query in Drupal 7

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…

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:

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!

The Author

Kim is our Director, Development - she spends her time working with her team to solve complex technical challenges and is always trying to better the way we code and build projects. With her background in computing science and an Acquia Drupal certification, Kim is often elbow deep in PHP (or other programming languages) helping out wherever she can.

When Kim is not making the Internet, she’s often still behind the screen playing various video games. If you can get her outside, she’ll be riding horses at the stable or relaxing on the beach.