Essential CodeIgniter Database Admin Tool Setup (CI3)

Reading time 57 minutes

Ah, awesome! Building your own custom database admin tool in CodeIgniter is a fantastic project. It gives you a lightweight alternative to heavier tools like phpMyAdmin, directly integrated and securely controlled within your app.

Here is the complete, beginner-friendly blog post you requested, including all the required sections, security notes, code, and image prompts!

Table of Contents

1. Why Build a Custom CodeIgniter Database Admin Tool?
Structure: The Database Connection Configuration
2. Step 1: The Database Connection Configuration
3. Step 2: Securing Your Admin Controller (The Most Important Part!)
4. Step 3: Creating the Core Model (Dbadmin_model.php)
5. Step 4: The Brain—The Dbadmin.php Controller Logic
- Key Security Points in the Controller:
6. Step 5: The Views—Structure and Display

1. Why Build a Custom CodeIgniter Database Admin Tool?

As CodeIgniter 3 (CI3) developers, we frequently need a quick peek or a simple modification right inside our application’s database. While external tools like phpMyAdmin are great, wouldn’t it be easier if you had a CodeIgniter Database Admin utility baked directly into your project? 🤔

This tool is fast, lightweight, and most importantly, it allows you to enforce your own strict security rules from day one. You get to control exactly who can access it and what destructive operations they can run. It’s an essential utility for quick debugging and internal operations in 2025.

Structure: The Database Connection Configuration

├── application/
│   ├── config/
│   │   └── database.php
│   ├── controllers/
│   │   └── Dbadmin.php
│   ├── models/
│   │   └── Dbadmin_model.php
│   └── views/
│       └── dbadmin/
│           ├── alter.php
│           ├── browse.php
│           ├── footer.php
│           ├── header.php
│           ├── query.php
│           ├── structure.php
│           └── tables.php
└── index.php

2. Step 1: The Database Connection Configuration

Before we write any logic, we need to ensure CodeIgniter can talk to your database. Open your main configuration file and update the connection details.

File: application/config/database.php

PHP

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'db_username', // ⬅️ **CHANGE THIS**
    'password' => 'db_passwprd', // ⬅️ **CHANGE THIS**
    'database' => 'db_name',    // ⬅️ **CHANGE THIS**
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT === 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

CRITICAL: Make sure you replace 'db_username', 'db_passwprd', and 'db_name' with your actual database credentials!

3. Step 2: Securing Your Admin Controller (The Most Important Part!)

Security is not optional when building a web tool that can alter your database. We’re using IP Whitelisting in the controller’s __construct() method to ensure only allowed developers can access the page.

File: application/controllers/Dbadmin.php (The Constructor)

PHP

// application/controllers/Dbadmin.php
public function __construct()
{
    parent::__construct();
    $this->load->database();
    $this->load->helper(['url','file','download','form']);
    $this->load->library('pagination');
    $this->load->model('Dbadmin_model');

    // SECURITY: highly recommended to set these before enabling
    // IMPORTANT: Change 14.192.53.49 to your own public IP address.
    $allowed_ips = ['127.0.0.1','::1','14.192.00.00'];
    if (!in_array($this->input->ip_address(), $allowed_ips)) {
        show_404();
        // show_error('404 - Page Not Found!', 404);
        // NOTE: The line above is commented so you can use during dev — **ENABLE before production.**
    }

    // Basic HTTP Auth option (example) - uncomment this for extra security layer
    /*
    $username = 'admin';
    $password = 'strongpassword';
    if (!isset($_SERVER['PHP_AUTH_USER']) || $_SERVER['PHP_AUTH_USER'] !== $username || $_SERVER['PHP_AUTH_PW'] !== $password) {
        header('WWW-Authenticate: Basic realm="DB Admin"');
        header('HTTP/1.0 401 Unauthorized');
        echo 'Authentication required';
        exit;
    }
    */
}

4. Step 3: Creating the Core Model (`Dbadmin_model.php`)

The model is the dedicated layer for all database interactions. It keeps your controller clean and your database queries centralized. Notice the use of $this->db->escape_str($table) everywhere for protection!

File: application/models/Dbadmin_model.php

PHP

<?php
defined(‘BASEPATH’) OR exit(‘No direct script access allowed’);

class Dbadmin_model extends CI_Model {

    public function __construct()
    {
        parent::__construct();
    }

    public function list_tables()
    {
        return $this->db->list_tables();
    }

    public function get_columns($table)
    {
        $table = $this->db->escape_str($table);
        $q = $this->db->query(“SHOW COLUMNS FROM `{$table}`”);
        return $q->result_array();
    }

    public function count_rows($table)
    {
        $table = $this->db->escape_str($table);
        $q = $this->db->query(“SELECT COUNT(1) AS cnt FROM `{$table}`”);
        $r = $q->row_array();
        return isset($r[‘cnt’]) ? (int)$r[‘cnt’] : 0;
    }

    // get_rows($table, $limit, $offset) – secure retrieval with limits
    public function get_rows($table, $limit = 25, $offset = 0)
    {
        $table = $this->db->escape_str($table);
        $limit = (int)$limit;
        $offset = (int)$offset;
        // Use LIMIT offset, count form with parameter binding for safety
        $sql = “SELECT * FROM `{$table}` LIMIT ?, ?”;
        $q = $this->db->query($sql, [$offset, $limit]);
        return $q->result_array();
    }

