Introduction to MySQL, tools and SELECT queries

Hi, I'm Chris Shattuck with BuildaModule dot com and in this video we're going to be talking about using MySQL in Drupal. If you were to split up Drupal into its two major components, the first would be the code and the second would be the database; whereas the code handles all the logic for the site, the database handles the storage of content and settings for the site. Eventually, if you're building out new features or modifying existing Drupal features, you'll need to be able to interact with the database.

I'm starting off this lesson assuming that you don't have a lot of experience with databases. So we're going to start at the very beginning and explain what a database actually is and how to interact with that with queries. So we're going to talk about queries a bit.

We'll look at how Drupal handles database interactions and how that's different than the default PHP functions that you could use. We'll look at a few tools for browsing database content and tables, and then we'll write our first SELECT query. So this is going to be your first query ever potentially and we'll keep it simple.

But we'll move on to a more complex version, and then we'll bring in multiple tables together by using joins. If you don't know what that is, that's okay. We'll cover it then.

We'll move on to using variables in queries which is how we can make our queries more secure and robust. And finally, we'll look at how to insert, update, and delete data from databases using those queries. Okay.

Let's go ahead and start by looking at what a database is. Probably the simplest way to describe a database is as a collection of tables. The tables themselves are comprised of rows and columns like a spreadsheet.

So if you've used Excel or any other spreadsheet program, you're already most to the way into understanding how a table is structured. The columns represent the types of data that are going to be stored in the table, whereas the rows represent individual entries into the table. Tables can relate to one another using keys.

So one table can have a column that represents a user ID and that user ID can be used on other columns to cross-reference or connect data from two different tables. The database contains content like nodes, users, comments, and settings like what modules are installed. So basically, every piece of content that exists in Drupal exists inside of the database.

There's a variety of different tools you can use to have a user interface for a database, but I'm going to go over to one of my favorites which is Sequel Pro and show you kind of a representation of the Drupal database that's actually running this presentation. So on the left-hand side we have the list of tables. Right now I have the node table selected but you can there are maybe 40 tables here.

On the right-hand side right now we're showing the structure of the table. So every entry here is actually a column in the table and we can modify these columns in different ways. We can select Types, different types, so there's integers, for example, which you see here as int.

There's varchar which is a way of storing a limited number of characters. And if we want to take a look at the content, we can switch over to the Content tab and this will show us all of the columns across the top and then every row here represents an actual entry into the node table. So each one of these represents a piece of content.

So if you want to get a bird's eye view of your content, you can use a program like this and just browse through the tables and see how it's structured and see where your content is being stored. This will help you when it comes to actually grabbing information from the database which we're going to be going over shortly. So now you have an idea of what a database is and its role in a Drupal install.

Let's go ahead and take a look at what a query is. I've taken a few examples of queries from Core Drupal modules so you can see what they look like when they're being used. So these three lines are actually lines of PHP code and the query is this sort of bright blue part in the middle.

You can usually tell a query because there's a lot of capital letters in it and it's inside of a function called db_query() or another function called db_query_range(). So the query part here is being highlighted on each one. And you can tell just by reading it that they get a feel for what a query does by just looking at it a lot of times.

So for example, this one is red, SELECT DISTINCT (bid) FROM (book). So it sounds like we're grabbing some information from book which is probably a table and this DISTINCT part is probably saying that we are getting unique items from the table. So when you're reading a query, a lot of times it's just like reading plain English until it gets a little bit more complex.

The basics of a query is that it allows you to interact with a database. If you're manipulating the database in some way, chances are you're going to be doing it either through a query or through a user interface like Sequel Pro that I just showed you. And with your queries you can do multiple things.

Probably the most common thing you'll do is select data from the database, but you can also change data, add it, or delete it as well. Before we get into actually writing queries, I want to talk to you a little bit about how Drupal handles database interactions. So if you're just learning MySQL, chances are you're drawing from multiple sources in order to learn.

