15.4 C
Nairobi
Friday, September 30, 2022

Adding data to ExtJS grid from PostgreSQL

Loading data from a PostgreSQL database into an application is useful more so if there will be frequent changes on the data stored in the databases. The data is loaded from the database when the application is loading. Any changes on the data are reflected into the grid carrying our data in the application.

I will demonstrate using PHP and ExtJS on how to query a database and return the data into a JSON format that will be used in our JavaScript application.
First, we write a script that will query our database in PHP (data.php)
 
 //data.php
$db = pg_connect(“host=somehost port=someport dbname=**** user=**** password=****”) or die(‘could not connect:’.pg_last_error());
$result = pg_query($db, “SELECT *,ST_AsGeoJSON(geom) FROM Africa”) or die(“Data load failed:”.pg_last_error());
$rows = array(
         ‘type’      => ‘FeatureCollection’,
        ‘features’  => array()
  );
while($r = pg_fetch_assoc($result))
                {
                                $rows[] = $r;
                }
 echo json_encode($rows);
?>
This will return our result as a JSON file.We then define a path in our JavaScript file to point to the query results
var mydata = “data.php”;

Secondly, we create a JSON store to read our query result above.This will be done using the ExtJS library Ext.data.JsonStore()

var store = new Ext.data.JsonStore({
                         proxy: {
                                          type: ‘ajax’,
                                          url:mydata,
                                          reader: {
                                                       type: ‘json’,
                                           }
                                  },
                         fields: [
                                     {name: ‘gid’, type: ‘int’},
                                       ‘cntry_name’, ‘sovereign’, ‘pop_cntry’,’curr_type’,’landlocked’
                                 ]
            });
 
We then load our store with data from mydata
 
store.load();
 
Lastly, we define our grid that will display the data
var grid = new Ext.grid.GridPanel({
                        store: store,
                        columns: [
                               {header: ‘gid’, width: 50, sortable: true, dataIndex: ‘gid’},
                               {header: ‘Country Name’, width: 100, sortable: true, dataIndex: ‘cntry_name’},
                               {header: ‘Sovereign’, width: 100, sortable: true, dataIndex: ‘sovereign’},
                               {header: ‘Population’, width: 250, sortable: true, dataIndex: ‘pop_cntry’},
                               {header: ‘Currency’, width: 250, sortable: true, dataIndex: ‘curr_type’},
                               {header: ‘Landlocked’, width: 250, sortable: true, dataIndex: ‘landlocked’}
                          ],
                         stripeRows: true,
                         columnLines:true,
                         title:’DB Grid’
    });
 
The results resembles this;
 
 
 
 

That was a short demonstration on how data can be loaded from a PostgreSQL database with PHP into our application and be displayed in a grid.

There are various ways to achieve this, and this is just one of them. Hope this helps.
close

Join Our Mailing List

Wanjohi Kibui
Wanjohi Kibuihttps://www.lifeingis.com
A GIS user with interests in web and desktop systems development, FOSS advocate, trainer and mentor. Lives in Nairobi but finds adventure in travelling. The "Life in GIS" phrase is as a result of the many told and untold stories in the Geospatial realm.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stay Connected

933FansLike
132FollowersFollow
3,730SubscribersSubscribe
- Advertisement -

Join Our Mailing List

Latest Articles