Drupal 6: Online Presentation of Data
Illustration by John Hersey
Watching:

Looking at Drupal's database


From:

Drupal 6: Online Presentation of Data

with Tom Geller

Start your free trial now, and begin learning software, business and creative skills—anytime, anywhere—with video instruction from recognized industry experts.

Start Your Free Trial Now

Video: Looking at Drupal's database

Now we are ready to talk about Drupal itself. Keep in mind that Drupal essentially is just an attractive and flexible interface to a back end database. So it's worthwhile to take a look at that database to understand what exactly happens when you create, modify, and delete content in Drupal. This understanding will make Drupal administration a lot clearer overall and it opens up a world of possibilities if you are an advanced programmer or a database administrator. We are working with a pretty clean installation of Drupal here. We are going to mess it up a little, so we'll be ready to roll it back to its previous state we are done. The Using the Exercise Files chapter of this course shows you how to save and restore a database, so be sure to watch that video if you decide to follow along and you end up making changes that you decide not to keep later.
Expand all | Collapse all
  1. 13m 21s
    1. Welcome
      1m 12s
    2. Reviewing requirements
      3m 35s
    3. Using the exercise files
      3m 36s
    4. Touring examples of data visualization
      4m 58s
  2. 27m 55s
    1. Planning data structure
      8m 26s
    2. Importing and manipulating data
      6m 39s
    3. Looking at Drupal's database
      6m 13s
    4. Deciding whether to store personal data as nodes or users
      6m 37s
  3. 1h 12m
    1. Understanding the Content Construction Kit (CCK)
      4m 57s
    2. Creating new content types with CCK
      7m 26s
    3. Hiding the Body field
      2m 46s
    4. Reflecting CCK field data in the Title field
      7m 28s
    5. Managing CCK field placement
      7m 32s
    6. Exploring CCK's other features
      8m 22s
    7. Using other CCK field types
      3m 25s
    8. Adding date information as a CCK field
      8m 43s
    9. Including images as CCK fields
      10m 23s
    10. Connecting content to existing nodes
      5m 58s
    11. Using taxonomies to categorize and group data
      5m 59s
  4. 53m 54s
    1. Understanding why views are useful
      6m 12s
    2. Using SimpleViews to create basic content views
      5m 49s
    3. Diving into the Views interface
      11m 16s
    4. Adding fields to a view
      7m 12s
    5. Understanding iconic controls in the Views interface
      7m 15s
    6. Surveying the Sort, Filter, and Field options in Views
      5m 40s
    7. Adding view displays as pages, blocks, and RSS feeds
      10m 30s
  5. 43m 48s
    1. Overriding default settings on view displays
      8m 56s
    2. Attaching more information to views
      10m 57s
    3. Improving view appearances with grid, list, and table formatting
      9m 20s
    4. Surveying other basic display settings in Views
      11m 3s
    5. Altering a view's appearance through CSS
      3m 32s
  6. 55m 0s
    1. Importing, exporting, and cloning views
      6m 9s
    2. Controlling access to views
      7m 19s
    3. Learning from built-in views
      5m 52s
    4. Creating views that aren't based on nodes
      10m 6s
    5. Extending views with arguments
      10m 9s
    6. Extending views with relationships
      7m 2s
    7. Going further with relationships
      8m 23s
  7. 46m 39s
    1. Understanding geographic data
      4m 25s
    2. Setting up the Location module
      16m 20s
    3. Entering geographic data with the Location module
      10m 10s
    4. Displaying basic maps with the GMap module
      6m 43s
    5. Integrating the GMap module with Views
      9m 1s
  8. 54m 21s
    1. Exporting data in tabular form
      11m 25s
    2. Planning with the calendar modules
      11m 31s
    3. Using the Charts module and Google Charts
      7m 11s
    4. Graphing data with Open Flash Chart
      4m 50s
    5. Making important data pop out with tag clouds
      7m 46s
    6. Putting it all together in an attractive package
      11m 38s
  9. 36s
    1. Conclusion
      36s

please wait ...
Watch the Online Video Course Drupal 6: Online Presentation of Data
6h 8m Intermediate Jul 01, 2009

Viewers: in countries Watching now:

Drupal's built-in data presentation tools offer several ways for web designers to clearly and attractively package their data. In Drupal 6: Online Presentation of Data, Tom Geller explains how Drupal handles data so users can set up intelligent structures and implement them with Drupal's Content Construction Kit. Tom also shows how a data-driven web site can improve its interactivity by using geographic data to connect real-world addresses to maps. Exercise files accompany the course.

Topics include:
  • Importing and manipulating data in Drupal
  • Presenting date-formatted information in calendars
  • Understanding Drupal's data-query interface, "Views"
  • Improving view appearances with grid, list, and table formatting
  • Importing, exporting, and cloning views
  • Extending views with arguments and relationships
Subject:
Web
Software:
Drupal
Author:
Tom Geller

Looking at Drupal's database

