How to Populate ExtJs MySql Datastore

In this tutorial I’ll show you how to gather data obtained from a database (MySQL) and convert to JSON and storing it in your datasore for later use by your datagrid. There aren’t many steps involved, first you just need to connect to your mysql database as in this tutorial. Once you have created the connection and gathered the data you wish to display, you just need to add this to your grid.js (or whatever you wish to call the file).

**** UPDATE — Added data.php below:

<?php
include('../../Connections/con.php');

//connection String
$con = mysql_connect($hostname, $username, $password) or die('Could not connect: ' . mysql_error());
//select database
mysql_select_db($database, $con);
//Select The database
$bool = mysql_select_db($database, $con);
if ($bool === False){
	print "can't find $database";
}


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

// Gather all pending requests
$query = "SELECT
PMNumber,
Description,
ControlNumber
FROM yourtablename" ;

$query .= " ORDER BY PMNumber ASC ";
$query .= " LIMIT ".$start.",".$limit;



$result = mysql_query($query, $con); 


// Here we do the count
$query_c = "SELECT
PMNumber,
Description,
ControlNumber
FROM yourtablename" ;

//get total
$count_result = mysql_query($query_c, $con);
$total = mysql_num_rows($count_result);


if (mysql_num_rows($result) > 0){
while($obj = mysql_fetch_object($result))
	{
        $arr[] = $obj;
    }



// Now create the json array to be sent to our datastore

$myData = array('myInventory' => $arr, 'totalCount' => $total);
echo json_encode($myData);
return;	
exit();

}

else { // If no requests found, we return nothing

$myData = array('myInventory' => '', 'totalCount' => '0');
echo json_encode($myData);
return;	
exit();

}

?>
var ds = new Ext.data.GroupingStore({
        proxy: new Ext.data.HttpProxy({
              url:'data.php'
        }),
        reader: new Ext.data.JsonReader({
            id: 'Completion_ID',  
            totalProperty: 'totalCount',
            root: "myInventory",
            fields:
            [{name: 'PMNumber', type: 'int', mapping: 'PMNumber'},
            {name: 'Description', type: 'int', string: 'Description'},
            {name: 'ControlNumber', type: 'string', mapping: 'ControlNumber'},
            {name: 'Completed_Date', type: 'date', dateFormat: 'Y-m-d', mapping: 'Completed_Date'}
            ]
             }),
       sortInfo:{field: 'PMNumber', direction: "ASC"},
           groupField:'PMNumber', 
           remoteSort: true
           
    });

After you’ve created your datastore you just need to create your grid and load the data:

// First we decide what columns we'd like our grid to have
var mycolModel = new Ext.grid.ColumnModel({
  columns: [
            {header: 'PM Number', width:70, dataIndex: 'PMNumber'},
            {header: 'Control Number', width:70, dataIndex: 'ControlNumber'},
            {header: 'Completed Date', width: 70, dataIndex: 'Completed_Date',renderer: Ext.util.Format.dateRenderer(Ext.form.DateField.prototype.format)}
           ]
});


 // trigger the data store load
  ds.load({params:{start:0, limit:1500}});

//Now our grid
var grid = new Ext.grid.EditorGridPanel({
        store: ds ,
        stripeRows: true,
	width: 630,
	height:315,
        autoSizeGrid: false,
        title:'PM Status (Current Quarter)', 
        collapsible: false,
        animCollapse: false
        cm: mycolModel,
        selModel: new Ext.grid.RowSelectionModel(),
        viewConfig: {
        forceFit: true
    }
        
});


//And finally, we render our grid to our index.php file.
grid.render('db-grid'); // db-grid is a div in our index.php file

I hope this step by step tutorial has helped you some. Thank you for stopping by and please share with others, after all, code should be free.foscode.com | becasuse code should be free