    // execute ALTER (or other admin) statement
    public function run_alter($sql)
    {
        // final: ensure it’s ALTER TABLE to avoid accidental mutations
        if (!preg_match(‘/^\s*ALTER\s+TABLE/i’, $sql)) {
            throw new Exception(‘Only ALTER TABLE statements allowed here.’);
        }
        $this->db->query($sql);
        if ($this->db->affected_rows() === 0 && $this->db->trans_status() === FALSE) {
            // This might be too strict, as ALTER might not affect rows, but good for diagnostics
            // throw new Exception(‘Query failed: ‘.$this->db->last_query());
        }
        return true;
    }
}

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Dbadmin_model extends CI_Model {

    public function __construct()
    {
        parent::__construct();
    }

    public function list_tables()
    {
        return $this->db->list_tables();
    }

    public function get_columns($table)
    {
        $table = $this->db->escape_str($table);
        $q = $this->db->query("SHOW COLUMNS FROM `{$table}`");
        return $q->result_array();
    }

    public function count_rows($table)
    {
        $table = $this->db->escape_str($table);
        $q = $this->db->query("SELECT COUNT(1) AS cnt FROM `{$table}`");
        $r = $q->row_array();
        return isset($r['cnt']) ? (int)$r['cnt'] : 0;
    }

    // get_rows($table, $limit, $offset) - secure retrieval with limits
    public function get_rows($table, $limit = 25, $offset = 0)
    {
        $table = $this->db->escape_str($table);
        $limit = (int)$limit;
        $offset = (int)$offset;
        // Use LIMIT offset, count form with parameter binding for safety
        $sql = "SELECT * FROM `{$table}` LIMIT ?, ?";
        $q = $this->db->query($sql, [$offset, $limit]);
        return $q->result_array();
    }

    // execute ALTER (or other admin) statement
    public function run_alter($sql)
    {
        // final: ensure it's ALTER TABLE to avoid accidental mutations
        if (!preg_match('/^\s*ALTER\s+TABLE/i', $sql)) {
            throw new Exception('Only ALTER TABLE statements allowed here.');
        }
        $this->db->query($sql);
        if ($this->db->affected_rows() === 0 && $this->db->trans_status() === FALSE) {
            // This might be too strict, as ALTER might not affect rows, but good for diagnostics
            // throw new Exception('Query failed: '.$this->db->last_query());
        }
        return true;
    }
}

5. Step 4: The Brain—The `Dbadmin.php` Controller Logic

The controller ties everything together, handling the routes, cleaning user input, and preparing data for the views.

Key Security Points in the Controller:

XSS Cleaning: $this->security->xss_clean($table) is used on table names retrieved from the URL.
SELECT-Only Query Console: The query() method uses preg_match('/^\s*select/i', $sql_trim) to ensure only SELECT queries can be run from the web form, preventing accidental DELETE or DROP commands.

File: application/controllers/Dbadmin.php (Complete Code)

PHP

<?php
defined(‘BASEPATH’) OR exit(‘No direct script access allowed’);

class Dbadmin extends CI_Controller {
    protected $per_page = 25;
    protected $allowed_types = [
        ‘INT’,’TINYINT’,’SMALLINT’,’MEDIUMINT’,’BIGINT’,
        ‘VARCHAR’,’CHAR’,’TEXT’,’MEDIUMTEXT’,’LONGTEXT’,
        ‘DATE’,’DATETIME’,’TIMESTAMP’,’FLOAT’,’DOUBLE’,’DECIMAL’,’BOOLEAN’
    ];

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->load->helper([‘url’,’file’,’download’,’form’]);
        $this->load->library(‘pagination’);
        $this->load->model(‘Dbadmin_model’);

