This chapter shows how you can have a Drupal module do more than one thing—in this case the hello_world module creates a custom page and you will now create a custom block. Rather than create a brand-new module to create the custom block, you’ll just add code to the existing module. This chapter also explores how to construct basic queries to pull information out of Drupal’s back-end datastore, which for most Drupal installations will be a MySQL database (although hooking up other database types to Drupal is possible).

Creating Your First Block Programmatically

In this section you will create some code within the custom module that allows you to, with code, create a block that shows up within Drupal’s block system. Then, through the block system (covered in Chapter 3), you can place the block you will create in code into a region of Drupal—the header, a sidebar, the footer, or any other region of the theme. Being able to create a block in a module makes it possible to use HTML, CSS, JavaScript, and PHP to construct any block imaginable. As in previous chapters, you can think of the code that follows as a template that you can copy, paste, and modify. Also, note that the PHP comments in Drupal 8 modules actually do matter and need to be syntactically correct just like the rest of the PHP code in the module.

1. Create all the necessary directories (check Github to get a better visual of the directory structure). On my local machine (a Mac) the directory structure looks as follows: /Users/jbarnett/Sites/drupal8/drupal/modules/custom/drupal8_book/hello_world/src/Plugin/Block.
2. Open your code editor and create the file HelloWorldBlock.php file within the Block directory. This step is most important. If you are creating your own custom block and name your module something other than “hello_world,” replace the preceding directory name hello_world with your module’s name. Type this code within your HelloWorldBlock.php file:

   <?php
   
        /**
         * @file
         * Contains Drupalhello_worldPluginBlockHelloWorldBlock1
         */
   
        namespace Drupalhello_worldPluginBlock;
   
        use DrupalCoreBlockBlockBase;
        use DrupallockAnnotationBlock;
        use DrupalCoreAnnotationTranslation;
   
        /**
         * Provides a simple block.
         *
         * @Block(
         *   id = "hello_world_block1",
         *   admin_label = @Translation("Hello World Block1"),
         *   module = "hello_world"
         * )
         */
        class HelloWorldBlock extends BlockBase {
   
          /**
           * Implements DrupallockBlockBase::blockBuild().
           */
   
          public function build() {
            $this->configuration['label'] = t('Hello World Block1');
            return array(
              '#markup' => t('Hello from a custom block'),
            );
          }
        }

   The preceding code provides for a simple block that displays the name Hello World Block 1.

3. Save the file.
4. Place the new block you created in the block system into a region on your site. Chapter 3 already covered how to do this, but as a refresher, click Menu in the black admin bar and then click the Structure link.
5. Click the Block layout option (see Figure 11-1).

   

   Figure 11-1. Clicking the Block layout option places your custom block on the page

6. With the screen shown in Figure 11-2 now displayed, click the “Place block” button next to the region where you would like to place your block.

   

   Figure 11-2. Click the “Place block” button next to the region you would like to place your block

7. A list of available blocks will appear, you’ll then click the button “Place block” to the right of  “Hello World Block 1”.  Then you’ll see a screen (Figure 11-3) where you can choose to restrict visibility of the block, by content type, to only certain pages, or roles.

   

   Figure 11-3. Restrict the visibility of your block

8. Click the Save block button.

Figure 11-4 shows how the blocks look after I place the new block in the right sidebar using the block system.

You just learned how to create a block in your custom module. In the next section, you find out how to grab data from Drupal’s back-end datastore, the MySQL database. To grab data from the database, you use what is known as a “query.”

Learning about the Drupal Database and Using a MySQL Query in a Custom Block

Now it’s time to have fun and run some MySQL queries in the Drupal database. In this case, you will list all recent blog titles in one of the blocks. To do that, you need to query the database, which is the store of information behind the scenes of Drupal, to get a list of the five most recently created blog posts. First, you need to access the Drupal database via a tool called phpMyAdmin (there are many other tools out there as well to help you directly access the database, which is a MySQL database). Then you can display the results of your query in Drupal within a block you create programmatically.

1. If you installed Drupal using DAMP (covered in Chapter 1), go to the Acquia Dev Desktop Control Panel and click “Manage my database” to open the phpMyAdmin tool, which allows you to manage the MySQL database. I’m using MAMP (not DAMP) on my Mac; if I click “Open start page” from the MAMP control panel, I see a link for phpMyAdmin in an upper left tab on the page. However you installed Apache and MySQL, read your installation’s documentation and open the phpMyAdmin tool.
2. Choose the database you installed Drupal within (see Figure 11-5). Out of the box, DAMP creates a database called acquia_drupal—click that one. I called mine drupal8 for the sake of this demo.

   

   Figure 11-5. A screenshot of phpMyAdmin

   Once you click the name of your Drupal database, a screen like the one shown in Figure 11-6 displays.

   

   Figure 11-6. The node and users table within the Drupal8 database

   Before continuing, remember that “nodes” are what Drupal programmers call “pieces of content” in the Drupal system. You create your content types (or use the default content types that install with Drupal) to create articles, basic pages, blogs, wikis, events, or other types of content. These pieces of content are stored in the database as what we call “nodes.”

   Each node has its data stored in the MySQL database in various tables. You will now look at some of these tables that comprise the node data. First, you will look at the node and node_field_data tables in phpMyAdmin to take a peek under the covers and see what the data looks like in the database.

