Database: Custom Tables & CRUD Operations

When to Use Custom Tables

WordPress provides several data storage options:

Creating the Database Table

<?php
// src/Database/TasksTable.php

namespace TaskManagerDatabase;

class TasksTable {
    
    private string $table_name;
    private string $charset_collate;
    
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'task_manager_tasks';
        $this->charset_collate = $wpdb->get_charset_collate();
    }
    
    /**
     * Create the tasks table.
     */
    public static function create_table(): void {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'task_manager_tasks';
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            title varchar(255) NOT NULL,
            description longtext,
            status varchar(20) NOT NULL DEFAULT 'pending',
            priority tinyint(1) NOT NULL DEFAULT 1,
            due_date datetime DEFAULT NULL,
            assigned_to bigint(20) unsigned DEFAULT NULL,
            created_by bigint(20) unsigned NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY status (status),
            KEY assigned_to (assigned_to),
            KEY due_date (due_date),
            KEY created_by (created_by)
        ) {$charset_collate};";
        
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
        
        // Store table version for upgrades
        update_option( 'task_manager_db_version', '1.0.0' );
    }
    
    /**
     * Get table name.
     */
    public function get_table_name(): string {
        return $this->table_name;
    }
}

The Task Model

Create a data class to represent tasks:

<?php
// src/Models/Task.php

namespace TaskManagerModels;

class Task {
    
    public int $id = 0;
    public string $title = '';
    public string $description = '';
    public string $status = 'pending';
    public int $priority = 1;
    public ?string $due_date = null;
    public ?int $assigned_to = null;
    public int $created_by = 0;
    public string $created_at = '';
    public string $updated_at = '';
    
    /**
     * Create Task from database row.
     */
    public static function from_row( object $row ): self {
        $task = new self();
        
        $task->id = (int) $row->id;
        $task->title = $row->title;
        $task->description = $row->description ?? '';
        $task->status = $row->status;
        $task->priority = (int) $row->priority;
        $task->due_date = $row->due_date;
        $task->assigned_to = $row->assigned_to ? (int) $row->assigned_to : null;
        $task->created_by = (int) $row->created_by;
        $task->created_at = $row->created_at;
        $task->updated_at = $row->updated_at;
        
        return $task;
    }
    
    /**
     * Convert to array for database operations.
     */
    public function to_array(): array {
        return [
            'title'       => $this->title,
            'description' => $this->description,
            'status'      => $this->status,
            'priority'    => $this->priority,
            'due_date'    => $this->due_date,
            'assigned_to' => $this->assigned_to,
            'created_by'  => $this->created_by,
        ];
    }
    
    /**
     * Check if task is overdue.
     */
    public function is_overdue(): bool {
        if ( ! $this->due_date || $this->status === 'completed' ) {
            return false;
        }
        return strtotime( $this->due_date ) < time();
    }
    
    /**
     * Get status label.
     */
    public function get_status_label(): string {
        $labels = [
            'pending'     => __( 'Pending', 'task-manager' ),
            'in_progress' => __( 'In Progress', 'task-manager' ),
            'completed'   => __( 'Completed', 'task-manager' ),
        ];
        return $labels[ $this->status ] ?? $this->status;
    }
}

CRUD Operations

Now implement the Create, Read, Update, Delete methods:

<?php
// src/Database/TasksTable.php (continued)

namespace TaskManagerDatabase;

use TaskManagerModelsTask;

class TasksTable {
    
    // ... constructor and create_table() from above ...
    
    /**
     * CREATE: Insert a new task.
     */
    public function create( array $data ): int {
        global $wpdb;
        
        // Apply filter for extensibility
        $data = apply_filters( 'task_manager_before_create', $data );
        
        // Set defaults
        $data = wp_parse_args( $data, [
            'status'     => 'pending',
            'priority'   => 1,
            'created_by' => get_current_user_id(),
        ]);
        
        $result = $wpdb->insert(
            $this->table_name,
            [
                'title'       => sanitize_text_field( $data['title'] ),
                'description' => sanitize_textarea_field( $data['description'] ?? '' ),
                'status'      => sanitize_key( $data['status'] ),
                'priority'    => absint( $data['priority'] ),
                'due_date'    => $data['due_date'] ? sanitize_text_field( $data['due_date'] ) : null,
                'assigned_to' => $data['assigned_to'] ? absint( $data['assigned_to'] ) : null,
                'created_by'  => absint( $data['created_by'] ),
            ],
            [ '%s', '%s', '%s', '%d', '%s', '%d', '%d' ]
        );
        
        if ( false === $result ) {
            return 0;
        }
        
        $task_id = $wpdb->insert_id;
        
        do_action( 'task_manager_task_created', $task_id, $data );
        
        return $task_id;
    }
    