        // SECURITY: recommended to set these before enabling
        $allowed_ips = [‘127.0.0.1′,’::1′,’14.192.00.00′]; // modify or remove as needed
        if (!in_array($this->input->ip_address(), $allowed_ips)) {
            show_404();
        }
    }

    // list tables
    public function index()
    {
        $tables = $this->Dbadmin_model->list_tables();
        $data = [‘title’=>’DB Admin – Tables’,’tables’=>$tables];
        $this->load->view(‘dbadmin/header’, $data);
        $this->load->view(‘dbadmin/tables’, $data);
        $this->load->view(‘dbadmin/footer’);
    }

    // show structure
    public function structure($table = null)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();
        $columns = $this->Dbadmin_model->get_columns($table);
        $count = $this->Dbadmin_model->count_rows($table);
        $data = [‘title’=>”Structure: {$table}”,’table’=>$table,’columns’=>$columns,’count’=>$count];
        $this->load->view(‘dbadmin/header’, $data);
        $this->load->view(‘dbadmin/structure’, $data);
        $this->load->view(‘dbadmin/footer’);
    }

    // browse rows (pagination per table)
    public function browse($table = null, $offset = 0)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();

        $offset = (int)$offset;
        if ($this->input->get(‘page’) !== null) {
            $offset = (int)$this->input->get(‘page’);
        }

        $config[‘base_url’] = site_url(“dbadmin/browse/{$table}”);
        $config[‘total_rows’] = $this->Dbadmin_model->count_rows($table);
        $config[‘per_page’] = $this->per_page;
        $config[‘page_query_string’] = TRUE;
        $config[‘query_string_segment’] = ‘page’;
        $config[‘reuse_query_string’] = TRUE;
        $this->pagination->initialize($config);

        $rows = $this->Dbadmin_model->get_rows($table, $config[‘per_page’], $offset);
        $columns = [];
        if (!empty($rows)) $columns = array_keys($rows[0]);

        $data = [
            ‘title’ => “Browse: {$table}”,
            ‘table’ => $table,
            ‘rows’ => $rows,
            ‘columns’ => $columns,
            ‘pagination’ => $this->pagination->create_links(),
            ‘count’ => $config[‘total_rows’],
            ‘offset’ => $offset,
            ‘per_page’ => $config[‘per_page’]
        ];

        $this->load->view(‘dbadmin/header’, $data);
        $this->load->view(‘dbadmin/browse’, $data);
        $this->load->view(‘dbadmin/footer’);
    }

    // SQL Query page (SELECT-only by default)
    public function query()
    {
        $data[‘title’] = ‘DB Admin – SQL Query’;
        $sql = $this->input->post(‘sql’, TRUE);
        $result = null; $error = null;
        if ($sql) {
            $sql_trim = ltrim($sql);
            // Only allow SELECT queries
            if (preg_match(‘/^\s*select/i’, $sql_trim)) {
                try {
                    $q = $this->db->query($sql);
                    $result = $q->result_array();
                } catch (Exception $e) {
                    $error = $e->getMessage();
                }
            } else {
                $error = ‘Only SELECT queries are allowed from the web UI by default.’;
            }
        }
        $data[‘sql’]=$sql; $data[‘result’]=$result; $data[‘error’]=$error;
        $this->load->view(‘dbadmin/header’, $data);
        $this->load->view(‘dbadmin/query’, $data);
        $this->load->view(‘dbadmin/footer’);
    }

    // CSV export
    public function export($table = null)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();
        $rows = $this->Dbadmin_model->get_rows($table, 1000000, 0); // Get lots of rows
        if (empty($rows)) show_error(‘No rows to export or table empty.’);
        $filename = $table . ‘_’ . date(‘Ymd_His’) . ‘.csv’;
        $csv = fopen(‘php://temp’,’r+’);
        fputcsv($csv, array_keys($rows[0]));
        foreach ($rows as $r) fputcsv($csv, $r);
        rewind($csv); $csv_content = stream_get_contents($csv); fclose($csv);
        force_download($filename, $csv_content);
    }

    // ALTER table form
    public function alter($table = null)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();
        $columns = $this->Dbadmin_model->get_columns($table);
        $data = [
            ‘title’ => “Alter: {$table}”,
            ‘table’ => $table,
            ‘columns’ => $columns,
            ‘allowed_types’ => $this->allowed_types
        ];
        $this->load->view(‘dbadmin/header’, $data);
        $this->load->view(‘dbadmin/alter’, $data);
        $this->load->view(‘dbadmin/footer’);
    }

    // ALTER action – receives POST
    public function alter_action()
    {
        $this->load->library(‘form_validation’);

        $action = $this->input->post(‘action’, TRUE); // add|modify|drop|rename
        $table  = $this->security->xss_clean($this->input->post(‘table’, TRUE));
        if (!$table || !$action) {
            show_error(‘Invalid request’, 400);
        }

        // Basic validation of identifiers (only letters, numbers, underscore)
        $valid_ident = function($v){ return (bool)preg_match(‘/^[A-Za-z0-9_]+$/’, $v); };

        if (! $valid_ident($table)) show_error(‘Invalid table name’, 400);

        $sql = null;
        if ($action === ‘add’) {
            $col = $this->input->post(‘new_column’, TRUE);
            $type = strtoupper($this->input->post(‘new_type’, TRUE));
            $len = $this->input->post(‘new_length’, TRUE);
            if (! $valid_ident($col) ) show_error(‘Invalid column name’, 400);
            if (! in_array($type, $this->allowed_types)) show_error(‘Type not allowed’, 400);
            $type_sql = in_array($type, [‘VARCHAR’,’CHAR’,’DECIMAL’]) && $len ? “{$type}({$this->db->escape_str($len)})” : $type;
            $null = $this->input->post(‘new_null’) ? ‘NULL’ : ‘NOT NULL’;
            $default = $this->input->post(‘new_default’) !== null ? “DEFAULT ‘”.$this->db->escape_str($this->input->post(‘new_default’)).”‘” : ”;
            $sql = “ALTER TABLE `{$this->db->escape_str($table)}` ADD `{$this->db->escape_str($col)}` {$type_sql} {$null} {$default}”;
        } elseif ($action === ‘drop’) {
            $col = $this->input->post(‘col’, TRUE);
            if (! $valid_ident($col) ) show_error(‘Invalid column name’, 400);
            $sql = “ALTER TABLE `{$this->db->escape_str($table)}` DROP COLUMN `{$this->db->escape_str($col)}`”;
        } elseif ($action === ‘modify’) {
            $col = $this->input->post(‘col’, TRUE);
            $type = strtoupper($this->input->post(‘type’, TRUE));
            $len = $this->input->post(‘length’, TRUE);
            if (! $valid_ident($col) ) show_error(‘Invalid column name’, 400);
            if (! in_array($type, $this->allowed_types)) show_error(‘Type not allowed’, 400);
            $type_sql = in_array($type, [‘VARCHAR’,’CHAR’,’DECIMAL’]) && $len ? “{$type}({$this->db->escape_str($len)})” : $type;
            $null = $this->input->post(‘null’) ? ‘NULL’ : ‘NOT NULL’;
            $default = $this->input->post(‘default’) !== null ? “DEFAULT ‘”.$this->db->escape_str($this->input->post(‘default’)).”‘” : ”;
            $sql = “ALTER TABLE `{$this->db->escape_str($table)}` MODIFY `{$this->db->escape_str($col)}` {$type_sql} {$null} {$default}”;
        } elseif ($action === ‘rename’) {
            $old = $this->input->post(‘col_old’, TRUE);
            $new = $this->input->post(‘col_new’, TRUE);
            $type = strtoupper($this->input->post(‘col_type’, TRUE));
            $len = $this->input->post(‘col_length’, TRUE);
            if (! $valid_ident($old) || ! $valid_ident($new)) show_error(‘Invalid column name’, 400);
            if (! in_array($type, $this->allowed_types)) show_error(‘Type not allowed’, 400);
            $type_sql = in_array($type, [‘VARCHAR’,’CHAR’,’DECIMAL’]) && $len ? “{$type}({$this->db->escape_str($len)})” : $type;
            $null = $this->input->post(‘col_null’) ? ‘NULL’ : ‘NOT NULL’;
            $default = $this->input->post(‘col_default’) !== null ? “DEFAULT ‘”.$this->db->escape_str($this->input->post(‘col_default’)).”‘” : ”;
            // MySQL: CHANGE `old` `new` TYPE …
            $sql = “ALTER TABLE `{$this->db->escape_str($table)}` CHANGE `{$this->db->escape_str($old)}` `{$this->db->escape_str($new)}` {$type_sql} {$null} {$default}”;
        } else {
            show_error(‘Unsupported action’, 400);
        }

        // final safety check: allow only ALTER queries from this function
        if (!preg_match(‘/^\s*ALTER\s+TABLE/i’, $sql)) {
            show_error(‘Refusing to run non-ALTER SQL’, 400);
        }

        // Run via model
        try {
            $res = $this->Dbadmin_model->run_alter($sql);
            $this->session->set_flashdata(‘dbadmin_msg’, ‘Success: Alter executed.’);
        } catch (Exception $e) {
            $this->session->set_flashdata(‘dbadmin_error’, ‘Error: ‘.$e->getMessage());
        }

        redirect(‘dbadmin/alter/’.rawurlencode($table));
    }
}

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Dbadmin extends CI_Controller {
    protected $per_page = 25;
    protected $allowed_types = [
        'INT','TINYINT','SMALLINT','MEDIUMINT','BIGINT',
        'VARCHAR','CHAR','TEXT','MEDIUMTEXT','LONGTEXT',
        'DATE','DATETIME','TIMESTAMP','FLOAT','DOUBLE','DECIMAL','BOOLEAN'
    ];

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->load->helper(['url','file','download','form']);
        $this->load->library('pagination');
        $this->load->model('Dbadmin_model');

        // SECURITY: recommended to set these before enabling
        $allowed_ips = ['127.0.0.1','::1','14.192.00.00']; // modify or remove as needed
        if (!in_array($this->input->ip_address(), $allowed_ips)) {
            show_404();
        }
    }

    // list tables
    public function index()
    {
        $tables = $this->Dbadmin_model->list_tables();
        $data = ['title'=>'DB Admin - Tables','tables'=>$tables];
        $this->load->view('dbadmin/header', $data);
        $this->load->view('dbadmin/tables', $data);
        $this->load->view('dbadmin/footer');
    }

    // show structure
    public function structure($table = null)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();
        $columns = $this->Dbadmin_model->get_columns($table);
        $count = $this->Dbadmin_model->count_rows($table);
        $data = ['title'=>"Structure: {$table}",'table'=>$table,'columns'=>$columns,'count'=>$count];
        $this->load->view('dbadmin/header', $data);
        $this->load->view('dbadmin/structure', $data);
        $this->load->view('dbadmin/footer');
    }

    // browse rows (pagination per table)
    public function browse($table = null, $offset = 0)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();

        $offset = (int)$offset;
        if ($this->input->get('page') !== null) {
            $offset = (int)$this->input->get('page');
        }

        $config['base_url'] = site_url("dbadmin/browse/{$table}");
        $config['total_rows'] = $this->Dbadmin_model->count_rows($table);
        $config['per_page'] = $this->per_page;
        $config['page_query_string'] = TRUE;
        $config['query_string_segment'] = 'page';
        $config['reuse_query_string'] = TRUE;
        $this->pagination->initialize($config);

        $rows = $this->Dbadmin_model->get_rows($table, $config['per_page'], $offset);
        $columns = [];
        if (!empty($rows)) $columns = array_keys($rows[0]);

        $data = [
            'title' => "Browse: {$table}",
            'table' => $table,
            'rows' => $rows,
            'columns' => $columns,
            'pagination' => $this->pagination->create_links(),
            'count' => $config['total_rows'],
            'offset' => $offset,
            'per_page' => $config['per_page']
        ];

        $this->load->view('dbadmin/header', $data);
        $this->load->view('dbadmin/browse', $data);
        $this->load->view('dbadmin/footer');
    }

    // SQL Query page (SELECT-only by default)
    public function query()
    {
        $data['title'] = 'DB Admin - SQL Query';
        $sql = $this->input->post('sql', TRUE);
        $result = null; $error = null;
        if ($sql) {
            $sql_trim = ltrim($sql);
            // Only allow SELECT queries
            if (preg_match('/^\s*select/i', $sql_trim)) {
                try {
                    $q = $this->db->query($sql);
                    $result = $q->result_array();
                } catch (Exception $e) {
                    $error = $e->getMessage();
                }
            } else {
                $error = 'Only SELECT queries are allowed from the web UI by default.';
            }
        }
        $data['sql']=$sql; $data['result']=$result; $data['error']=$error;
        $this->load->view('dbadmin/header', $data);
        $this->load->view('dbadmin/query', $data);
        $this->load->view('dbadmin/footer');
    }

    // CSV export
    public function export($table = null)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();
        $rows = $this->Dbadmin_model->get_rows($table, 1000000, 0); // Get lots of rows
        if (empty($rows)) show_error('No rows to export or table empty.');
        $filename = $table . '_' . date('Ymd_His') . '.csv';
        $csv = fopen('php://temp','r+');
        fputcsv($csv, array_keys($rows[0]));
        foreach ($rows as $r) fputcsv($csv, $r);
        rewind($csv); $csv_content = stream_get_contents($csv); fclose($csv);
        force_download($filename, $csv_content);
    }

    // ALTER table form
    public function alter($table = null)
    {
        $table = $this->security->xss_clean($table);
        if (!$table) show_404();
        $columns = $this->Dbadmin_model->get_columns($table);
        $data = [
            'title' => "Alter: {$table}",
            'table' => $table,
            'columns' => $columns,
            'allowed_types' => $this->allowed_types
        ];
        $this->load->view('dbadmin/header', $data);
        $this->load->view('dbadmin/alter', $data);
        $this->load->view('dbadmin/footer');
    }

    // ALTER action - receives POST
    public function alter_action()
    {
        $this->load->library('form_validation');

        $action = $this->input->post('action', TRUE); // add|modify|drop|rename
        $table  = $this->security->xss_clean($this->input->post('table', TRUE));
        if (!$table || !$action) {
            show_error('Invalid request', 400);
        }

        // Basic validation of identifiers (only letters, numbers, underscore)
        $valid_ident = function($v){ return (bool)preg_match('/^[A-Za-z0-9_]+$/', $v); };

        if (! $valid_ident($table)) show_error('Invalid table name', 400);

        $sql = null;
        if ($action === 'add') {
            $col = $this->input->post('new_column', TRUE);
            $type = strtoupper($this->input->post('new_type', TRUE));
            $len = $this->input->post('new_length', TRUE);
            if (! $valid_ident($col) ) show_error('Invalid column name', 400);
            if (! in_array($type, $this->allowed_types)) show_error('Type not allowed', 400);
            $type_sql = in_array($type, ['VARCHAR','CHAR','DECIMAL']) && $len ? "{$type}({$this->db->escape_str($len)})" : $type;
            $null = $this->input->post('new_null') ? 'NULL' : 'NOT NULL';
            $default = $this->input->post('new_default') !== null ? "DEFAULT '".$this->db->escape_str($this->input->post('new_default'))."'" : '';
            $sql = "ALTER TABLE `{$this->db->escape_str($table)}` ADD `{$this->db->escape_str($col)}` {$type_sql} {$null} {$default}";
        } elseif ($action === 'drop') {
            $col = $this->input->post('col', TRUE);
            if (! $valid_ident($col) ) show_error('Invalid column name', 400);
            $sql = "ALTER TABLE `{$this->db->escape_str($table)}` DROP COLUMN `{$this->db->escape_str($col)}`";
        } elseif ($action === 'modify') {
            $col = $this->input->post('col', TRUE);
            $type = strtoupper($this->input->post('type', TRUE));
            $len = $this->input->post('length', TRUE);
            if (! $valid_ident($col) ) show_error('Invalid column name', 400);
            if (! in_array($type, $this->allowed_types)) show_error('Type not allowed', 400);
            $type_sql = in_array($type, ['VARCHAR','CHAR','DECIMAL']) && $len ? "{$type}({$this->db->escape_str($len)})" : $type;
            $null = $this->input->post('null') ? 'NULL' : 'NOT NULL';
            $default = $this->input->post('default') !== null ? "DEFAULT '".$this->db->escape_str($this->input->post('default'))."'" : '';
            $sql = "ALTER TABLE `{$this->db->escape_str($table)}` MODIFY `{$this->db->escape_str($col)}` {$type_sql} {$null} {$default}";
        } elseif ($action === 'rename') {
            $old = $this->input->post('col_old', TRUE);
            $new = $this->input->post('col_new', TRUE);
            $type = strtoupper($this->input->post('col_type', TRUE));
            $len = $this->input->post('col_length', TRUE);
            if (! $valid_ident($old) || ! $valid_ident($new)) show_error('Invalid column name', 400);
            if (! in_array($type, $this->allowed_types)) show_error('Type not allowed', 400);
            $type_sql = in_array($type, ['VARCHAR','CHAR','DECIMAL']) && $len ? "{$type}({$this->db->escape_str($len)})" : $type;
            $null = $this->input->post('col_null') ? 'NULL' : 'NOT NULL';
            $default = $this->input->post('col_default') !== null ? "DEFAULT '".$this->db->escape_str($this->input->post('col_default'))."'" : '';
            // MySQL: CHANGE `old` `new` TYPE ...
            $sql = "ALTER TABLE `{$this->db->escape_str($table)}` CHANGE `{$this->db->escape_str($old)}` `{$this->db->escape_str($new)}` {$type_sql} {$null} {$default}";
        } else {
            show_error('Unsupported action', 400);
        }

        // final safety check: allow only ALTER queries from this function
        if (!preg_match('/^\s*ALTER\s+TABLE/i', $sql)) {
            show_error('Refusing to run non-ALTER SQL', 400);
        }

        // Run via model
        try {
            $res = $this->Dbadmin_model->run_alter($sql);
            $this->session->set_flashdata('dbadmin_msg', 'Success: Alter executed.');
        } catch (Exception $e) {
            $this->session->set_flashdata('dbadmin_error', 'Error: '.$e->getMessage());
        }

        redirect('dbadmin/alter/'.rawurlencode($table));
    }
}