Now we are ready to talk about Drupal itself. Keep in mind that Drupal essentially is just an attractive and flexible interface to a back end database. So it's worthwhile to take a look at that database to understand what exactly happens when you create, modify, and delete content in Drupal. This understanding will make Drupal administration a lot clearer overall and it opens up a world of possibilities if you are an advanced programmer or a database administrator. We are working with a pretty clean installation of Drupal here. We are going to mess it up a little, so we'll be ready to roll it back to its previous state we are done. The Using the Exercise Files chapter of this course shows you how to save and restore a database, so be sure to watch that video if you decide to follow along and you end up making changes that you decide not to keep later.

First, we have our site right here. Let's go and take a look at the database that's sitting behind it. To do so, we go up to phpMyAdmin and click on lynda, which is the name of the database we are using for this course. Each of the things you see here in this left column and here in the main part of the screen are individual tables. The names of these tables are fairly self-explanatory for the most part. For example, let's take one of the simpler ones. User, we go down and we click where it says users here and we see the structure of the database.

Each one of the rows here: name, pass, mail, and so forth is a field in that table. The mail field for example stores the email address for that user that the user gave when they created that account. If you click on the little icon to the left of that word user here, you get to browse the database. The other way to do that is by clicking on Browse up here, then you see the values that are in the database itself. Right now we have two users. When you create a Drupal site, user ID 0 is the anonymous user. It's sort of the placeholder for people who haven't actually logged into this site. User ID 1 is the super administrator who can do everything. From then on, everybody else is an authenticated user.

Information about these roles is stored in another table, here called role. In the roles table, we see the two roles that are setup in Drupal automatically. Role ID 1 is the anonymous user. Role ID 2 is an authenticated user. If we were to setup other roles later, they would be roles ID 3, 4, and so forth, and they would have whatever names we gave them. Then when we create users and assigned them to that role, those two would be connected so that a user would have a role ID.

Let's go back briefly to our users table however. And then let's go back to our site and actually create a user. To do so you would go to Administer > User management > Users > Add user. So under Username, we are going to make it tom, E-mail address tom@example.com. Password, we'll just say password. Password of course we have to confirm, password. Scroll to the bottom. Active user, we are not going to notify them of it, but we create the account. Good, and it's all done.

Now that we've created that user, let's see what actually happened in the database. So we go back to phpMyAdmin, and click again to browse the user's table. You'll notice there is tom, user ID 3. I think somewhere in the middle there I created another user and then deleted it, so instead of saying 2 it was 3. It will keep increasing the user ID automatically, so you don't have to worry about it. But you can see there is our Password, it's encrypted so that someone who is looking at the database can't simply see it. The e-mail address, some flags on it to say whether or not they are an active user, when they last logged in and so forth.

This example is going to be more complex, because when you create a node in Drupal, it actually affects many more tables. If you look along this left-hand side, you'll see node, node_access, node _common_statistics, etcetera, but let's go ahead and do that we'll see how it works. So we go back to our site and Create content. This time I'm going to create a story and I'm going to say "Hello again, world!" The body is "Did you miss me?" and I'll scroll to the bottom and since this is a story, it will go to the front page automatically that's in Publishing options here, and Save. If we go back to our front page, we see there it is.

Now let's go back to our database and see exactly what happened. We click up here to go back to phpMyAdmin. Let's start with a node table. And there we see the first page we created earlier in this course, Hello, world! and there is our second one. It has a node ID, known as nId, a version ID, vId, and a lot of other information in here, but interestingly, it doesn't include the body of the node. That is actually referred to in another table, in node_revisions. If we go there, so you can see the Drupal actually keeps a complex set of relationships between node tables, and node revisions tables, and even the user tables. Here you can see which user created it, with the uId.

For those of you who want to get really deep into Drupal's database, I would like to suggest a few resources. The first one is a module called schema, and it's available at drupal.org/project/schema. A schema describes how a database is built, and this module will show you how your particular database is created and interacts a little bit better while you are running your Drupal site. The second module is called devel. Devel is for any kind of Drupal developer. It will show you the SQL commands that are being sent to and from the server, PHP messages, very useful if you are doing any sort of high-end Drupal development.

So, that gives you an introduction to how Drupal handles data. I know this knowledge may seem arcane now, but it will help you in many ways beyond this course. For example, if your Drupal site is broken, you might be able to fix it by manipulating the database directly. And a thorough understanding of the database is essential to importing legacy data or to writing custom modules that use data in some way. The functions of those 45 or so tables installed in Drupal by default, and the tables installed by dozens and dozens of common modules are a worthwhile study if you want to go further with your Drupal database knowledge. All it takes is phpMyAdmin, a Drupal installation and plenty of time.

Find answers to the most frequently asked questions about Drupal 6: Online Presentation of Data .


Expand all | Collapse all
please wait ...

Q: The exercise files for the course appear to be missing.

A: Full exercise files for this course were not provided because of the unusually large amount of images, modules, and other files that would have to be installed in specific places, in addition to the database. We hope to have a solution for future Drupal courses that installs all items in their correct places.

Q: During the course the author makes reference to being able to add data via the exercise files; however, the data is not in the exercise files.
A: During the recording of the title, some of the exercise files were removed, since the frequent updates to Drupal itself and to the modules that are needed to run the exercise files cause them to break.

The data referenced in the video consists mostly of names and addresses, which will need to be typed by hand.
 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

* Estimated file size

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Drupal 6: Online Presentation of Data.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.