CRUD using CodeIgniter and EXTJS Grid – Part 1

July 10th, 2010 by aditia rahman / 22 Comments  

     

In this post I want to share how to create simple CRUD (create, read, update, delete) using codeigniter ExtJS editor grid. In this first part, we will setup the database schema, codeigniter and extjs. I limited this first part, only setup fetching data from database and pagination grid in extjs grid.

1. Create Database Schema

The table that we will use is two tables with “has one” relationship, namely users table with foreign key country_id and countries table that store all the countries data, so when we want to know the country name from the users we have to get from the countries table, and here the sql for creating the table.

--
-- Table structure for table `countries`
--

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=102 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(150) NOT NULL,
  `email` varchar(100) NOT NULL,
  `country_id` int(11) NOT NULL DEFAULT '1',
  `occupation` varchar(150) NOT NULL,
  `birthdate` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

2. Setup Codeigniter

Like we always do in previous tutorial, there are the files that we have to setup: config.php, database.php, autoload.php and route.php, all this files are inside the same folder in codeigniter namely “application/system/config”, and don’t forget to setup the extjs file.
config.php file

$config['base_url'] = "http://localhost/ci_extjs_crud/";

database.php file

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "ci_extjs_crud";
$db['default']['dbdriver'] = "mysql";

autoload.php file

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');

route.php file

$route['default_controller'] = "user";

we will create user class so set the default route to user and the extjs file looks on my project folder (inside codeigniter)

ci-ext-folder

I already remove only the used extjs file so, it much smaller than the fresh copy

3. Create The Controller

Create file image.php inside codeigniter controller folder and add this code below, this class only have 3 function. First function is to initialize the controller class, the index function only to render the view file and the ext_get_all function is to get the data from database that will displayed on the grid, you can see inside that function there are code to handle the pagination and the related table, the output from this function have to be in JSON format, so it will make easier to be displayed on Extjs grid.

<?php

class User extends Controller {

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

    public function index()
    {
        $this->load->view('user/index');
    }

    public function ext_get_all()
    {
        $start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
	$limit = isset($_REQUEST['limit']) ? $_REQUEST['limit'] : 10;

        $this->db->select('users.*, countries.country_name');
        $this->db->from('users');
        $this->db->join('countries',
                'countries.id = users.country_id');

        $this->db->limit($limit, $start);

        $query = $this->db->get();
        $results = $this->db->count_all('users');
        $arr = array();
        foreach ($query->result() as $obj)
        {
            $arr[] = $obj;
        }
        echo '{success:true,results:'. $results .
                ',rows:'.json_encode($arr).'}';
    }
}

4. Create The View

Create file index.php inside folder “application/views/user/”, just include the extjs file on the header that linked to file that we already setup on the step 2, and add this code below in the javascript tag (whatever you like whether in the separate file or in the same file)

<script type="text/javascript">
var BASE_URL = '<?php echo base_url(); ?>' + 'index.php/';
var BASE_PATH = '<?php echo base_url(); ?>';
var BASE_ICONS = BASE_PATH + 'assets/icons/';
Ext.onReady(function() {
    var strUsers = new Ext.data.Store({
        reader: new Ext.data.JsonReader({
            fields: [
                'id', 'fullname', 'email', 'country_id',
                'country_name', 'occupation', 'birthdate'
            ],
            root: 'rows', totalProperty: 'results'
        }),
        proxy: new Ext.data.HttpProxy({
            url: BASE_URL + 'user/ext_get_all',
            method: 'POST'
        })
    });

    var tbUsers = new Ext.Toolbar({
        items:[{
            text: 'Add',
            icon: BASE_ICONS + 'user_add.png'
        }, '-', {
            text: 'Delete',
            icon: BASE_ICONS + 'user_delete.png'
        }]
    });

    var cbGrid = new Ext.grid.CheckboxSelectionModel();

    var gridUsers = new Ext.grid.GridPanel({
        frame: true, border: true, stripeRows: true, sm: cbGrid,
        store: strUsers, loadMask: true, title: 'Users List',
        style: 'margin:0 auto;', height: 330, width: 722,
        columns: [
            cbGrid, {
                header: "Fullname",
                dataIndex: 'fullname',
                width: 180
            }, {
                header: "Email",
                dataIndex: 'email',
                width: 180
            }, {
                header: "Country",
                dataIndex: 'country_name',
                width: 120
            }, {
                header: "Occupation",
                dataIndex: 'occupation',
                width: 120
            }, {
                header: "Birth",
                dataIndex: 'birthdate',
                width: 80,
                renderer : Ext.util.Format.dateRenderer('d/m/Y')
            }
        ],
        tbar: tbUsers,
        bbar: new Ext.PagingToolbar({
            pageSize: 10,
            store: strUsers,
            displayInfo: true
        })
    });

    gridUsers.render('divgrid');
    strUsers.load();
});
</script>