But one important thing to realize is that in Drupal all the queries that we run are run through a database abstraction layer which is basically a set of PHP functions that provide some additional functionality above and beyond the default PHP functions. So the reason for this is that it handles a bunch of tasks for us without us having to really work very hard to get them. So the first thing that it does is it modifies queries so that they work with different types of databases.

So if we run our queries through these special functions like you saw, a db_query() in the examples above, then those queries will likely work in a multitude of different database types like Postgres and Oracle. Another thing that it does is it allows other modules to then modify your queries before they're run. We want to be able to expose as much in our code as possible to other modules so that they can extend our functionality without having to hack into our code.

And the third big thing that it does is it sanitizes data to protect against SQL injection attacks, and we'll look at some of that later on in this lesson. The tables are tightly interrelated so a lot of times we don't actually want to run a direct query against the database unless we're just selecting something. So if you're going to be altering some content in the database, it's important to look to see if that's actually what you want to do.

For example, here we have an example of deleting content from the node table, and this is the right way to delete something from a table using the database abstraction layer. But unfortunately, what this does is it leaves a whole lot of data behind in other tables that are associated with this node. So if we delete a node from the node table, we might not see it, other places in Drupal it might look like it's deleted, but in our database we have a whole bunch of data associated with it that wasn't removed because we didn't use the Drupal API in order to delete it.

So in this case, we would want to use a function called node_delete() to delete that content. So it's just important, if you're thinking about modifying some data directly in the database to make sure that you know that there's not an API call that will do it for you, a lot of times the API calls are actually easier to use than a direct query anyway. Okay.

I want to spend just a moment to talk about a few different tools you can use to layer a graphical interface on top of your database to easily manage it. Probably the most common interface that you'll see around is called PHPMyAdmin. PHPMyAdmin comes bundled with most hosting accounts and also if you use a stack installer to create a local development environment for yourself, then typically PHPMyAdmin will come bundled with that as well.

The benefit to PHPMyAdmin is that it's everywhere. So you can almost rely on the fact that it's going to be one tool that you can use to manage virtually any MySQL database that's on a shared hosting account. The downside is that it's a web-based client so it's a little bit slower than the desktop-based clients that are available out there.

And also each install of PHPMyAdmin is typically closely associated with the server that it's on. So if you're managing multiple databases on multiple servers, you'll typically have to log in to PHPMyAdmin on each one. In contrast, if you use a desktop plan like Navicat or Sequel Pro, then you can use that same interface, a single interface to connect with multiple databases on multiple servers.

So that can be a huge time saver. Also, these both run a lot faster than PHPMyAdmin and you have some additional tools available to you. So I particularly like Sequel Pro but it's only for the Mac.

Navicat, however, is cross-platform so you can use it on Linux, Mac, or Windows. Even though it's not a graphical interface, it's worth mentioning the command line here because if you use the desktop clients or PHPMyAdmin, you'll have a pretty good set of tools for which to manage your database. However, there is a whole subset of tools that aren't supported by those interfaces and the only way to really get to those is by using the command line.

So it's important eventually in your learning to get a bit of a grasp with how to use command line to manage MySQL, in particular importing and exporting databases and running SELECT queries so that you can search through a database when there's not a client available for you to use. Okay. Let's go ahead and move on to writing your first query.

So for your first query we're going to use a SELECT statement because that will be the most common one that you'll end up using in your code. So we're going to go through this piece by piece so you can see what's going on. First of all, we want to get the result set.

So our result set is a series of rows that match certain criteria. What we're doing is creating a variable called result and this is a convention that you'll see often in Drupal code. If you see the result variable, typically what it contains is a set of results from a database query.

And then what we're assigning that is the return value from running the db_query() function, and in order to get a return value from that we need to pass it a query. So this is our query right here. Let's break this into the different parts.

The first is the keyword SELECT which means that we're going to be running a SELECT query versus running an INSERT query or an UPDATE query. The asterisk that you see here indicates that we want to select all columns from this query. If we wanted to specify very particular columns, we could just list those here instea ...

Read the last half of the transcript