Chapter 11 covered doing basic select statements (reads) from the MySQL database. This chapter covers the other essential functions encapsulated in the acronym CRUD—Create, Read, Update, and Delete. We’ll cover some more complicated selects as well as creating records, updating records, and deleting records; how to create tables; and more. Often, when installing Drupal (not using Acquia Dev Desktop), you’ll need to create a MYSQL database first. There are other times you’ll need to look in the database to check the data directly to see if there’s some problem. This chapter will round out your basic MYSQL skills so you can field more of the common issues you may face.

Creating a New Database

As part of our advanced coverage of MySQL, first we’ll cover how to create a new database. Most install instructions for Drupal require you to manually create a MySQL database. After you create the database, you can play around with the new database—looking at how to edit and add records, for example. Recall that Chapter 11 covered using phpMyAdmin. You’ll use phpMyAdmin to create the new database, and you’ll look at how to create a new database from the command line.

Using phpMyAdmin to Create a Database

To begin, open PhpMyAdmin and click the Databases tab (see Figure 16-1).

On this screen fill out the Create database text field with the name (see Figure 16-1), your new database will show in the list of databases. Click the name of your newly created database.

That’s all there is to it. To see how to create a database from the command line, read the next section. To continue your database work with phpMyAdmin, skip to the section “Creating Tables.”

Using the Command Line to Create a Database

Creating a database using phpMyAdmin is just one way to create a database. Alternatively, to create a database from the command line, you would connect to MySQL and then type the following:

create database mysqlPlayground;

To connect to MySQL from the command line on a Mac or another version of Linux, simply type

mysql –u username –ppassword

Notice that the -p argument has no space before the password, but the -u argument does have a space before the username.

Once you’ve created your database, you will need to type

use mysqlPlayground;

After typing this, you will be using the newly created database called “mysqlPlayground.” Now, all SQL statements that you issue will run against this database.

To switch to another database, you can issue the same command.

use database_name;

To show a list of all available databases, you can issue the following command:

show databases;

Now that you have successfully created your database, in the next section you’ll practice adding a new table to it.

Creating Tables

Now that you have your newly created database, you will create a new table (a table is the basic structure used to store data in the database). First, you’ll create a table using phpMyAdmin, and then you’ll create a table from the command line using a create statement.

Whenever you define a table, you must designate a primary key (which has a unique value). The primary key column for each row cannot be NULL (empty). Often the primary key will be an integer that autoincrements every time you insert a new row into the table.

Using phpMyAdmin to Create a Table

Again in phpMyAdmin, the process of creating a table easy. At the top of the page showing your new database, there is a Table name field to input the name of the new table you wish to create. You can also specify how many columns you would like your new table to have. After you name your table and specify the number of columns, you’ll see the screen in Figure 16-2.

You’ll notice in Figure 16-2 that I’ve clicked the Type column. Each column can be defined to contain many different types of data. The primary types you’ll see used are INT, which is an integer; VARCHAR, which is a string; DATE, DATETIME, or TIMESTAMP, which are all date types; and LONGTEXT, which is a really long string.

Using the Command Line to Create a Table

Here is an example create statement that you could run from the command line to create a new table. (Alternatively, you can run straight SQL from within the phpMyAdmin tool by clicking on the SQL tab.)

CREATE TABLE users
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
);

After running the preceding create table SQL code, the users table is now created in the new database (see Figure 16-3). The number in parentheses—for example, the number 255 in the line FirstName varchar(255)—specifies how many characters are allowed in the varchar. (Remember that varchar is just a string.) The line PRIMARY KEY (ID) specifies the ID column as this table’s primary key. The primary key in a table uniquely identifies each row in a table. It can be a single column or multiple columns whose values can uniquely identify a row in the table. The primary key is a primary idex for cataloging and searching a table and greatly improves database performance as it’s easier to identify data being sought.

If you want to look at the structure of a table on the MySQL command line, you can use the following SQL command:

describe table users;

