Database Views

Updating my contributions on the wiki has become a nightmare. Please check my svn at google for the latest revisions: http://code.google.com/p/tamer/ CI_TaMeR SVN

How would you like it if you could save your views in the database? You could save all your views in the db or just views you would like to give users access to change. I also save my flat files like Privacy Policy, ToS, About Us and success page etc in the db.

CREATE TABLE `view` (
  `id` int(11) NOT NULL auto_increment,
  `slug` varchar(20) NOT NULL,
  `Title` varchar(200) NOT NULL,
  `Body` longtext NOT NULL,
  `Groups` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `view`
-- 

INSERT INTO `view` (`id`, `slug`, `Title`, `Body`, `Groups`) VALUES (1, 'privacy_policy', 'Privacy Policy', '<h1>&lt;?=$this->title?&gt;</h1>\r\n', 1),
(2, 'about', 'About Us', '<h1>&lt;?=$this->title?&gt;</h1>', 1),
(3, 'terms_of_service', 'Terms of Service', '<h1>&lt;?=$this->title?&gt;</h1>', 1);

Controller file called pages.php:

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

class Pages extends Controller {

    function setVars(){
        $this->tpl['tpljs'][]  = 'title_to_slug';
        $this->tpl['head'] = '';
        $this->tpl['title'] = 'Admins Desk';
        $this->uid = $this->session->userdata('uid');
        $this->slug = $this->uri->segment(4, FALSE);
    }
    
    
    function __construct()
    {
        parent::Controller();    
        log_message('debug', "Admin: Controler Class Initialized");
        $this->setVars();
    }

    function index()
    {
        $query = $this->getdblist();
        foreach($query->result_array() as $k => $v) {
            $tbresult[$k] = $v;
        }
        $this->load->library('table');
        $this->table->set_heading('slug', 'Title');
        foreach($tbresult as $row){
            $s = $row['slug'];
            $row['slug'] = anchor('admin/pages/add_edit_page/'.$s,$s);
            $this->table->add_row($row);
        }            
        $this->tpl['body'] = $this->table->generate();
        $this->table->clear();
        $this->load->view('layout/blank', $this->tpl);
    }


    
    function add_edit_page()
    {
        $this->getdb();
        extract($this->dbdata);
        if(isset($_POST['submit_page']))
        {
            $this->postCheck();
            extract($this->vdata, EXTR_OVERWRITE);
        }
        
        $attributes = array('name' => 'page');
        $this->tpl['body'] =  form_open("admin/pages/add_edit_page/$this->slug", $attributes);
        if($this->action == 'edit'){
            $js = '';
            $form['Title'] = '<input type="text" name="Title" id="Title" value="'.$Title.'" maxlength="200" size="50" onkeyup="'.$js.'" />';
            $form[1]['slug'] =  '<input type="text" readonly="readonly" name="slug" value="'.$slug.'"/>';
        }elseif($this->action = 'add'){
            $js = "title_to_slug('page', 'Title', 'slug')";
            $form['Title'] = '<input type="text" name="Title" id="Title" value="'.$Title.'" maxlength="200" size="50" onkeyup="'.$js.'" />';
            $form[1]['slug'] =  '<input type="text" name="slug" value="'.$slug.'"/>';
        }else{
            log_message('error', "Admin: dbView->pages.php error with action");
        }
        $form[1]['Groups'] =  form_dropdown('Groups', user_groups(), $Groups);
        $form['Body'] =  '<textarea name="Body" rows="20" cols="80" />'.$Body.'</textarea>';
        $this->load->library('table');
        $this->tpl['body'] .= $this->table->form_table($form);
        $this->tpl['body'] .= '<div style="text-align: right;">&lt;input type="submit" name="submit_page" value="Submit" /&gt;</div>';
        $this->tpl['body'] .= '&lt;/form&gt;';
        $this->load->view('layout/blank', $this->tpl);
    }

    function postCheck()
    {
        $this->load->library('validation');
        $rules['Title']            = 'trim|min_length[5]|max_length[200]';
        $rules['slug']            = 'trim|min_length[5]|max_length[200]|alpha_dash';
        $rules['Groups']        = 'trim|required|max_length[30]|alpha_dash';
        $rules['Body']          = 'trim|required|';
        $rules['url']           = 'trim';
        $this->validation->set_rules($rules);
        $val = $this->validation->run();
        $this->vdata = (array) $this->dbdata;
        foreach($this->vdata as $k => $v){
            if(isset($this->validation->$k)){
                $this->vdata[$k] = $this->validation->$k;
            }
        }
        if ($val !== FALSE)
        {
            $this->editdb();
        }
    }
    
    function getdblist()
    {
        $this->db->select('slug, Title');
        $this->db->orderby("slug", "asc");
        $query = $this->db->get('view');
        if($query->num_rows() == 0) {
            redirect('admin/pages/add_edit_page', 'location');
        }else{
            return $query;
        }
    }    
    function getdb()
    {
        if($this->slug !== FALSE){
            $query = $this->db->getwhere('view', array('slug' => $this->slug), 1);
            if($query->num_rows() == 0) {
                redirect('admin/pages/add_edit_page', 'location');
            }else{

                $dbdata = $query->row_array();
                $this->vid = $dbdata['id'];
                $this->dbdata = delids($dbdata);
                $this->action = 'edit';
                //$this->tpl = array_merge($this->tpl, $this->dbdata);
            }
        }else{
            $fields = $this->db->list_fields('view');
            foreach ($fields as $field) $this->dbdata[$field] = '';
            $this->dbdata = delids($this->dbdata);
            $this->action = 'add';
            //$this->tpl = array_merge($this->tpl, $this->dbdata);
        }
    }

    function editdb()
    {
        if( ! isset($this->vid) || $this->vid < 1)
        {   # Create if it doesn't excist.
            $slug = $this->vdata['slug'];
            $this->db->insert('view', array('slug' => $slug)); 
            $this->vid = $this->db->insert_id();
        } 
        $data = new_value($this->dbdata, $this->vdata);
        if(count($data) != 0){
            //$this->load->helper('typography');
            //$data['Body'] = auto_typography($data['Body']);
            $this->db->where('id', $this->vid);
            $this->db->update('view', $data);
            $this->msg->setMsg('Your Record has been updated');
        }else{
            $this->msg->setError('Nothing to update');
        }
    }
}
?&gt;

I saved this in a helper file and do autoload arrays_helper.php

/************************** START UNSET/DEL IDS ****************************
*  Removes id, *_id and also first and last date fields from database array
****************************************************************************/
function delids($array)
{
    $array = (array) $array;
    if(isset($array[0])){
        foreach($array as $k => $v){
            $array[$k] = unsetids($v);
        }
    }else{
        $array = unsetids($array);
    }
    return $array;
}

function unsetids($array)
{
    $keys = array_keys($array);
    foreach($keys as $v){
        if($v=='id')unset($array[$v]);
        if($v=='last')unset($array[$v]);
        if($v=='first')unset($array[$v]);
        if(strstr($v, '_id'))unset($array[$v]);
    }
    return $array;
}

Category:Help::TipsAndTricks Category:Views