20 thoughts on “How to Populate ExtJs MySql Datastore

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

  2. Pingback: Populate ExtJs Datastore with MySql Data | foscode -- Topsy.com

  3. hi
    nice tutorial
    thank you so much
    i want to contruct a tree using php and mysql
    could you plz share tutorial/links wi th me?

    Thanks
    Anusha

  4. @Anusha: Thanks for stopping by, glad the post helped you. I’ll be writing a short app within the next few weeks that will actually include a tree and mysql, so check back in a few weeks and I should have that tutorial up soon after. Thanks again for stopping by and please feel free to let others know about the site. Have a great day.

  5. Can you post the SQL for the sample database? I looked at the previous tutorials and did not see a data file or SQL.

  6. Can you also post a live example? Not sure what other js or css you included or even what ext js. Thanks!

  7. @Chet: Thanks for the input, I’ve added the data.php sample above, hope this helps you and everyone else who visits. Thanks for pointing that out. I don’t have any live samples to post at the moment, but for the time being, you could visit extjs site and check them out, they have a whole load of examples there, then feel free to stop by and use one of my tutorials to help. Extjs samples are usually with static data, so by using my examples you’ll be able to manipulate live data. You can find the Extjs demo site here: http://www.sencha.com/products/js/

  8. Hi,

    I tried the above posted example, but didnot work, grid comes out to be empty.

    Please help..

    Thanks in Advance

  9. @Shilpi: These are just excerpts, they aren’t the entire application, so you’ll need to create the connection and make sure the fields match what you have in your database. I’ll post a complete working example in a few weeks, but for the time being please feel free to check the link I posted above for extjs. They do have many examples on their site that might help you. Please let me know if you still can’t figure out what’s missing and post the code you have and I can help you out with it.

  10. Thanks for your work! Would you mind updating your tutorial to Ext JS 4.0? I think they changed quite a bit to make the search for working tutorials for newbs like me a pain in the ass ;-)

  11. @Matthias, I’ll post some tutorials for 4.0 as soon as I have something put together, however, most of the functionality still the same. ExtJs claims the new versions are backward compatible, although sometimes it is indeed hard to figure out when things go wrong since, well, their help isn’t the greatest, for the exception of a few folks of course. Could you please let me know what you are having problems with and I’ll try to help you out and hopefully get your app running. Thanks for stopping by and hopefully you’ll find my tutorials helpful.

  12. it’s so clearly … the best one extjs with database
    could u show me sample of master detail grid in extjs 4.0
    thanks u

  13. @bhudaya Thank you so much, glad I was able to help. I’ll have something along those lines sometime in the near future.

  14. Can you please post the entire code . I also need the index.php .I mean can you please tutor in the entire code. From connection to the end i;e until localhost/index.php

  15. Your Source Codes has lots of error…

    ” collapsible: false,
    animCollapse: false
    cm: mycolModel,”

    terrible…

  16. @have Thanks for taking the time to post your thoughts, however, if you would have taken the time to read extjs’s api, you’d notice what you called “Your Source Codes has lots of error…” are indeed configurable options that you may choose to use or not. From the api:

    collapsible : Boolean
    True to make the panel collapsible and have the expand/collapse toggle button automatically rendered into the header tool button area, false to keep the panel statically sized with no button (defaults to false).

    animCollapse : Boolean
    true to animate the transition when the panel is collapsed, false to skip the animation (defaults to true if the Ext.Fx class is available, otherwise false).

    cm is the columnmodel that was previously defined on line 2.

    Again, thank you for stopping by, but if you don’t have something constructive to post, I’d suggest you use your time reading the api instead. The api can be found here http://docs.sencha.com/ext-js/3-4/

  17. Hi Dan,

    I am working on ExtJS. Can you tell me how to send request to PHP from script?

    I will set dataIndex in the script and I need to tell that value to PHP, for example, if I am setting dataIndex of a grid to “id”, I have to dynamically set that “id” in PHP like,

    SELECT id FROM table_name

    I am working with checkboxes, like I mentioned in your twitter, selecting “id” here means I am checking “id” checkbox.

    Can you help me with an example?

    Thanks.

  18. Hi Dan,

    Thanks for the tutorial. I try to replicate your code with modification of my own ( strictly from the database side only, connection , db name, tables, etc). I got to the point of storing MySQL data to JASON array, it works great. however, when I try to display it in the GRID, I got “GroupingStore error id not a constructor” error. Can you help me with this?
    Thanks.

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>