The first screenshot from this tutorial is something like this and you can download all the code below, i have included the sql schema and all the extjs file that needed in this post.

ExtJS CodeIgniter-CRUD_Part_1

Download Source

        submit to reddit Delicious

22 Comments Leave a Comment Subscribe RSS

  • suresh.katiki says:

    This is excellent job. Thank you.

  • Jim says:

    This breaks the MVC model (your’re doing DB queries from the controller)

  • I want to use all four part of CRUD in a single application. How could i do this.

    I am using a single controller file user.php and i have placed all functions( ext_get_all(), ext_register(), ext_login()) in this. Now the problem is in my view file. I have placed extjs code for ext_get_all function in index.php file but is not working. So please help me. should i use different view file for ext_get_all() function ?

    • if I see your code below in ext_get_all() you don’t have to load the view again, this function is to sending the data in json format so it can be grabbed by the store component

  • This is my controller file user.php
    my_usession->logged_in)
    {
    $data['title'] = ‘User Index’;
    $this->load->view(‘user/index’, $data);
    }
    else
    {
    redirect(‘user/login’);
    }
    }

    public function login()
    {
    if ($this->my_usession->logged_in)
    {
    redirect(‘user/index’);
    }
    else
    {
    $data['title'] = ‘User Login’;
    $this->load->view(‘user/login’, $data);
    }
    }

    public function ext_is_unique_username()
    {
    $cond = array(‘username’ => $_POST['username']);
    $query = $this->db->get_where(‘users’, $cond);
    if ($query->num_rows() != 0)
    {
    echo 0;
    }
    else
    {
    echo 1;
    }
    }

    public function ext_is_unique_email()
    {
    $cond = array(‘email’ => $_POST['email']);
    $query = $this->db->get_where(‘users’, $cond);
    if ($query->num_rows() != 0)
    {
    echo 0;
    }
    else
    {
    echo 1;
    }
    }

    public function ext_logout()
    {
    $this->my_usession->unset_userdata(“user_id”);
    echo “{success:true}”;
    }

    public function ext_login()
    {
    $cond = array(
    ‘username’ => $_POST['username'],
    ‘password’ => $_POST['password']
    );
    $query = $this->db->get_where(‘users’, $cond);
    if ($query->num_rows() != 0)
    {
    $row = $query->row();
    $this->my_usession->set_userdata(‘user_id’, $row->id);
    $this->my_usession->set_userdata(‘username’, $row->username);
    echo “{success:true}”;
    }
    else
    {
    echo “{success:false, errors: { reason: ‘User not found !’ }}”;
    }
    }

    public function ext_register()
    {
    $data = array(
    ‘username’ => $_POST['username'],
    ‘password’ => $_POST['password'],
    ‘email’ => $_POST['email']
    );
    $this->db->insert(‘users’, $data);

    echo “{success:true}”;
    }

    public function ext_get_all()
    {

    $start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
    $limit = isset($_REQUEST['limit']) ? $_REQUEST['limit'] : 10;

    $this->db->select(‘users.*’);
    $this->db->from(‘users’);

    $this->db->limit($limit, $start);

    $query = $this->db->get();
    $results = $this->db->count_all(‘users’);
    $arr = array();
    foreach ($query->result() as $obj)
    {
    $arr[] = $obj;
    }
    $data['title']=”User Lists”;

    $data['list'] = ‘{success:true,results:’. $results .
    ‘,rows:’.json_encode($arr).’}’;
    $this->load->view(‘user/userlist’, $data);

    }

    }

  • This is my view file userlist.php

    load->view(‘header’); ?>

    var layout_west1 = new Ext.tree.TreePanel({
    region: ‘north’, title: ‘Menus’, height: 250, bodyStyle: ‘margin-bottom:6px;’,
    autoScroll: true, enableDD: false, rootVisible: false, id: ‘treePanel’,
    root: {
    text: ‘Menu’,
    expanded: true,
    nodeType: ‘async’,
    children: [{
    text: 'Menu 1',
    expanded: true,
    children: [{
    text: 'Menu 1.1',
    leaf: true
    }, {
    text: 'Menu 1.2',
    expanded: true,
    children: [{
    text: 'Menu 1.2.1',
    leaf: true
    }, {
    text: 'Menu 1.2.2',
    leaf: true
    }, {
    text: 'Menu 1.2.3',
    leaf: true
    }]
    }]
    }, {
    text: ‘Menu 2′,
    expanded: true,
    children: [{
    text: 'Menu 2.1',
    leaf: true
    }, {
    text: 'Menu 2.2',
    leaf: true
    }]
    }, {
    text: ‘Logout’,
    id: ‘logout’,
    icon: BASE_PATH + ‘assets/img/icons/minus-circle.png’,
    leaf: true
    }]
    },
    listeners: {
    click: function(n) {
    switch (n.id) {
    case ‘logout’:
    do_logout();
    break;
    }
    }
    }
    });

    var layout_west2 = new Ext.Panel({
    region: ‘center’, margin: ’10 0 0 0′, autoScroll: true,
    bodyStyle: ‘padding:10px;background:#eee;font-family:”Lucida Grande”‘,
    html: ‘\n\
    CodeIgniter – Extjs Simple Login Screen’
    });

    var tab_center = new Ext.TabPanel({
    xtype: ‘tabpanel’, resizeTabs: false, minTabWidth: 115, tabWidth: 135,
    enableTabScroll: true, layoutOnTabChange: true, border: false,
    activeItem: ‘tab_welcome’, autoDestroy: false,
    items: [
    { xtype: 'panel', id: 'tab_welcome', bodyStyle: 'padding:10px', title: 'Welcome to code igniter' }
    ]
    });

    var tbCenter = new Ext.Toolbar({
    items: ['->', {
    icon: BASE_PATH + 'assets/img/icons/minus-circle.png',
    text: 'Logout',
    handler: do_logout
    }]
    });

    var layout_center = new Ext.Panel({
    id: ‘content-panel’, region: ‘center’, layout: ‘card’, margins: ’0 5 5 0′,
    activeItem: 0, border: true, tbar: tbCenter, items: [tab_center]
    });

    var layout_main = new Ext.Viewport({
    layout: ‘border’, renderTo: Ext.getBody(),
    items: [
    { region: 'north', autoHeight: true, height: 100, border: false,
    html: 'CodeIgniter - ExtjsSimple Login Screen',
    margins: '0 0 5 0', style: 'border-bottom: 4px solid #4c72a4;' },
    { region: 'west', baseCls: 'x-plain', xtype: 'panel', autoHeight: true,
    width: 180, border: false,
    split: true, margins: '0 0 0 5', items: [layout_west1, layout_west2]
    }, layout_center]
    });

    function do_logout() {
    Ext.Ajax.request({
    url: BASE_URL + ‘user/ext_logout’,
    method: ‘POST’,
    success: function(xhr) {
    window.location = BASE_URL + ‘user/login’;
    }
    });
    }

    layout_main.show();

    var BASE_URL = ” + ‘index.php/’;
    var BASE_PATH = ”;
    var BASE_ICONS = BASE_PATH + ‘assets/img/icons/’;
    Ext.onReady(function() {
    var strUsers = new Ext.data.Store({
    reader: new Ext.data.JsonReader({
    fields: [
    'id', 'username', 'password', 'email'
    ],
    root: ‘rows’, totalProperty: ‘results’
    }),
    proxy: new Ext.data.HttpProxy({
    url: BASE_URL + ‘user/ext_get_all’,
    method: ‘POST’
    })
    });

    var tbUsers = new Ext.Toolbar({
    items:[{
    text: 'Add',
    icon: BASE_ICONS + 'user_add.png'
    }, '-', {
    text: 'Delete',
    icon: BASE_ICONS + 'user_delete.png'
    }, '-', {
    text: 'Search',
    icon: BASE_ICONS + 'user_search.png'
    }]
    });

    var cbGrid = new Ext.grid.CheckboxSelectionModel();

    var gridUsers = new Ext.grid.GridPanel({
    frame: true, border: true, stripeRows: true, sm: cbGrid,
    store: strUsers, loadMask: true, title: ‘Users List’,
    style: ‘margin:0 auto; margin-top:73px; margin-left:190px;’, height: 500, width: 800,
    columns: [
    cbGrid, {
    header: "Id",
    dataIndex: 'id',
    width: 110
    },{
    header: "Fullname",
    dataIndex: 'username',
    width: 110
    }, {
    header: "Email",
    dataIndex: 'email',
    width: 110
    }, {
    header: "Password",
    dataIndex: 'password',
    width: 113
    }
    ],
    tbar: tbUsers,
    bbar: new Ext.PagingToolbar({
    pageSize: 10,
    store: strUsers,
    displayInfo: true
    })
    });

    gridUsers.render(‘divgrid’);
    strUsers.load();
    });

    load->view(‘footer’); ?>

  • Ales says:

    Help me plz…
    i try to load grid, but it doesnt work… I miss something… help me

    Human Resources System

    Ext.onReady(function() {

    var dataRecord = new Ext.data.Record.create([
    {name: 'id'}

    ]);
    var dataReader = new Ext.data.JsonReader({
    root: ‘rows’,

    },
    dataRecord
    );

    var dataProxy = new Ext.data.HttpProxy({
    url: ‘MyUrL’,
    method: ‘POST’
    });
    var dataStore = new Ext.data.Store({
    proxy: dataProxy,
    reader: dataReader,

    });
    var colModel = new Ext.grid.ColumnModel([
    {header: "ID", sortable: true, dataIndex: 'id'}

    ]);

    var grid = new Ext.grid.GridPanel({
    autoHeight: true,

    store: dataStore,
    id: ‘grid’,
    width: 740,
    viewConfig: {
    forceFit: true
    },
    cm: colModel
    });
    dataStore.load();

    var myPanel = new Ext.Panel({
    //win: window.nativeWindow,
    width: 800,
    height: 600,
    autoScroll: true,

    items: [grid]

    });
    myPanel.render(document.body);
    });

  • Nole says:

    Hello,

    thank you for the sample but I can not get the pagination to work with mod_rewrite and without the index.php (http://site/controller/method not http://site/index.php/controller/method).

    Any ideas?

  • juniawan arif says:

    help..it’s not working.
    i just copy and paste form your code but it’s not working..it’s said that “No data to display”
    what did i miss?

  • varrick says:

    hi,
    it is an excellent job.
    I follow your guide and finally get the picture you provided,however,there is something different,because the icon such as add” I could not see,but when I click the position there ,It also could response,I am so confused !! Is there any steps to integrate codeigniter and extjs??
    the similar phenomenon also peovide here :
    http://codeigniter.org.cn/forums/forum.php?mod=viewthread&tid=9771&page=1&extra=#pid44769
    the second pic is snapshoot that I integrate codeigniter and extjs ,and the upper pic is snapshoot when I only use the extjs,
    thank you very much !

  • Priyankara says:

    I got some error ,
    Ext is not defined
    /sites/CodeIgniter_2.0.3/()/sites…_2.0.3/ (line 5)
    [Break On This Error] Ext.onReady(function() {

    how i fix it

  • mj says:

    server is fetching data but not displaying in grid..any ideas?

  • Robin says:

    Good job. But, can by my view .html ?

  • Surya says:

    i like it, i try to convert to ext 4.0.

  • Ervin Stutz says:

    I appreciate, cause I found just what I was looking for. You’ve ended my four day long hunt! God Bless you man. Have a great day. Bye

  • Yazid Erman says:

    It was very useful!
    Thank you!

Leave a Comment