Просмотр архива Databasr MySQL Administration Interface


<?php

class Table extends MY_Controller
{
    public function data($name)
    {
        // check if table exists
        if (!$this->db->table_exists($name)) {
            show_404();
            return;
        }

        $this->displayAlert();

        // load table fields and data
        $fields = $this->db->field_data($name);
        $data = $this->db->get($name)->result_array();

        // load table size
        $this->db->select("table_schema, table_name, data_length, index_length");
        $this->db->where("table_name", $name);
        $size = $this->db->get("information_schema.tables", 1)->row();

        $table = [
            'fields' => $fields,
            'data' => $data,
            'name' => $name,
            'size' => $this->formatBytes($size->data_length)
        ];
        
        $this->template->assign('table', $table);

        $this->addCrumb($table['name'], 'table/data/' . $table['name']);

        $this->template->view('table/view');
    }

    public function structure($name)
    {
        // check if table exists
        if (!$this->db->table_exists($name)) {
            show_404();
            return;
        }

        // load table fields and data
        $fields = $this->db->field_data($name);

        $table = [
            'fields' => $fields,
            'name' => $name
        ];

        $this->template->assign('table', $table);

        $this->addCrumb($table['name'], 'table/data/' . $table['name']);
        $this->addCrumb('Structure', 'table/structure/' . $table['name']);

        $this->template->view('table/structure');
    }

    public function query()
    {
        $post = $this->input->post();

        $result = $this->db->query($post['query']);

        $error = $this->db->error();
        
        if(isset($error['code']) && $error['code']) {
            $this->alert("<strong>{$error['code']}</strong> {$error['message']}", "warning");
            $this->forward('table', 'data', $post['table_name']);
        }

        $affectedRows = $this->db->affected_rows();

        $this->alert("Your query has been executed successfully. <strong>{$affectedRows}</strong> rows are affected.");
        $this->forward('table', 'data', $post['table_name']);
    }
    
    public function editStructure($tableName)
    {
        if (!$this->db->table_exists($tableName)) {
            $this->forward();
        }

        $this->addCrumb($tableName, 'table/data/' . $tableName);
        $this->addCrumb("Edit table", '');

        $fields = $this->db->field_data($tableName);
        $this->template->assign('fields', $fields);

        $this->template->assign('tableName', $tableName);

        $this->template->view('table/edit');
    }

    public function updateStructure()
    {
        $post = $this->input->post();
        $fields = $post['fields'];
        
        $tableName = $post['table_name'];

        if(!$tableName) {
            $this->alert("No table name provided.", "error");
            $this->forward();
        }

        $this->load->dbforge();

        foreach($fields as $oldName => $field) {

            $updatedFields = [];
            // check if column name has been changed
            if($oldName != $field['name']) {
                $updatedFields[$oldName]['name'] = $field['name'];
            }

            $updatedFields[$oldName]['type'] = $field['type'];
            $updatedFields[$oldName]['constraint'] = $field['max_length'];

            $this->dbforge->modify_column($tableName, $updatedFields);
        }

        $this->alert("The table structure has been updated");
        $this->forward();
    }

    public function drop($tableName)
    {
        if (!$this->db->table_exists($tableName)) {
            $this->forward();
        }

        $this->load->dbforge();
        $this->dbforge->drop_table($tableName);

        $this->alert("The table <strong>{$tableName}</strong> has been dropped.");
        $this->forward();
    }

    public function insert($tableName)
    {
        $this->addCrumb($tableName, 'table/data/' . $tableName);
        $this->addCrumb("Insert record", '');

        $this->template->assign('mode', 'insert');

        $this->editRecord($tableName, 0);
    }

    public function editRecord($tableName, $internalId)
    {
        if (!$this->db->table_exists($tableName)) {
            $this->forward();
        }

        $fields = $this->db->field_data($tableName);
        $fields = json_decode(json_encode($fields), true);

        $this->template->assign('tableName', $tableName);

        if($internalId) {

            $this->addCrumb($tableName, 'table/data/' . $tableName);
            $this->addCrumb("Edit record", '');

            $this->template->assign('mode', 'update');

            $this->db->limit(1, $internalId-1);
            $record = $this->db->get($tableName)->row_array();

            foreach($fields as &$field) {

                if(!isset($record[$field['name']])) {
                    continue;
                }

                $field['value'] = $record[$field['name']];
            }
        }

        $this->template->assign('fields', $fields);
        $this->template->view('table/editrecord');
    }
    
    public function updateRecord()
    {
        $post = $this->input->post();

        if($post['mode'] == "insert") {

            $this->db->insert($post['table_name'], $post['fields']);

            $this->alert("The record has been inserted successfully");
            $this->forward('table', 'data', $post['table_name']);
            return;
        }

        if(!$post['table_name'] || !$post['old_fields'] || !$post['fields']) {
            $this->alert("There is an error occured", "error");
            $this->forward();
        }

        // select old record
        foreach($post['old_fields'] as $key => $value) {
            $this->db->where($key, $value);
        }

        $this->db->update($post['table_name'], $post['fields']);
        

        $this->alert("Record has been updated successfully");
        $this->forward('table', 'data', $post['table_name']);
    }

    public function dropRecord($tableName, $internalId)
    {
        if (!$this->db->table_exists($tableName)) {
            $this->forward();
        }

        $this->db->limit(1, $internalId-1);
        $record = $this->db->get($tableName)->row_array();
        
        foreach($record as $key => $value) {
            $this->db->where($key, $value);
        }

        $success = $this->db->delete($tableName);

        if($success) {
           $this->alert("The record has been removed successfully");
        } else {
            $this->alert("The record could not been removed", "error");
        }

        $this->forward('table', 'data', $tableName);
    }

    public function truncateTable($tableName)
    {
        if (!$this->db->table_exists($tableName)) {
            $this->forward();
        }

        $this->db->truncate($tableName);

        $this->alert("The table has been truncated succesfully.");
        $this->forward('table', 'data', $tableName);
    }
}