ExtJS Grid Search

ExtJS as stated on their site is a cross-browser JavaScript library for building internet applications. Most of the examples on their site deals with static data, and if you ask their experts on how to accomplish the same with mysql or any other database, they’ll probably tell you it is the same and just look it up in their api and figure it out.

Well, that’s just not good enough, so after countless hours of google searches trying to find the solution (which most of the time only involves a few lines of code), I’ve come of up with some simple steps that might help get your extjs grid search going so and I’ll share these with you.

Any extjs application you develop will usually have 3-5 files (grid.js, index.php, connection.php, data.php and submit.php). You can name them whatever you’d like, I just named them this way just to distinguish each one so you could understand what I’ll be doing a bit easier.

I’m not going to get into much detail about the connection.php and submit.php files on this tutorial, but I’ll write a separate article at a latter date (the tutorial is now available)
you can always ask on this post though and I’ll be happy to help you create them if you’re not sure how.

Ok, so first things first, create the connection.php file with your database connection and save it in a safe place, preferably not in your world readable directory you serve your pages from.
Now you’ll create the grid.js file, which will have all the grid detail along with any other fields, forms, fieldsets and so on; I’ll just focus on the section for the extjs grid, which includes the lines that bring in the data from our data.php file and grid code.
First the datastore:

var mydatastore = new Ext.data.Store({
        reader: new Ext.data.JsonReader({
          fields : [
            {name: 'field1', type: 'int', mapping: 'mysql_field1'},
            {name: 'field2', type: 'string', mapping: 'mysql_field2'},
            {name: 'field3', type: 'string', mapping: 'mysql_field3'},
            {name: 'field4', type: 'string', mapping: 'mysql_field4'},
            {name: 'field5', type: 'string', mapping: 'mysql_field5'}
                    ],
          root: 'myInventory'
        }),
  proxy: new Ext.data.HttpProxy({
            url: './data.php'
        }),
        sortInfo:{field: 'field5', direction: "ASC"},
        remoteSort: true

    });

Ok, we continue in the same grid.js file, this time we’ll actually be creating the grid:

var mygrid = new Ext.grid.GridPanel  ({
    store: mydatastore,
    columns: [
            {header: 'Field 1', width:45, dataindex: 'mysql_field1'},
            {header: 'Field 2', width:55, dataindex: 'mysql_field2'},
            {header: 'Field 3', width:70, dataindex: 'mysql_field3'},
            {header: 'Field 4',  width:90, dataindex: 'mysql_field4'},
            {header: 'Field 5',  width:40, dataindex: 'mysql_field5'}
    ],
     stripeRows: true,
     autoSizeColumns: true,
     autoSizeGrid: true,
     title:'Your Grid Title',
     collapsible: false,
     animCollapse: false,
     height: 445,
     columnLines: true,
     viewConfig: {
        forceFit: true
    }
    
});

Now to make it a little more organized, I’ve created a fieldset where the grid will be displayed and also the formpanel (you can go without these if you want, they are not necessary to make this all work). First the fieldset panel:

var myinfo = {
    layout: 'column',
    xtype:'fieldset',
    checkboxToggle:false,
    title: 'Fieldset Title',
    autoHeight:true,
    collapsed: false,
        items: [{
            layout: 'form',
            width: 450,
            items: [mygrid]
}]
};

Now we’re getting to the good part, add the panel, which we’ll include our grid and also our search box (which is probably the reason why you’re here the first place), and will get our extjs grid search all put together. You can also add any other forms/grids/fields you may have (in this example I’m going to add just the grid and the search box):

