How to create custom database table and show table data in Drupal 10

Imagine that you are designing a website for a university using Drupal 10. The university administration requires a separate page in the admin panel where they should be able to view a list of student records that are stored in a custom database table. This blog will help you understand the process of creating a custom module that will show these records in the Drupal Admin Panel.

Let’s start creating modules and solutions.

Folder Structure

Before jumping directly into implementing the module , It is important to understand the folder structure of the module so that it will help you create file at right place

student_records/  
├── student_records.info.yml  
├── student_records.routing.yml  
├── student_records.install  
└── src/  
    └── Controller/  
        └── StudentRecordsController.php

Firstly, Create a Custom Module

First, we will create a new module and name it student_records.

In the modules/custom folder, make a new folder and call it student_records.

mkdir -p modules/custom/student_records

Now let’s create a .info file to define the module definition , Like name, version, definition, etc. We will create a file named student_records.info.yml.

student_records.info.yml

name: 'Student Records'  
type: module
description: 'Displays student records from a custom database table in the admin panel.'
core_version_requirement: ^10
package: Custom
dependencies:  
  - drupal:system

Create a Custom Route

Now create a new route to get the admin page. Open a file with the name student_records.routing.yml in the student_records folder. Another point that it is essential to remember is that all names used in the module match the names of files in the student_records folder.

student_records.routing.yml

student_records.admin_page:  
  path: '/admin/student-records'  
  defaults:  
    _controller: '\Drupal\student_records\Controller\StudentRecordsController::content'  
    _title: 'Student Records'  
  requirements:  
    _permission: 'administer site configuration'

Create a Controller

Controller is used to process the request and return the response. Now inside the student_records directory create a src directory and inside src create another directory Controller. In this folder create a file which also has the name StudentRecordsController.php.

StudentRecordsController.php

<?php  
  
namespace Drupal\student_records\Controller;  
  
use Drupal\Core\Controller\ControllerBase;  
use Drupal\Core\Database\Database;  
  
class StudentRecordsController extends ControllerBase {  
  
  public function content() {  
    // Define table headers.  
    $header = [  
      'id' => $this->t('ID'),  
      'name' => $this->t('Name'),  
      'email' => $this->t('Email'),  
      'course' => $this->t('Course'),  
    ];  
  
    // Fetch data from custom table.  
    $rows = [];  
    $connection = Database::getConnection();  
    $query = $connection->select('student_records', 'sr')  
      ->fields('sr', ['id', 'name', 'email', 'course'])  
      ->execute();  
  
    // Populate rows array.  
    foreach ($query as $record) {  
      $rows[] = [  
        'id' => $record->id,  
        'name' => $record->name,  
        'email' => $record->email,  
        'course' => $record->course,  
      ];  
    }  
  
    // Build render array for the table.  
    $build = [  
      '#type' => 'table',  
      '#header' => $header,  
      '#rows' => $rows,  
      '#empty' => $this->t('No data found.'),  
    ];  
  
    return $build;  
  }  
}

Now Create Custom Database Table

For storing all records of the students, we have to create a new table with the name of student_records. for this you can writing an update hook in your module’s .install file. make sure to create file with name as student_records.install.

student_records.install

<?php  
  
/**  
 * Implements hook_schema().  
 */  
function student_records_schema() {  
  $schema['student_records'] = [  
    'fields' => [  
      'id' => [  
        'type' => 'serial',  
        'not null' => TRUE,  
      ],  
      'name' => [  
        'type' => 'varchar',  
        'length' => 255,  
        'not null' => TRUE,  
      ],  
      'email' => [  
        'type' => 'varchar',  
        'length' => 255,  
        'not null' => TRUE,  
      ],  
      'course' => [  
        'type' => 'varchar',  
        'length' => 255,  
        'not null' => TRUE,  
      ],  
    ],  
    'primary key' => ['id'],  
  ];  
  
  return $schema;  
}

Time To Enable The Module

Enable your custom module by running the following Drush command:Enable your custom module by running the following Drush command

drush en student_records -y

Lets add some sample data

INSERT INTO student_records (name, email, course) VALUES   
('John Doe', 'john.doe@example.com', 'Computer Science'),  
('Jane Smith', 'jane.smith@example.com', 'Mathematics'),  
('Alice Johnson', 'alice.johnson@example.com', 'Physics');

View the Data in backend

Now lets go to /admin/student-records to see the student records, which will be displayed in table format.

Conclusion

By so doing, you’ll be in a position to develop a vibrant admin page within your Drupal 10 site to manage students’ record found in a specific table. This approach offers a flexible method of handling and analyzing student information from the site’s admin panel hence improving the user experience.

I did it this way because Data display is fully under my control, however, some other developer may like to go with contributed modules such as ‘views’.

You May Also Like

How to create custom database table and Show table data on custom page in Drupal 10

Table of Contents