Looking at the structure of a table is useful if you’re wondering what type of data your table is expecting so then you can write PHP code to correctly insert into the table. Or perhaps you’ve encountered a problem with inserting into the table and you want to look at the database table structure to identify if the length of a column is too short. There are plenty of great reasons to describe a table, for a closer look at how it’s constructed.

In Figure 16-4 you can see the series of commands issued to use the new database and then finally to describe the newly created users table.

With the table now created and the primary key designated, we can focus on filling that table with data.

Inserting Data into a Table

Inserting data into our table in phpMyAdmin is fairly straightforward. You just click the Insert tab and then fill out the form (see Figure 16-5). Note you don’t need to fill out the ID column. It will get its value automatically because we defined it as a primary key with autoincrement. As such it will just take on the next available integer value.

To insert a row using just the command line, we would issue the following SQL command:

INSERT INTO users (LastName, FirstName, Address, City) VALUES ('Barnett',
'Jiminy', '50 Rainbow Way', 'Ruby City');

You could also write the same INSERT statement as follows:

INSERT INTO users ('Barnett', 'Jiminy', '50 Rainbow Way', 'Ruby City');

Again, notice in both of the preceding lines of code that we ignore the id column because that will automatically get set because it’s an autoincrement column.

After we insert these two rows into our table, we can browse our table using phpMyAdmin (see Figure 16-6).

We could also view our newly created rows on the command line by just issuing the command

SELECT * from users;

The output on the command line will look like the output shown in Figure 16-7.

After you’ve created a table and filled it with data, you may find that you need to update it. You’ll do that next.

Updating Tables

To update a table row, you would write a SQL statement like the following, using phpMyAdmin:

To update a table from the command line, it would look as follows:

update users set FirstName = 'Barney' where id = 1;

In this case in the users table, the FirstName column will be updated to the value of Barney where the value of the id column is 1. This SQL statement will update the value of exactly one row (see Figure 16-8).

If you had omitted the WHERE clause, where id = 1, then all rows would have been updated with the FirstName column being Barney.

You can also easily update rows using phpMyAdmin, as seen in Figures 16-9 and 16-10. Simply click the pencil icon to edit a row.

In addition to editing your table data, you might have to delete data, when it is no longer needed. To find out how, read on.

Deleting Data

Using phpMyAdmin you can very easily delete data, as seen in in Figure 16-11. To do so, you can click the red X in the row you want to delete. Alternatively, you can select the row or rows you want to delete and then click the red X located under the table rows.

To delete data from the table, you can issue a statement like the following:

delete from users where id = 3;

Just as you would guess, the row of the table with the id of 3 would be deleted. But if you were to omit the WHERE clause, where id =3, then all rows would be deleted from the table.

Now that you’ve seen how to work with tables directly in MySQL, the next section looks at how to add and work with tables in Drupal modules.

Creating and Editing Tables in Drupal

To create tables in Drupal, the process is a bit different. You will be defining your tables in a your_module_name.install file, where if your module’s name was “hello_world,” then the file for your module would be hello_world.install. The MySQL tables you create will be created with your module upon installation of the module.

Creating the Table in the Drupal Module

The following code, which is found within the hello_world.install file, creates a table called hello_world for the Hello World module. Recall that the Hello World module was first introduced in Chapter 4; you’ll be adding to that module in this chapter.

<?php

/**
 * @file
 * Install, update and uninstall functions for the dblog module.
 */

/**
 * Implements hook_schema().
 */
function hello_world_schema() {
  $schema['hello_world'] = array(
    'description' => 'Table that contains information for the hello world
module.',
    'fields' => array(
      'hid' => array(
        'type' => 'serial',
        'not null' => TRUE,
        'description' => 'Primary Key: Unique hello world event ID.',
      ),
      'uid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'description' => 'The {users}.uid of the user who entered the data.',
      ),
      'message' => array(
        'type' => 'text',
        'not null' => TRUE,
        'size' => 'big',
        'description' => 'Text of log message to be passed into the t() function.',
      ),
      'timestamp' => array(
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
        'description' => 'Unix timestamp of when event occurred.',
      ),
    ),
    'primary key' => array('hid'),
  );

  return $schema;
}