3. Click the node_field_data table (see Figure 11-7). You should be able to see some basic information stored here about each of your pieces of content, including titles and other basic information.

   

   Figure 11-7. The node_field_data table within phpMyAdmin

    Note  If you’re a Drupal veteran, you’ll notice the title for nodes is no longer stored in the node table but instead is in this node_field_data table. There is still a node table, but it stores much more basic info than Drupal 7 did.

   The node and node_field_data table are connected by the nid (node id) column. So if, for example, the content’s unique node id (nid) was 5, then in the node table and the node_field_data table, both columns with a nid of 5 are related. That means the system will draw information from both tables (and many other tables with the unique nid of 5) to piece together all the data it needs to display the piece of content stored with the unique node id (nid) of 5. (This is why databases are often called relational databases. Databases stitch together all the data they need via these relationships the data tables have.)

   Notice in Figure 11-7 that the node table has columns and rows. Each column is referred to by what you see at the top, in row 1. There are nid, vid, type, langcode, and title columns. The nid is the unique id of the piece of content (the node); the title is the title of the node; and langcode refers to what language the node is written in (English is represented in the database as en, for example), and so on. The type column shows what content type the node is (recall the discussion of Drupal content types in Chapter 3: every node belongs to one of the content types defined in your Drupal installation). This exercise focuses on the column we’re most interested in: the title column.

4. Click the SQL tab toward the top of the page (shown in Figure 11-8).

   

   Figure 11-8. Running a query in phpMyAdmin

5. In the text area, type the following MySQL query to get information from the database on the five most recent articles (nodes) entered into Drupal. After typing the query (see Figure 11-8), click the Go button in the lower right corner of the page to run your query.

   select n.title, u.uid, n.created from node_field_data n,
   users u where u.uid = n.uid order by n.created desc limit 5

Because there were only two blog posts in my database (see Figure 11-8), that’s all the posts that are returned.

You see that the results returned the blog post title, the unique user id (UID) of the user who submitted the article (his or her UID), and the time the article was created in Unixtime.

Here’s an explanation of the query. You’ll notice that node is followed by n.which is an alias for the node_field_data table.

The first part of the query says select n.title. This could have been written as select node_field_data.title, but n.title is easier and allows you to learn about aliases, because they’re so common. So this code says, “Select the title, uid (unique user id), and created time for these nodes.”

The alias before these column names refers to which table to find the column. To ensure that the system finds the exact column you’re looking for, you must be explicit. Although you can omit the alias in MySQL if there is no ambiguity as to which columns you’re referring to (i.e., different tables don’t have duplicate column names), I recommend that if you’re dealing with more than one table to always prefix columns with a table alias. Note that this code also gives the users table an alias of u.

After the query selects which columns it would like returned (title, UID, and created), it has the “WHERE” clause. In this case the “WHERE” clause is where u.uid = n.uid and tells the database tool you’re using that the user and node_field_data tables are related when the uid in the two columns match. If a UID of 1 wrote the node, then you can also give me info from the users table for the user with uid of 1. In this way you can select information from multiple tables with one query statement.

Note that the query also includes order by n.created desc. This part of the code orders the results by the created column (which you know is from the node table because it has the alias n. prefacing it) in descending order. This will display the titles in reverse chronological order, with the newest content at the top. Note that asc stands for ascending and would be another way to sort data.

There are much fancier queries for sure, but these are the basics and can take you very far—you can relate as many tables as you like to assemble the data you need. Now, you’re ready to practice running a basic query in your phpMyAdmin web tool.

Using a Query to Display Data in a Custom Programmatically Created Block

Now, get ready to use a basic query within your custom Drupal module. Because you’re going to make a second custom block, you’ll need the file called HelloWorldBlock2.php to add in your query. You can create this second block by copying the HelloWorldBlock.php file.

