Drupal 7 Module Development Part 7 - Database API displaying data

By shane
Thu, 2012-10-04 23:22
Daily Dose of Drupal Episode #22

Share with Others

Continued from the last episode of the Daily Dose of Drupal, this episode continues Drupal 7 module development work with the Database API to pull data from the database and display it on a page.

In this episode you will learn:

  • How to pull data from the Drupal database using the Drupal 7 db_query function
  • How to use db_select and the Database API's dynamic query building to pull data from the database
  • How to display data using a theme function to output data as a table
  • How to sanitize user entered data for display using the Drupal check_plain and check_markup functions

Welcome to another Daily Dose of Drupal, this is Shane with codekarate.com and today we’re on Episode Number 22. We’re going to continue on with Drupal Module Development and this will be the last day we go over some Drupal Module Development concepts for a while.

So if you haven’t watched yesterday’s episode and I think even the day before, we we’re going over a module … we were writing called Form Example. The first day, we walk through how to create a Database Table within a Drupal 7 Module and yesterday we walked through how to create this Example Form and get it to actually insert data into the Database.

Today we’re going to go through how to build a custom page that just lists the results of this in a Table format and keep in mind that as I mentioned in previous episode; this is very primitive simple example, you’d probably want to use just Drupal Content Types and a Drupal Views to make this a lot quicker to build and just more flexible from all angles but this example touches on a bunch of core concepts that you’ll need in order to build more complex Drupal Modules.

So the first thing we’re going to do is we’re going to come back into our formexample.module file and this is all things that we have written yesterday and I’m going to add another permission because we’re going to create another page on this site and I’m going to call it Access Form Example Submissions and then we’re going to add another Menu Item and we’ll make the URL just be called form-submissions and we want the access argument to match up here through the actual permission we just created.

So we’re going to go ahead and paste that in there and we’re not going to have any page arguments in this case and our Page Callback is just going to be Form Example Submissions which we will come down here and create it. We’ll go below all these other Form Functions we created yesterday and we’re going to create our new function. Okay so now that we have very basic outline here of what we’re going to do, we actually get to make the query that’s going to go up to the database, pull all the form submissions and then print them out.

So a couple of things here; the first thing we’re going to take a look at is the Drupal Database API, so we go to the Database API page on drupal.org and this is going to have a whole bunch of information that’s going to show you different ways that you can pull data from the Database.

The first way … I mean if you just go into the general concepts, it’s going to go through a whole bunch of things talking about just basic concepts of the database, how to configure the database, the Static Queries, that’s what we’re going to take the first look and we’re going to use the second version here and I’m going to show you two different ways to do this; this is actually using db_query which is just a Drupal function, this is used pretty heavily in Drupal 6 modules as well so it actually allows you to write the SQL that you want to use to pull data from the database, in this case I’m using a MySQL Database but as long as you write fairly standard SQL, it should work across databases and you’ll notice that there’s little brackets around the actual database table and that’s anytime you want to have database prefixes inside your Drupal site that will allow for that to work.

So we’re going to come down into our code here and we’re going to do a couple of things in this Form Example Submissions Function that we started to create. I’m going to just start by saying a variable called $results equals db_query and I’m going to run a select … I’m just going to select everything from Form Example as I add those brackets around the Table.

This is the very basic simple way to do it, most are going to show you a slightly different version but we’ll start with this, we’re also going to then create two variables since we’re going to output things in a Table format; we need a Header, so I’m going to create a $header Variable and it’s going to be an array and it’s going to contain all of the different Header Rows that I want. So I’m going to have one for ID Field and notice I’m running this through the T Function, one for My Number, one for My Text Field and one for My Text and I’m also going to create a Rows Variable that’s going to hold all the rows based on this data that I pulled back from the Form Example Table, we are then going to loop through the results and this is just simple looping through a database results. So you’ll notice I just say for each results, as result and then I’m going to get the record one at a time and this is going to be as an object so it’s going to pull back as an object from the database table.

In this case since I’m selecting everything, it’s going to be based on the name of the Fields. So for instance if I access fe_id I’ll get that field, My Number; I will get that field and you can of course in here you could do a print statement like this if you would like and it’s going to loop through and print out all of those so you can get a better look at it and I’m going to keep things moving here and I’m just going to go rows, add a new item to the row and I’m going to say results fe_id, results My Number, results My Text Field and results My Text and those are just all the columns from that database table which is going to continually loop through this and add each item from here as a row.

Now we’re going to call a Drupal Theme Function; I’m going to say return Theme and this built into Drupal and it’s just a Table Function and it takes two parameters; in this case we’re going to need to wrap the parameters in an associate array and I’m going to have as one index is going to be Header and it’s going to be my Header Variable and rows is going to be my Rows Variable and that documentation if you look for the theme_table function on drupal.org it’s going to have a whole bunch of information about what it does, how it’s set up, you can then of course overwrite how this is printed in your theme if you need to, others in the comments there’s some examples, I basically took that one right in the comments.