This code in the hello_world.install file creates a table that looks like the one shown in Figure 16-12. I’ve used the command describe hello_world; within the MySQL command line.

In this section you created a custom table within your Hello World module. In the next section, you’ll interact with the table by doing some inserts, updates, and deletes.

Inserting Data in a Drupal Table

In this section you’re going to update the form you created in Chapter 14, which created nodes, so that you can instead create entries in the hello_world table. Next, you’ll select from the hello_world table to print output on the screen. Finally, you’ll put your custom form into a block. Essentially, you’re going to pull together all the skills you’ve learned so far to create this functionality.

Figure 16-13 shows the end result of what you’re going to create. You enter a message into the custom form that lives in a custom block, which you’ve put into a region on the right-hand side of the screen. Then, upon submission of the form in the custom block, you've created a new row in the hello_world table. The custom page then shows the output of what’s in the hello_world table, listing the contents of each row.

To have this code work in your browser, you need to have the with_install_file branch checked out and active in your modules/custom directory (or anywhere under the modules directory will do). You need to have the hello_world module enabled, and you need to place Hello World Block3 in the second sidebar. (Chapter 2 covered how to enable and place blocks within your Drupal site.)

Now, let’s go over the code that was needed to create what you saw in Figure 16-13.

Recall that the controller file is in the hello_world/src/Controller directory. Notice the controller now has a db_query statement to do a simple select from the hello_world table. Then in a foreach statement, we loop through the results and add them to the $content variable. Finally, the $content variable’s contents become the output to the screen.

<?php
/**
 * @file
 * Contains Drupalhello_worldHelloWorldController.
 */

namespace Drupalhello_worldController;

/**
 * Provides route responses for the hello world page example.
 */
class HelloWorldController {
  /**
   * Returns a simple hello world page.
   *
   * @return array
   *   A very simple renderable array is returned.
   */
  public function myCallbackMethod() {
    $content = '<p><b>Here are all the messages recorded in the hello_world
      custom table! </b></p>';
    $results = db_query('SELECT * from hello_world');
    foreach($results as $row) {
      $content .= 'Row: ' . $row->hid . ', Message: ' . $row->message . ',      
      Timestamp: ' . $row->timestamp . '</br>';
    }

    $element = array(
      '#markup' => $content,
    );
    return $element;
  }
}

Then I also changed the form in the FirstForm.php file, which lives here in Github: https://github.com/barnettech/drupal8_book/blob/master/hello_world/src/Form/FirstForm.php.

Remember, the custom form lives within the hello_world/src/Form directory within our hello_world module. The form just needs one text field to input the message that will be inserted into the hello_world custom table. Then I also modified the submit function public function submitForm to do a submit into the hello_world table with the result values we collect from the custom form.

<?php

/**
 * @file
 * Contains Drupalhello_worldFormFirstForm.
 */

namespace Drupalhello_worldForm;

use DrupalCoreFormFormBase;
use DrupalCoreFormFormStateInterface;

class FirstForm extends FormBase {

  /**
   *  {@inheritdoc}
   */
  public function getFormId() {
    return 'first_form';
  }

  /**
   * {@inheritdoc}
   *
   * @param SymfonyComponentHttpFoundationRequest $request
   *   The request object.
   */
  public function buildForm(array $form, FormStateInterface $form_state) {
    // Use the Form API to define form elements.
    // This array element will collect the message
    // to put into the message column of the
    // hello_world table in the database.
    $form['message'] = array(
      '#title' => t('Enter your message to insert into the hello_world table'),
      '#type' => 'textarea',
    );
    // This array element will create the submit button on the form.
    $form['submit'] = array(
      '#type' => 'submit',
      '#value' => t('Submit'),
    );
    return $form;
  }

  /**
   * {@inheritdoc}
   */
  public function validateForm(array &$form, FormStateInterface $form_state) {
    // Validate the form values.
  }