1. Open your HelloWorldBlock.php file. Save a copy of it named HelloWorldBlock2.php in the same directory. So, for example, within my directory (/Users/jbarnett/Sites/drupal8/drupal/modules/custom/ drupal8_book/hello_world/src/Plugin/Block) on my Mac, I now have two files: HelloWorldBlock.php and HelloWorldBlock2.php.
2. Modify HelloWorldBlock2.php as shown here in bold.

   /**
    * @file
    * Contains Drupalhello_worldPluginBlockHelloWorldBlock2
    */
   
   namespace Drupalhello_worldPluginBlock;
   
   use DrupalCoreBlockBlockBase;
   use DrupallockAnnotationBlock;
   use DrupalCoreAnnotationTranslation;
   /**
    * Provides a simple block.
    *
    * @Block(
    *   id = "hello_world_block2",
    *   admin_label = @Translation("Hello World Block2"),
    *   module = "hello_world"
    * )
    */
   class HelloWorldBlock2 extends BlockBase {
   
     /**
      * Implements DrupallockBlockBase::blockBuild().
      */
     public function build() {
       $this->configuration['label'] = t('Hello World Block2');
       // You wrap your query in the db_query function and put
       // the results in the $result variable
       $result = db_query("select n.title, u.uid, n.created
       from node_field_data n, users u where u.uid = n.uid order
       by n.created desc limit 5");
       $content = "";
       // The result variable is an object with as many rows in
       // it as there were rows of data returned from your query,
       // you’re going to loop through these rows with the foreach
       // statement, put the individual row data into the $row
       // variable, add the title from that $row into the $content
       // variable, continue the loop, add the next title, and so
       // on until all the titles are listed in the $content
       // variable.
       // Then you assign $block['content'] to be equal to
       // $content and tada! Your titles from your query end up
       // in the first block on your screen.
       foreach($result as $row){
         // The ".=" rather than just "=" means take whatever
         // was already in the content variable and then
         // concatenate it with what comes after ".="
       $content .= $row->title . '</br>';
       }
       return array(
         '#markup' => $content,
       );
     }
   }

   The preceding code provides for a simple block that displays the name Hello World Block 2. To begin, you wrap your query in the db_query function and put the results in the $result variable. The $result variable is an object with as many rows in it as there were rows of data returned from your query. You’re going to loop through these rows with the foreach statement, put the individual row data into the $row variable, add the title from that $row into the $content variable, continue the loop, add the next title, and so on until all the titles are listed in the $content variable. Then you assign $block['content'] to be equal to $content and—ta-da!—your titles from your query end up in the first block on your screen.

3. Save your file and display your page. Your screen should look similar to that in Figure 11-9.

To modify the query in the preceding code example, you could add variables into the query, say, to show only titles of articles of type article (as opposed to type wiki or any other content type). The query would look as follows in Drupal:

$result = db_query("select n.title, u.uid, n.created from
node_field_data n, users u where u.uid = n.uid and n.type = :type
order by n.created desc limit 5", array("type" => "article"));

Notice that :type is a placeholder for a variable, and in the array following the query, you define what "type" is. In this case, you put in a hard-coded "article" to define the placeholder (variable in the query), but more often you would use array("type" => $article) where $article would dynamically be defined based on what was happening in the code at the time of execution.

Often, queries return multiple results, as in the preceding case. Those results are stored in the $results variable (the variable name doesn’t matter), and then you loop through the results like you did earlier, using the following code:

foreach($result as $row){
        $content .= $row->title . '</br>';
      }

But if you know there will be just one piece of data returned, like a nid, which is common, you can write code to just put the one piece of data (like the nid) into the variable directly. In doing so, you don’t need to loop through results to get to that one bit of data. The code looks as follows:

 $nid = db_query("SELECT n.nid from node where title = 'Drupal
Web Programming Essentials'")->fetchField();

In the preceding code, the single value is returned and is put within the $nid variable, preventing the need to loop through the data in a foreach statement. Notice that fetchField()fetches the single piece of data for you; there is no need to loop through the result for just one piece of data returned. This method only works when there is just one piece of data being returned.

We’ve breezed through learning some SQL, which can be immediately useful for you. The rest of the chapter gives you more basic knowledge of MySQL and reviews basic essential SQL usage. Understanding SQL is essential to programming and this coverage of basic SQL should provide a solid foundation on which to continue building your knowledge of databases.

MySQL Primer

MySQL uses the query language SQL (structured query language), which, as you’ve gathered by now, allows you to access data and make changes to back-end stored data. SQL has some variation when seen in different database systems (Oracle, SQL Server, MySQL), but it’s mostly the same. This SQL primer goes over the primary must-know syntax to get you going.

Wildcard Character in Queries

You can use the wildcard character (*) in your SQL select statement to return data for all columns in a given table. For example, consider the following query:

select * from node;

You’ll see that the result is all the rows from the node column and data from every column (see Figure 11-10). In contrast to the wildcard character, you could use this query to return all rows from the node table but only information from the nid column.

select nid from node;

Distinct

The distinct keyword in a select query allows you to ensure that all results in a particular column have no duplicate values. Let’s say you want to see a list of all the contributors to your web site—a list of all UID numbers of those who’ve contributed—but you don’t want to see any duplicates. Each user in the Drupal system has a unique UID. Following would be the query to use:

select distinct(uid) from node_field_data;

This query would ensure a list of results that does not show duplicate uid values in the list. You can still add other columns to the select statement, but you won’t receive any rows with duplicate UIDs.

Now consider the following query:

select distinct(uid), langcode from node_field_data;

This query will show a list of UIDs and what languages the user wrote their node in. A result of "en" in the langcode column means the node is in English.

WHERE

Recall that you encountered the where keyword earlier in this chapter. The where keyword allows you to filter your results or join with another table. Take the following query, which uses a where clause to limit the results, showing only the posts written by the user with the uid of 356:

select * from node_field_data where uid = 356;

Or you can join two (or more) tables to stitch together information from related tables to provide the results. Take the following query, which shows us a list of unique usernames that contributed to the site (rather than just a list of UIDs):

select distinct(nfd.uid), u.name from node_field_data nfd,
users u where u.uid = nfd.uid;

The preceding code joins the node_field_data and users tables. You do this because you want more information about the user referenced in the node_field_data table. In the node_field_data table, you had only the user’s UID, and joining (adding) the users table to the query allows you to grab more information about the user.

When you get to know your database’s schema (layout), you get to know what columns are related. In this case, the UID in the node_field_data table is a “foreign key” that can map to the users table, which also has a UID column. A foreign key implies that a column is identical in another table and the tables can be joined on this foreign key column. You’ll notice that table aliases are used once again. The users table has an alias of u, and u. prefaces the name column. This makes it clear to the system that you’re referring to the name column within the users table. The node_field_data table is given an alias of nfd. Notice the columns that are prefaced with the nfd alias. Remember, all columns in the query should be prefaced with the table’s alias name so it’s clear which columns are being referenced. See Figure 11-11.

AND and OR

You can use the AND and OR keywords in your SQL queries to further limit, or qualify, your results. Take the following query, which shows only results written by user 'jbarnett' and written in either English ('en') or with a language of undefined ('und'):

select distinct(nfd.uid), u.name, nfd.langcode from
node_field_data nfd, users u where u.uid = nfd.uid
and u.name = 'jbarnett' and nfd.langcode = 'en' or
nfd.langcode = 'und';

As you can see, using AND and OR keywords in a SQL query is extremely helpful in returning the desired results.

ORDER BY

Using order by in your query allows you to sort by a column either alphabetically or numerically; you can also sort them in order or reverse order. For example, the following query lists all of the site’s contributors sorted by the name column:

select distinct(nfd.uid), u.name, nfd.langcode from
node_field_data nfd, users u where u.uid = nfd.uid order
by u.name;

You can use the DESC or ASC keywords to specify if you want to order in descending order or ascending order. By default, if you don’t use these keywords (DESC or ASC), your results will sort in ascending order when you use order by.

select distinct(nfd.uid), u.name, nfd.langcode from
node_field_data nfd, users u where u.uid = nfd.uid order
by u.name DESC;

You can see the results of the preceding query in Figure 11-12.

Using SQL in Drupal

I did not introduce this in the preceding code, so that there would appear to be very little difference between using SQL in phpMyAdmin and using SQL in your Drupal code, but something to remember is that every time you reference a table in your SQL within your Drupal code you’ll be wrapping the table name in brackets: {table_name}. This convention allows for other Drupal functions to override queries.

As well, remember that where you would put a variable in a query, you use placeholders rather than putting the variables directly in your SQL, which would create a security hole allowing for user-provided values to be injected into the query (which could allow for malicious code to be injected into our queries, called a SQL injection attack). Further documentation on placeholders is available at https://drupal.org/node/310072.

For more documentation on the Drupal way to create Drupal queries, consult these drupal.org documentation pages: http://drupal.org/developing/api/database.

Finally, here are some links to drupal.org pages to get into more details on how to do CRUD within Drupal: these pages also go into much more depth than this Drupal primer, but feel free to peruse the more advanced concepts so you know to look for them someday when they may come in handy.

• More on queries: http://drupal.org/node/310072
• More on inserts: http://drupal.org/node/310079
• More on updates: http://drupal.org/node/310080
• More on deletes: http://drupal.org/node/310081

Chapter 16 delves into more advanced MySQL usage, covering insert statements, update statements, delete statements, and many other SQL keywords to help you do what you need to get done.

Summary

This chapter covered how to create a custom programmatically created block in Drupal that you placed into a specific region of your Drupal site. In addition, this chapter covered how to use basic MySQL queries to obtain the data results you would like to display to your users from the back-end database. Next, the chapter showed you how to use a query in another block you created programmatically to display a list of the most recent blog posts on your site. The chapter continued with a MySQL primer, showing how to use phpMyAdmin to browse your back-end database. Finally, you learned basic SQL to learn how to use SELECT and get the data you’re looking for in your MySQL database—the back-end datastore for Drupal.