In this article, we are going to retrieve field types, defaultValues, and allowBlank settings via the meta data which is generated based on the data columns information of the MySQL database.
For example, we want to display a data grid with paging toolbar which shows a list of values of 'items'.
We now just need to create a simple JsonStore and let the proxy retrieves the information by 'MetaDataExample.php'.
// File Name: index.php
// Start loading the page
Ext.onReady(function(){
Ext.create('Ext.data.JsonStore', {
storeId:'itemsStore',
autoLoad:true,
proxy: {
type: 'ajax',
url: 'MetaDataExample.php', // our data link
reader: {
type: 'json'
}
}
});
Ext.create('Ext.grid.Panel', {
title: 'Items',
store: Ext.data.StoreManager.lookup('itemsStore'),
columns: [
{ text: 'ID', dataIndex: 'id' },
{ text: 'SKU', dataIndex: 'sku', flex: 1 },
{ text: 'Name', dataIndex: 'name', flex: 1 },
{ text: 'QTY', dataIndex: 'qty'}
],
dockedItems: [{
xtype: 'pagingtoolbar',
store: Ext.data.StoreManager.lookup('itemsStore'), // same store GridPanel is using
dock: 'bottom',
displayInfo: true
}],
height: 600,
width: 800,
renderTo: Ext.getBody()
});
});
The following function feeds the meta data to the client and it indicates that we want to show all records from all fields of items table. The 'db' variable is a custom class to handle the database operation and I will explain the functions later. The 'ret' variable contains output data to feed the JSON reader.
// File Name: MetaDataExample.php
public function getItems(){
$this->tableName = "items";
$start = isset($_GET['start']) ? $_GET['start'] : 0;
$limit = isset($_GET['limit']) ? $_GET['limit'] : 10;
$query = "SELECT * FROM `{$this->tableName}` LIMIT {$start},{$limit}";
$stmt = $this->db->query($query);
// get the number of records
$r = $this->db->query("SELECT COUNT(*) FROM `{$this->tableName}`", PDO::FETCH_NUM);
// gets the first column as value
$nbrows = $r->fetchColumn();
// initialize the default output array
$data = array();
// if we have rows
if($nbrows>0){
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
}
// prepare the metadata
$ret['metaData'] = $this->makeMetaData($stmt);
$ret['total'] = $nbrows;
$ret['data'] = $this->Char2Utf8($data);
$ret['success'] = true;
$ret['message'] = true;
echo json_encode($ret);
}
We want to avoid some unwanted characters to appear on the data, we need to convert some characters to UTF8 format characters.
// File Name: MetaDataExample.php
protected function Char2Utf8($var){
if (is_array($var)) {
$out = array();
foreach ($var as $key => $v) {
$out[$key] = $this->Char2Utf8($v);
}
} else {
if(gettype($var)=='string'){
$out = str_replace(chr(194), "", $var);
$out = utf8_encode($out);
}
else
$out = $var;
}
return $out;
}
At the following function, we start to generate the metadata for output. The key part is the value of 'fields', since we want to output the field types and default values based by the database's table structure.
// File Name: MetaDataExample.php
protected function makeMetaData($result, $overrides = false){
/*
metaData: {
idProperty: 'id',
root: 'data',
totalProperty: 'total',
successProperty: 'success',
messageProperty: 'message',
fields: [
{name: 'name', allowBlank: false},
{name: 'job', mapping: 'occupation'}
],
sortInfo: {field: 'name', direction:'ASC'}, // used by store to set its sortInfo
foo: 'bar' // custom property
}*/
$metaData['idProperty'] = 'id';
$metaData['totalProperty'] = 'total';
$metaData['successProperty'] = 'success';
$metaData['root'] = 'data';
$metaData['fields'] = $this->parseMetaDataFields($result, $overrides);
return $metaData;
}
We start to parse the meta data output fields from the data columns of the data table. First of all, we find out the name of the data table from the query result object and gather the columns' information by calling 'getFullColumnsInfo' function which is in the database class. Then we begin to collect the list of database output fields from the query result and match the names to the columns' information we have collected. The columns' information has pretty much enough properties we need in order to generate the settings of field name, field type, default value and allowBlank for meta data fields.
// File Name: MetaDataExample.php
protected function parseMetaDataFields($stmt, $overrides = false){
// gets the table of the $result
$table = $this->tableName;
// gets the table descriptions for future use
$columnsInfo = $this->db->getFullColumnsInfo($table);
// gets the number of fields
$nbFields = $stmt->columnCount();
// starts a empty array
$fields = array();
// loop through the fields
for ($i=0; $i < $nbFields; $i++){
$meta = $stmt->getColumnMeta($i);
$name = $meta['name'];
$fields[$i]['name'] = $name;
if(array_key_exists($name, $columnsInfo)){
$type = $columnsInfo[$name]['Type'];
if($type == 'date')
$fields[$i]['dateFormat'] = 'Y-m-d';
else if($type == 'datetime')
$fields[$i]['dateFormat'] = 'c';
$fields[$i]['type'] = $this->convertType($type);
if($name != 'id'){
$fields[$i]['allowBlank'] = ($columnsInfo[$name]['Null'] == 'YES') ? true : false;
// if we have default value in table column
if(!is_null($columnsInfo[$name]['Default'])){
if($columnsInfo[$name]['Default']=='CURRENT_TIMESTAMP'){
$fields[$i]['defaultValue'] ='new Date()';
}else{
$fields[$i]['defaultValue'] = $columnsInfo[$name]['Default'];
}
}
}
} else {
$fields[$i]['type'] = 'auto';
}
if(!empty($overrides) && is_array($overrides)){
if(array_key_exists($name, $overrides)){
foreach($overrides[$name] as $key=>$value){
$fields[$i][$key]=$value;
}
}
}
}
return $fields;
}
The convertType function plays an important role. We want to convert MySQL's data type to ExtJs data type. This function contains my most used types. The rest of less used types, I leave them to be 'auto'. If you have further types need to be converted, feel free to add them in.
Please note that MySQL does not have 'Boolean' type, but it uses 'tinyint' instead.
// File Name: MetaDataExample.php
public function convertType($type){
if(strpos($type,'(') !== false){
$type = substr($type, 0, strpos($type,'('));
}
switch($type){
case 'varchar':
case 'char':
case 'text':
case 'tinytext':
case 'mediumtext':
case 'longtext':
return 'string';
case 'int':
case 'smallint':
case 'mediumint':
case 'bigint':
return 'int';
case 'tinyint':
return 'boolean';
case 'real':
case 'float':
case 'double':
case 'decimal':
case 'numeric':
return 'float';
case 'date':
case 'datetime':
return 'date';
default:
return 'auto';
}
}
The following simple database class wraps functions we need together. In this example, we use PDO class to connect to our MySQL database. You might want to use mysqli extension for MySQL functions instead. However I don't recommend using mysql extension since mysql extension is deprecated since PHP version 5.5.0.
// File Name: MySqlDataAdapter.php
class MySqlDataAdapter
{
protected $server, $user, $password;
/**
* Db name
*/
public $dbName;
/**
* MySQL pdo object
*/
public $pdo;
/**
* Result of statement
*/
protected $stmt;
/**
* Constructor
*/
public function __construct($server, $user, $password, $dbName){
$this->server = $server; // Host address
$this->user = $user; // User
$this->password = $password; // Password
$this->dbName = $dbName; // Database
$this->connect();
}
/**
* Destructor
*
* @return void
*/
public function __destruct(){
$this->stmt = null;
$this->pdo = null;
}
/**
* Connect to the database
*/
public function connect(){
// create the pdo object
$this->pdo = new PDO('mysql:host='.$this->server.';dbname='.$this->dbName, $this->user, $this->password);
// if connection is failed, then trigger user error
if ($this->pdo === false){
trigger_error('Could not connect to the database.', E_USER_ERROR);
}else{
$this->pdo->exec("set names utf8");
}
// return the connection object
return $this->pdo;
}
/**
* Query the database
*
* @param string $queryStr SQL query string
* @return resource MySQL result set
*/
public function query($queryStr){
try{
//calling beginTransaction() turns off auto commit automatically
$this->pdo->beginTransaction();
// prepare the statement
$this->stmt = $this->pdo->prepare($queryStr);
// execute the statement
$this->stmt->execute();
// commit the statement if everything is alright
$this->pdo->commit();
}
catch(PDOException $ex){
//Something went wrong rollback!
$this->pdo->rollBack();
// trigger an sql error
trigger_error($ex->getMessage(), E_USER_ERROR);
}
// return the statement object
return $this->stmt;
}
/**
* Get column info from table name
*/
function getFullColumnsInfo($table){
$stmt = $this->query("SHOW FULL COLUMNS FROM `{$table}`");
$data = array();
// gets the information
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$data[$row['Field']] = $row;
}
// return the data array
return $data;
}
}
You can also use the same concept to configure the Grid Panel column settings via the meta data. Have a look at 'Dynamic GridPanel for ExtJs 4' and it may give you some ideas to apply column settings via meta data to the Grid Panel.
You can download the source code via here.
Just post a link for source code
ReplyDelete