  /**
   * {@inheritdoc}
   */
  public function submitForm(array &$form, FormStateInterface $form_state) {
    //  This line gets the currently logged in user's information, we'll
    //  put this user's uid into the hello_world table in the uid column
    //  along with whatever he or she enters within the form on the
    //  /first/form page.
    $account = Drupal::currentUser();
    //  This line actually inserts the data submitted in the form
    //  on the /first/form page into the hello_world table in the database.
    db_query("INSERT INTO hello_world (uid, message, timestamp) values
    (:uid, :message, :timestamp)", array(':uid' => $account->id(), ':message' =>
    $form_state->getValue('message'), ':timestamp' => time()));
    //  The lines below just display a message to the user that their
    //  form was submitted successfully.
    drupal_set_message('Your form was submitted successfully, you typed in
    the body ' . $form_state->getValue('message'));
    drupal_set_message('A new row was entered into the hello_world table! ');
  }

}

You’ve also put your modified custom form into your custom block.

Remember, the blocks are within the /hello_world/src/Plugin/Block directory within the hello_world module. I just modified some text in this file that made more sense for the current example. Here are the contents of the HelloWorldBlock3.php file. Please note that even the code in the PHP comments, between the /**  **/ comment markers, is essential to get a Drupal block working. This code is not just PHP comments; it is, in fact, used by the system to identify the block id, admin label, and module name this block belongs to.

<?php

/**
 * @file
 * Contains Drupalhello_worldPluginBlockHelloWorldBlock3
 */

namespace Drupalhello_worldPluginBlock;

use DrupalCoreBlockBlockBase;
use DrupallockAnnotationBlock;
use DrupalCoreAnnotationTranslation;
use Drupalhello_worldFormFirstForm;

/**
 * Provides a simple block.
 *
 * @Block(
 *   id = "hello_world_block",
 *   admin_label = @Translation("Hello World Block3"),
 *   module = "hello_world"
 * )
 */
class HelloWorldBlock3 extends BlockBase {

  /**
   * Implements DrupallockBlockBase::blockBuild().
   */
  public function build() {
    $this->configuration['label'] = t('Autocomplete Form Example');
    // This line below actually puts the new form we created in the
    // $theForm variable.
    $theForm = Drupal::formBuilder()->getForm('Drupalhello_worldFormFirstForm');
    // We actually render the form in the code below.
    return array(
      '#markup' => drupal_render($theForm),
    );
  }
}

Figure 16-14 shows you that the inserts made it into the custom hello_world table.

And there you have it—a custom form, within a custom block, using a custom table, created in a .install file, and the contents of the custom table spat out as output in the custom page at the URL .../hello/world.

If you’ve come this far, you have learned a lot and are really becoming a proficient Drupal 8 programmer. Next, you’ll continue to dive deep in your understanding of how to use MySQL within Drupal. You’ll learn how to update and delete records within Drupal module code.

Updating and Deleting in Drupal

Following is an example of what an UPDATE statement would look like in Drupal:

db_query("UPDATE {hello_world} SET message = 'goodbye world' WHERE uid = :uid",
array(':uid' => 1));

This example, which you can adapt to your needs, would set all rows in the hello_world table to have the message column be “goodbye world” where the uid row is equal to 1.

Following is an example of what a DELETE statement would look like in Drupal:

db_query('DELETE FROM {hello_world} where uid = :uid',
    array(':uid' => 5));

This example DELETE statement, which you could put in your Drupal custom code if you found it appropriate, deletes all entries in the hello_world table where the uid is equal to 5. You can obviously adapt this SQL to fit your needs.

Summary

In this chapter you created and edited a basic table, and you learned about basic insert statements, update statements, and delete statements in MySQL. Then you practiced how to create a custom table in Drupal 8 using a .install file. You then used this new table in your custom Hello World module and saw how to put your custom form into a custom block. Finally, you inserted data (via the newly created form) into the new Drupal table you created yourself in your module’s .install file. After that exercise, you took a brief look at inserts, updates, and deletes within Drupal coding. This chapter really pulled together lot of techniques you’ve learned so far and showed off some great Drupal 8 programming skills.