    /**
     * READ: Get a single task by ID.
     */
    public function find( int $id ): ?Task {
        global $wpdb;
        
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE id = %d",
                $id
            )
        );
        
        return $row ? Task::from_row( $row ) : null;
    }
    
    /**
     * READ: Get all tasks with optional filtering.
     */
    public function get_all( array $args = [] ): array {
        global $wpdb;
        
        $defaults = [
            'status'      => '',
            'assigned_to' => 0,
            'created_by'  => 0,
            'orderby'     => 'created_at',
            'order'       => 'DESC',
            'limit'       => 20,
            'offset'      => 0,
        ];
        
        $args = wp_parse_args( $args, $defaults );
        
        // Build WHERE clause
        $where = [ '1=1' ];
        $values = [];
        
        if ( $args['status'] ) {
            $where[] = 'status = %s';
            $values[] = $args['status'];
        }
        
        if ( $args['assigned_to'] ) {
            $where[] = 'assigned_to = %d';
            $values[] = $args['assigned_to'];
        }
        
        if ( $args['created_by'] ) {
            $where[] = 'created_by = %d';
            $values[] = $args['created_by'];
        }
        
        $where_sql = implode( ' AND ', $where );
        
        // Sanitize orderby
        $allowed_orderby = [ 'id', 'title', 'status', 'priority', 'due_date', 'created_at' ];
        $orderby = in_array( $args['orderby'], $allowed_orderby, true ) 
            ? $args['orderby'] 
            : 'created_at';
        $order = strtoupper( $args['order'] ) === 'ASC' ? 'ASC' : 'DESC';
        
        $sql = "SELECT * FROM {$this->table_name} 
                WHERE {$where_sql} 
                ORDER BY {$orderby} {$order} 
                LIMIT %d OFFSET %d";
        
        $values[] = $args['limit'];
        $values[] = $args['offset'];
        
        $results = $wpdb->get_results(
            $wpdb->prepare( $sql, $values )
        );
        
        return array_map( [ Task::class, 'from_row' ], $results );
    }
    
    /**
     * READ: Count tasks.
     */
    public function count( array $args = [] ): int {
        global $wpdb;
        
        $where = [ '1=1' ];
        $values = [];
        
        if ( ! empty( $args['status'] ) ) {
            $where[] = 'status = %s';
            $values[] = $args['status'];
        }
        
        $where_sql = implode( ' AND ', $where );
        
        if ( $values ) {
            return (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE {$where_sql}",
                    $values
                )
            );
        }
        
        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->table_name} WHERE {$where_sql}"
        );
    }
    
    /**
     * UPDATE: Update an existing task.
     */
    public function update( int $id, array $data ): bool {
        global $wpdb;
        
        $data = apply_filters( 'task_manager_before_update', $data, $id );
        
        $update_data = [];
        $format = [];
        
        if ( isset( $data['title'] ) ) {
            $update_data['title'] = sanitize_text_field( $data['title'] );
            $format[] = '%s';
        }
        
        if ( isset( $data['description'] ) ) {
            $update_data['description'] = sanitize_textarea_field( $data['description'] );
            $format[] = '%s';
        }
        
        if ( isset( $data['status'] ) ) {
            $update_data['status'] = sanitize_key( $data['status'] );
            $format[] = '%s';
        }
        
        if ( isset( $data['priority'] ) ) {
            $update_data['priority'] = absint( $data['priority'] );
            $format[] = '%d';
        }
        
        if ( array_key_exists( 'due_date', $data ) ) {
            $update_data['due_date'] = $data['due_date'] 
                ? sanitize_text_field( $data['due_date'] ) 
                : null;
            $format[] = '%s';
        }
        
        if ( array_key_exists( 'assigned_to', $data ) ) {
            $update_data['assigned_to'] = $data['assigned_to'] 
                ? absint( $data['assigned_to'] ) 
                : null;
            $format[] = '%d';
        }
        
        if ( empty( $update_data ) ) {
            return false;
        }
        
        $result = $wpdb->update(
            $this->table_name,
            $update_data,
            [ 'id' => $id ],
            $format,
            [ '%d' ]
        );
        
        if ( false !== $result ) {
            do_action( 'task_manager_task_updated', $id, $data );
        }
        
        return false !== $result;
    }
    
    /**
     * DELETE: Remove a task.
     */
    public function delete( int $id ): bool {
        global $wpdb;
        
        // Allow plugins to prevent deletion
        if ( ! apply_filters( 'task_manager_can_delete', true, $id ) ) {
            return false;
        }
        
        $result = $wpdb->delete(
            $this->table_name,
            [ 'id' => $id ],
            [ '%d' ]
        );
        
        if ( $result ) {
            do_action( 'task_manager_task_deleted', $id );
        }
        
        return (bool) $result;
    }
    
    /**
     * Bulk update status.
     */
    public function bulk_update_status( array $ids, string $status ): int {
        global $wpdb;
        
        if ( empty( $ids ) ) {
            return 0;
        }
        
        $ids = array_map( 'absint', $ids );
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        
        $affected = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$this->table_name} SET status = %s WHERE id IN ({$placeholders})",
                array_merge( [ $status ], $ids )
            )
        );
        
        return $affected ?: 0;
    }
}

Using Prepared Statements

Always use $wpdb->prepare() with placeholders:

// ✅ Correct - Using prepared statements
$wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$table} WHERE id = %d AND status = %s",
        $id,
        $status
    )
);

// ❌ DANGEROUS - SQL injection vulnerability!
$wpdb->get_row( "SELECT * FROM {$table} WHERE id = {$id}" );

Placeholder Types

Database Schema Upgrades

Handle table changes across plugin versions:

<?php
// In Activator.php or a dedicated Upgrader class

public static function maybe_upgrade(): void {
    $installed_version = get_option( 'task_manager_db_version', '0' );
    $current_version = '1.1.0';
    
    if ( version_compare( $installed_version, $current_version, '<' ) ) {
        self::upgrade_database( $installed_version );
        update_option( 'task_manager_db_version', $current_version );
    }
}

private static function upgrade_database( string $from_version ): void {
    global $wpdb;
    $table = $wpdb->prefix . 'task_manager_tasks';
    
    // Upgrade from 1.0.0 to 1.1.0: Add labels column
    if ( version_compare( $from_version, '1.1.0', '<' ) ) {
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN labels TEXT AFTER priority" );
    }
    
    // Future upgrades...
    // if ( version_compare( $from_version, '1.2.0', '<' ) ) { ... }
}

Next Steps

In Lesson 6, we'll expose our tasks via a REST API and build AJAX-powered interactions.