6. Step 5: The Views—Structure and Display

The final step is creating the minimal views needed to display the interface. We’ll use Bootstrap 4 for quick, clean styling (as seen in the header.php view).

Header and Footer (Bootstrap Setup)

File: application/views/dbadmin/header.php

PHP

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title><?= isset($title) ? html_escape($title) : 'DB Admin' ?></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
body { background: #f8f9fa; }
.container { margin-top: 30px; margin-bottom: 50px; }
.table-sm td, .table-sm th { padding: .3rem .5rem; }
.navbar-brand { font-weight: 600; }
.pagination { margin-top: 10px; }
pre.sql-result { background: #fff; border: 1px solid #ccc; padding: 10px; overflow-x: auto; }
</style>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
  <a class="navbar-brand" href="<?=site_url('dbadmin')?>">DB Admin</a>
  <div class="collapse navbar-collapse">
    <ul class="navbar-nav mr-auto">
      <li class="nav-item"><a class="nav-link" href="<?=site_url('dbadmin')?>">Tables</a></li>
      <li class="nav-item"><a class="nav-link" href="<?=site_url('dbadmin/query')?>">SQL Query</a></li>
    </ul>
  </div>
</nav>
<div class="container">

File: application/views/dbadmin/footer.php

PHP

</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Listing Tables (`tables.php`)

File: application/views/dbadmin/tables.php

PHP

<h4>Database Tables</h4>
<hr>

<table class="table table-bordered table-hover table-sm">
<thead class="thead-light">
<tr>
  <th>Table Name</th>
  <th width="180">Actions</th>
</tr>
</thead>
<tbody>
<?php if(!empty($tables)): ?>
  <?php foreach($tables as $t): ?>
  <tr>
    <td><?= html_escape($t) ?></td>
    <td>
      <a href="<?=site_url('dbadmin/structure/'.$t)?>" class="btn btn-outline-secondary btn-sm">Structure</a>
      <a href="<?=site_url('dbadmin/browse/'.$t)?>" class="btn btn-outline-primary btn-sm">Browse</a>
      <a href="<?=site_url('dbadmin/export/'.$t)?>" class="btn btn-outline-success btn-sm">Export CSV</a>
      <a href="<?=site_url('dbadmin/alter/'.$t)?>" class="btn btn-outline-warning btn-sm">Alter</a>
    </td>
  </tr>
  <?php endforeach; ?>
<?php else: ?>
  <tr><td colspan="2">No tables found.</td></tr>
<?php endif; ?>
</tbody>
</table>

Browsing Data (`browse.php`)

File: application/views/dbadmin/browse.php

PHP

<h4>Browse Table: <?=html_escape($table)?></h4>
<p class="text-muted">Total rows: <b><?=html_escape($count)?></b></p>

<div class="mb-3">
  <a href="<?=site_url('dbadmin/structure/'.$table)?>" class="btn btn-outline-secondary btn-sm">Structure</a>
  <a href="<?=site_url('dbadmin/alter/'.$table)?>" class="btn btn-outline-warning btn-sm">Alter</a>
  <a href="<?=site_url('dbadmin/export/'.$table)?>" class="btn btn-outline-success btn-sm">Export CSV</a>
</div>

<?php if(!empty($rows)): ?>
  <div class="table-responsive">
  <table class="table table-striped table-bordered table-sm">
  <thead class="thead-light">
  <tr>
    <?php foreach($columns as $c): ?>
      <th><?=html_escape($c)?></th>
    <?php endforeach; ?>
  </tr>
  </thead>
  <tbody>
  <?php foreach($rows as $r): ?>
  <tr>
    <?php foreach($columns as $c): ?>
      <td><?=html_escape($r[$c])?></td>
    <?php endforeach; ?>
  </tr>
  <?php endforeach; ?>
  </tbody>
  </table>
  </div>

  <div class="mt-3">
    <?=$pagination?>
  </div>
<?php else: ?>
  <div class="alert alert-info">No records found in this table.</div>
<?php endif; ?>

Running SQL Queries (`query.php`)

File: application/views/dbadmin/query.php

PHP

<h4>SQL Query Console</h4>
<p class="text-muted">⚠️ Only <b>SELECT</b> queries are allowed for safety.</p>

<form method="post" action="<?=site_url('dbadmin/query')?>">
  <div class="form-group">
    <textarea name="sql" class="form-control" rows="5" placeholder="Enter SQL here"><?=html_escape($sql ?? '')?></textarea>
  </div>
  <button class="btn btn-primary">Run Query</button>
</form>

<hr>

<?php if(!empty($error)): ?>
<div class="alert alert-danger"><b>Error:</b> <?=html_escape($error)?></div>
<?php endif; ?>

<?php if(!empty($result)): ?>
  <div class="table-responsive">
  <table class="table table-bordered table-sm">
    <thead class="thead-light">
      <tr>
        <?php foreach(array_keys($result[0]) as $col): ?>
          <th><?=html_escape($col)?></th>
        <?php endforeach; ?>
      </tr>
    </thead>
    <tbody>
      <?php foreach($result as $row): ?>
      <tr>
        <?php foreach($row as $val): ?>
          <td><?=html_escape($val)?></td>
        <?php endforeach; ?>
      </tr>
      <?php endforeach; ?>
    </tbody>
  </table>
  </div>
<?php elseif(isset($sql) && !$error): ?>
  <div class="alert alert-info">No rows returned.</div>
<?php endif; ?>

Altering Structure (`alter.php`) and Structure View (`structure.php`)

File: application/views/dbadmin/structure.php

PHP

<h4>Table Structure: <?=html_escape($table)?></h4>
<p class="text-muted">Total rows: <b><?=html_escape($count)?></b></p>

<div class="mb-3">
  <a href="<?=site_url('dbadmin/browse/'.$table)?>" class="btn btn-primary btn-sm">Browse Data</a>
  <a href="<?=site_url('dbadmin/alter/'.$table)?>" class="btn btn-warning btn-sm">Alter Table</a>
  <a href="<?=site_url('dbadmin/export/'.$table)?>" class="btn btn-success btn-sm">Export CSV</a>
</div>

<table class="table table-bordered table-hover table-sm">
<thead class="thead-light">
<tr>
  <th>Field</th>
  <th>Type</th>
  <th>Null</th>
  <th>Key</th>
  <th>Default</th>
  <th>Extra</th>
</tr>
</thead>
<tbody>
<?php foreach($columns as $c): ?>
<tr>
  <td><?=html_escape($c['Field'])?></td>
  <td><?=html_escape($c['Type'])?></td>
  <td><?=html_escape($c['Null'])?></td>
  <td><?=html_escape($c['Key'])?></td>
  <td><?=html_escape($c['Default'])?></td>
  <td><?=html_escape($c['Extra'])?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

File: application/views/dbadmin/alter.php

PHP

<div class=”container”>
  <h3>Alter table: <?=html_escape($table)?></h3>

  <?php if ($this->session->flashdata(‘dbadmin_msg’)): ?>
    <div class=”alert alert-success”><?=$this->session->flashdata(‘dbadmin_msg’)?></div>
  <?php endif; ?>
  <?php if ($this->session->flashdata(‘dbadmin_error’)): ?>
    <div class=”alert alert-danger”><?=$this->session->flashdata(‘dbadmin_error’)?></div>
  <?php endif; ?>

  <h5>Existing columns</h5>
  <table class=”table table-bordered”>
    <thead><tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr></thead>
    <tbody>
      <?php foreach($columns as $c): ?>
        <tr>
          <td><?=html_escape($c[‘Field’])?></td>
          <td><?=html_escape($c[‘Type’])?></td>
          <td><?=html_escape($c[‘Null’])?></td>
          <td><?=html_escape($c[‘Key’])?></td>
          <td><?=html_escape($c[‘Default’])?></td>
          <td><?=html_escape($c[‘Extra’])?></td>
        </tr>
      <?php endforeach; ?>
    </tbody>
  </table>

  <hr>

  <h5>Add column</h5>
  <form method=”post” action=”<?=site_url(‘dbadmin/alter_action’)?>”>
    <input type=”hidden” name=”action” value=”add”>
    <input type=”hidden” name=”table” value=”<?=html_escape($table)?>”>
    <div class=”row”>
      <div class=”col”><input name=”new_column” class=”form-control” placeholder=”column_name” required></div>
      <div class=”col”>
        <select name=”new_type” class=”form-control”>
          <?php foreach($allowed_types as $t): ?><option value=”<?=html_escape($t)?>”><?=$t?></option><?php endforeach; ?>
        </select>
      </div>
      <div class=”col”><input name=”new_length” class=”form-control” placeholder=”length (if needed)”></div>
      <div class=”col”><input name=”new_default” class=”form-control” placeholder=”default (optional)”></div>
      <div class=”col”><label class=”form-check-label”><input type=”checkbox” name=”new_null” value=”1″> NULL</label></div>
      <div class=”col”><button class=”btn btn-primary”>Add</button></div>
    </div>
  </form>

  <hr>
  <h5>Modify column</h5>
  <form method=”post” action=”<?=site_url(‘dbadmin/alter_action’)?>”>
    <input type=”hidden” name=”action” value=”modify”>
    <input type=”hidden” name=”table” value=”<?=html_escape($table)?>”>
    <div class=”row”>
      <div class=”col”>
        <select name=”col” class=”form-control”>
          <?php foreach($columns as $c): ?><option value=”<?=html_escape($c[‘Field’])?>”><?=html_escape($c[‘Field’])?></option><?php endforeach; ?>
        </select>
      </div>
      <div class=”col”>
        <select name=”type” class=”form-control”>
          <?php foreach($allowed_types as $t): ?><option value=”<?=html_escape($t)?>”><?=$t?></option><?php endforeach; ?>
        </select>
      </div>
      <div class=”col”><input name=”length” class=”form-control” placeholder=”length”></div>
      <div class=”col”><input name=”default” class=”form-control” placeholder=”default”></div>
      <div class=”col”><label><input type=”checkbox” name=”null” value=”1″> NULL</label></div>
      <div class=”col”><button class=”btn btn-warning”>Modify</button></div>
    </div>
  </form>

  <hr>
  <h5>Rename column</h5>
  <form method=”post” action=”<?=site_url(‘dbadmin/alter_action’)?>”>
    <input type=”hidden” name=”action” value=”rename”>
    <input type=”hidden” name=”table” value=”<?=html_escape($table)?>”>
    <div class=”row”>
      <div class=”col”>
        <select name=”col_old” class=”form-control”>
          <?php foreach($columns as $c): ?><option value=”<?=html_escape($c[‘Field’])?>”><?=html_escape($c[‘Field’])?></option><?php endforeach; ?>
        </select>
      </div>
      <div class=”col”><input name=”col_new” class=”form-control” placeholder=”new_name” required></div>
      <div class=”col”>
        <select name=”col_type” class=”form-control”>
          <?php foreach($allowed_types as $t): ?><option value=”<?=html_escape($t)?>”><?=$t?></option><?php endforeach; ?>
        </select>
      </div>
      <div class=”col”><input name=”col_length” class=”form-control” placeholder=”length”></div>
      <div class=”col”><input name=”col_default” class=”form-control” placeholder=”default”></div>
      <div class=”col”><label><input type=”checkbox” name=”col_null” value=”1″> NULL</label></div>
      <div class=”col”><button class=”btn btn-info”>Rename</button></div>
    </div>
  </form>

  <hr>
  <h5>Drop column</h5>
  <form method=”post” action=”<?=site_url(‘dbadmin/alter_action’)?>” onsubmit=”return confirm(‘Dropping column is irreversible — proceed?’)”>
    <input type=”hidden” name=”action” value=”drop”>
    <input type=”hidden” name=”table” value=”<?=html_escape($table)?>”>
    <div class=”row”>
      <div class=”col”>
        <select name=”col” class=”form-control”>
          <?php foreach($columns as $c): ?><option value=”<?=html_escape($c[‘Field’])?>”><?=html_escape($c[‘Field’])?></option><?php endforeach; ?>
        </select>
      </div>
      <div class=”col”><button class=”btn btn-danger”>Drop</button></div>
    </div>
  </form>
</div>

<div class="container">
  <h3>Alter table: <?=html_escape($table)?></h3>

  <?php if ($this->session->flashdata('dbadmin_msg')): ?>
    <div class="alert alert-success"><?=$this->session->flashdata('dbadmin_msg')?></div>
  <?php endif; ?>
  <?php if ($this->session->flashdata('dbadmin_error')): ?>
    <div class="alert alert-danger"><?=$this->session->flashdata('dbadmin_error')?></div>
  <?php endif; ?>

  <h5>Existing columns</h5>
  <table class="table table-bordered">
    <thead><tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr></thead>
    <tbody>
      <?php foreach($columns as $c): ?>
        <tr>
          <td><?=html_escape($c['Field'])?></td>
          <td><?=html_escape($c['Type'])?></td>
          <td><?=html_escape($c['Null'])?></td>
          <td><?=html_escape($c['Key'])?></td>
          <td><?=html_escape($c['Default'])?></td>
          <td><?=html_escape($c['Extra'])?></td>
        </tr>
      <?php endforeach; ?>
    </tbody>
  </table>

  <hr>

  <h5>Add column</h5>
  <form method="post" action="<?=site_url('dbadmin/alter_action')?>">
    <input type="hidden" name="action" value="add">
    <input type="hidden" name="table" value="<?=html_escape($table)?>">
    <div class="row">
      <div class="col"><input name="new_column" class="form-control" placeholder="column_name" required></div>
      <div class="col">
        <select name="new_type" class="form-control">
          <?php foreach($allowed_types as $t): ?><option value="<?=html_escape($t)?>"><?=$t?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col"><input name="new_length" class="form-control" placeholder="length (if needed)"></div>
      <div class="col"><input name="new_default" class="form-control" placeholder="default (optional)"></div>
      <div class="col"><label class="form-check-label"><input type="checkbox" name="new_null" value="1"> NULL</label></div>
      <div class="col"><button class="btn btn-primary">Add</button></div>
    </div>
  </form>

  <hr>
  <h5>Modify column</h5>
  <form method="post" action="<?=site_url('dbadmin/alter_action')?>">
    <input type="hidden" name="action" value="modify">
    <input type="hidden" name="table" value="<?=html_escape($table)?>">
    <div class="row">
      <div class="col">
        <select name="col" class="form-control">
          <?php foreach($columns as $c): ?><option value="<?=html_escape($c['Field'])?>"><?=html_escape($c['Field'])?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col">
        <select name="type" class="form-control">
          <?php foreach($allowed_types as $t): ?><option value="<?=html_escape($t)?>"><?=$t?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col"><input name="length" class="form-control" placeholder="length"></div>
      <div class="col"><input name="default" class="form-control" placeholder="default"></div>
      <div class="col"><label><input type="checkbox" name="null" value="1"> NULL</label></div>
      <div class="col"><button class="btn btn-warning">Modify</button></div>
    </div>
  </form>

  <hr>
  <h5>Rename column</h5>
  <form method="post" action="<?=site_url('dbadmin/alter_action')?>">
    <input type="hidden" name="action" value="rename">
    <input type="hidden" name="table" value="<?=html_escape($table)?>">
    <div class="row">
      <div class="col">
        <select name="col_old" class="form-control">
          <?php foreach($columns as $c): ?><option value="<?=html_escape($c['Field'])?>"><?=html_escape($c['Field'])?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col"><input name="col_new" class="form-control" placeholder="new_name" required></div>
      <div class="col">
        <select name="col_type" class="form-control">
          <?php foreach($allowed_types as $t): ?><option value="<?=html_escape($t)?>"><?=$t?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col"><input name="col_length" class="form-control" placeholder="length"></div>
      <div class="col"><input name="col_default" class="form-control" placeholder="default"></div>
      <div class="col"><label><input type="checkbox" name="col_null" value="1"> NULL</label></div>
      <div class="col"><button class="btn btn-info">Rename</button></div>
    </div>
  </form>

  <hr>
  <h5>Drop column</h5>
  <form method="post" action="<?=site_url('dbadmin/alter_action')?>" onsubmit="return confirm('Dropping column is irreversible — proceed?')">
    <input type="hidden" name="action" value="drop">
    <input type="hidden" name="table" value="<?=html_escape($table)?>">
    <div class="row">
      <div class="col">
        <select name="col" class="form-control">
          <?php foreach($columns as $c): ?><option value="<?=html_escape($c['Field'])?>"><?=html_escape($c['Field'])?></option><?php endforeach; ?>
        </select>
      </div>
      <div class="col"><button class="btn btn-danger">Drop</button></div>
    </div>
  </form>
</div>

7. Conclusion & Engagement

You’ve successfully implemented a secured, feature-rich CodeIgniter Database Admin tool using the MVC pattern! This tool gives you the power to:

Browse paginated table data easily.
View Structure and Export data to CSV.
Run safe SELECT queries from a web console.
Alter table structure (Add, Modify, Rename, Drop columns) with multiple security checks.

This is a powerful addition to your CI3 toolkit that saves massive amounts of time during development and testing.

Did you find this quick setup guide helpful for creating your CodeIgniter Database Admin tool? Let me know which security feature (IP Whitelist or SELECT-only Query) you think is the most important! 👍 / 👎 / Share with a CI friend!

541

0 Shares

Essential CodeIgniter Database Admin in 2025 (Quick Setup!)42 min read

1. Why Build a Custom CodeIgniter Database Admin Tool?

Structure: The Database Connection Configuration

2. Step 1: The Database Connection Configuration

3. Step 2: Securing Your Admin Controller (The Most Important Part!)

4. Step 3: Creating the Core Model (`Dbadmin_model.php`)

5. Step 4: The Brain—The `Dbadmin.php` Controller Logic

Key Security Points in the Controller:

6. Step 5: The Views—Structure and Display

Header and Footer (Bootstrap Setup)

Listing Tables (`tables.php`)

Browsing Data (`browse.php`)

Running SQL Queries (`query.php`)

Altering Structure (`alter.php`) and Structure View (`structure.php`)

7. Conclusion & Engagement

Comments

Leave a Reply Cancel reply

1. Why Build a Custom CodeIgniter Database Admin Tool?

Structure: The Database Connection Configuration

2. Step 1: The Database Connection Configuration

3. Step 2: Securing Your Admin Controller (The Most Important Part!)

4. Step 3: Creating the Core Model (Dbadmin_model.php)

5. Step 4: The Brain—The Dbadmin.php Controller Logic

Key Security Points in the Controller:

6. Step 5: The Views—Structure and Display

Header and Footer (Bootstrap Setup)

Listing Tables (tables.php)

Browsing Data (browse.php)

Running SQL Queries (query.php)

Altering Structure (alter.php) and Structure View (structure.php)

7. Conclusion & Engagement

Comments

Leave a Reply Cancel reply

4. Step 3: Creating the Core Model (`Dbadmin_model.php`)

5. Step 4: The Brain—The `Dbadmin.php` Controller Logic

Listing Tables (`tables.php`)

Browsing Data (`browse.php`)

Running SQL Queries (`query.php`)

Altering Structure (`alter.php`) and Structure View (`structure.php`)