It’s pretty self explanatory once you use it a few times. So now that we have this we can go ahead and save it, what we’re going to need to do is come over here and I’m going to clear my cache because we added in a new Menu Item s at minimum we need to make sure we clear the Menu cache here, just go ahead and clear everything. Now if I go to form-submissions you can see it works, we got my one form submission that I created, if I had more we’ll just continue to list them.

Pretty basic; I’m now going to go ahead and show you a different way to pull the data; if you come back to the Database API you can look at Dynamic Queries; this is going to allow you to chain different conditions, fields and just a whole bunch of different query options together to build a dynamic query.

A couple of things about this; it’s not quite as efficient as just writing out the SQL yourself, it has to do some extra processing so it’s not quite as fast, of course you know you’ll never actually notice the difference, it’s milliseconds of difference but just keep that in mind that it’s not quite as efficient but it is much more flexible. So you really have to determine for yourself there’s kind of people in the Drupal world on both sides of the fence that whether you should do it one way or the other and it’s really … in my opinion it’s up to you which you prefer, I sometimes like writing SQL myself but I’ll also use DB Select if the situation calls for it.

So I’m going to go ahead and just comment out the first example and I’m going to write a second way to do this. This is going to do exactly the same thing, just using the two different options. So I’m going to select from the Form Example Table and I give it just an alias of FE and then I am going to go ahead and in this case I’m going to define a specific fields, you use the formats for my Table that’s FE and give it an array of fields.

I want Fe ID, My Number, My Text Field and My Text and these are pulling straight from the actual database table based on the fields that I … field names that I provided when I created the Table in the Install file, I’m also going to chain on a range of 0 to 50 just so you can see how that would work and I can also chain on and order by and in this case I’ll say order by Fe ID, I think that’s how that works, let’s double check that. Okay so I need the actual prefix in front whatever the name of it is so fe.fe_id and I could have it ascending or descending but if I leave it blank it’ll just assume the default ascending.

The next step then is to go ahead and execute this query and now I save it, come back here, I refresh … I missed something here, oh this should be fields, I refresh and everything works, same as before, just two different ways to go about pulling out the data. As you can see this is a much simpler, shorter way to do it, of course I’m saying I just want everything here, I’m defining specific fields in a couple of different options but this will allow you to occur of course chain different options onto this query before you execute it.

So I would try out both, definitely look through the documentation on the Database API and the last point I want to make is right here I’m just completely displaying whatever is entered into the database. If you’re any type of security expert or know anything about security, you’ll realize that that is not a safe thing to do.

If I were to insert some questionable mark-up into my Form, there is possibility that I could cause some cross site scripting type of issues. So you want to go ahead and read or look at writing secure code on drupal.org and they talked about the check_plain or check_markup function and anytime you’re going to be outputting data onto the screen you should be running it through some type of sanitation function that Drupal writes.

In this case I’m going to use check_plain for the ID Field, of course that one just pulled from the database as a serial ID, don’t really need to run a few check_plain and I’m going to do check_markup for the My Text which will allow … if I went in to my input or my text formats on my Drupal site and I set the default to Filtered HTML or I could go ahead and you can see that second parameter is the format.

So if I wanted to allow in the My Text area HTML or certain types of tags to be allowed and displays HTML, I can do that, I would just read up on the check_markup function and the check_plain function and just make sure anytime you’re going to be displaying data to the screen, you want to run it through one of this functions to help prevent cross site scripting and all those things that you don’t want to happen on your site. So as you can see I refresh, nothing change but know that these functions are there to help and make your code much more secured.

And that’s it for this time on the Daily Dose of Drupal, next time we’re going to switch gears and make the videos a little shorter again and go back to some more basic things. So thanks for listening and watching, follow me on Twitter @smthomas3 and go to codekarate.com and sign up for the newsletter. Thanks for watching.


Very informative video. Thanks for taking the time to put this together. It did raise the question as to why one would use db_query or db_select and so I looked it up and wanted to pass along what I learned. Feel free to correct me if I'm off base.

The use of the STATIC query (db_query) is almost always "preferred" for writing just that -- STATIC queries. These are "hard coded", simple queries that fetch some data from your database tables.

The use of DYNAMIC queries (db_select) is "required" when the query changes, say a different table is selected based on user input, or you need to provide access control when fetching from the node table.

Thanks again!

Could you possibly do a video on the edit/delete process please?

Just to thank you, even if you don't have all the answers, i'm searching for about drupal 7, you made me go significatily forward, thanks and regards

Often the problem is that you need to clear the cache before the results will display. Anytime you implement hook_menu with a new menu item, the page is not accessible until the cache is cleared.

If that still does not help, then you need to make sure you named the hook_menu implementation accordingly (using your module name). Also check your access callback and access arguments on the hook_menu item.

One last thing to look at is that you are actually returning the menu items at the bottom of the hook_menu function.

Post new comment