var mypanel = new Ext.form.FormPanel({
         frame:true,
         bodyStyle:'padding: 5px 5px 0',
         xtype: 'form',
         id:'asset-form',
         layout: 'hbox',
         frame: true,
         collapsed: false,
         autoHeight:true,
         width: 1200,
           items: [ myinfo ], 
               tbar: [new Ext.Toolbar.TextItem ("Search For:"),
                  {xtype:'textfield',
                    fieldLabel:'Search',
                    name: 'pattern',
                    id:'pattern',
                    listeners: {  
                 //listen for the ENTER key, same as if the user clicks the search button
                   'render': function(c) {
                      c.getEl().on('keypress', function(e) {
                      if(e.getKey() == e.ENTER && this.getValue().length > 0) {
                      mydatastore.baseParams = {
                      pattern: document.getElementById('pattern').value
                                               };
                         mydatastore.load({
                             params:{
                                start: 0,
                               limit: 150
                                    }
                                                });
                                            }
                                              },
                       Ext.QuickTips.register({
                            target: c.getEl(),
                            text: 'If searching for date,<br/>\n\
                           \n\ format must be mm/dd/yy'
                                       }),
                                        c);
                                  }

                           }
                               },
                         
                      {xtype:'tbseparator'},new Ext.Toolbar.Button({
                         text: 'Search',
                         iconCls:'search-icon',
                      	handler: function search_submit() {
                                mydatastore.baseParams = {
                                     pattern: document.getElementById('pattern').value
                                  };
                                mydatastore.load({
                                  params:  {
                                    start: 0,
                                    limit: 150
                                  }
                                });
                              }
                          }),
                      {xtype:'tbseparator'},
                      new Ext.Toolbar.Button({
                         text: 'Reset Search',
                         iconCls:'reload-icon',
                      	handler: function() {
                              var s = Ext.getCmp('pattern');
                              s.setValue('');
                              mydatastore.baseParams = {
                                     pattern: document.getElementById('pattern').value
                                  };
                                mydatastore.load({
                                  params:  {
                                    start: 0,
                                    limit: 150
                                  }
                                });
                                 }
                      })
                      
                      ]
                    
            });

Now we render our panel above to a div in our index.php file:

// render grid
mypanel.render('mydiv_id');

Still with me? :-) Great, we’re almost there, now all we have left to do is edit our data.php file (the one that brings the data from mysql and loads our grid).

*** Revised php code below on July 30th 2011

$start = ($_REQUEST["start"] == null)? 0 : $_REQUEST["start"];
$limit = ($_REQUEST["limit"] == null)? 150 : $_REQUEST["limit"];	

$pattern = $_REQUEST["pattern"];

if ($pattern == null || $pattern == '') { 

$search = '0=0'; 

} else {

$search = "(mysqlfield_1 LIKE '%".$pattern."%' OR mysqlfield_2 LIKE '%".$pattern."%')";

}

//////////////   BEGIN COUNT   //////////

$queryc = "SELECT count(*) FROM mytable WHERE $search ";

$resultc = mysql_query($queryc, $weberp); 

$total = mysql_result($resultc, 0);

///  END COUNT //////


$query = "SELECT  * FROM mytable WHERE $search";

$query .= " LIMIT ".$start.",".$limit;
			
$result = mysql_query($query, $weberp); 
// Test the query
if(!$result){
    die("SQL Query ERROR! " . mysql_error($weberp)); 
}

if (mysql_num_rows($result) > 0){
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$myInventory[] = array(
        'mysql_field1' => $row['mysql_field1'],
        'mysql_field2' => $row['mysql_field2'],
        'mysql_field3' => $row['mysql_field3'],
        'mysql_field4' => $row['mysql_field4'],
	'mysql_field5' => $row['mysql_field5']
	);
}

$myData = array('myInventory' => $myInventory, 'totalCount' => $total);

echo json_encode($myData);
return;	
exit();

    }

Well, that’s it, hopefully this will take you in the right direction with your mysql dynamic datagrid . Please remember, these are just snippets, so you’ll need to adjust accordingly based on your setup, just copying and pasting these lines into a file all by themselves won’t work. You can also visit this site which helped me get as far as I did with the search grid and should help you get in the right direction as well, or just ask questions here and I’ll answer them. Thanks for stopping by and please share with others, after all, code should be free.

12 thoughts on “ExtJS Grid Search

  1. Pingback: Tweets that mention ExtJS Grid Search | foscode -- Topsy.com

  2. Pingback: Tweets that mention ExtJS Grid Search | foscode -- Topsy.com

  3. Pingback: Connect extjs with mysql using php | foscode

  4. Pingback: Submit ExtJS Incoming Put Data into MySQL With PHP | foscode

  5. Pingback: Toysblog » Blog Archive » Extjs

  6. i m developing one application having two region west having tree with node and center i want display grid when i click on tree node respectively…..i hv problem to not load grid in central area when click on particular node…help

  7. Pingback: Open iReport jrxml from ExtJs as pdf | foscode

  8. Hi..We are looking some help in making our grid search work for paging proxy where we are not calling server side code to fetch the records every time.Paging works fine but in search we are not getting results from current page..Please help if we can search multiple pages without calling server proxy.

  9. @Kuldeep, @Yura I’ve updated the code above and paging should be working properly now.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>