Hi Everyone,
Here’s some information about the Tables feature that I’ve been talking about some time ago.
DataTable/DataManager (aka Tables)
The concept behind DataTables allows a user to dynamically create data tables for store and retrieving information. DataTables are simply a mixture of new and existing technologies to create simple way to easily manager your day to day application data.
All DataTables consist of fields which are similar in concept to Template Variables. This means that a DataField can be associated with an input type to graphically create a data form. A user can then use this form to quickly enter or modify data inside the table.
DataFields can will most likely support the upcoming Widgets interface which again allows a developer to add some very cool functionality to a simple DataTable. In addition to Widgets a developer can also add Table functions to a table to make it into a completely cool spreadsheet solution.
Unlike Template Variables DataFields will support a new input type or column called DataLookup. This new column will allow a user to automatically populate a column based on values selected from another table. It’s similar in function to DataBound columns.
Examples using the DataManager API:
// Connect to the DataManager using MySQL's interface
$dm = DataManager::connect('MyDataBase','user','password','MySQL');
// create table store categories
$c = $dm->createTable('Categories');
$c->addField('catid','autonumber');
$c->addField('categoryname','Text|50');
$c->save();
// create table to store customers
$c = $dm->createTable('Customers');
$c->addField('custid','autonumber');
// add customername field as a Text Input
// and make it a required field
$c->addField('customername','Text|80',array('required'=>true));
$c->addField('address','TextArea|255');
$c->addLookUpField('catid','Categories','catid','categoryname');
$c->save();
Now that the tables are created lets add some data to it.
$c = $dm->openTable('Customer');
$c->set('customername','Mary Jane');
// set catid based on category name entered
$c->set('catid','Special Customer');
// or use numeric value
// $c->set('catid',10);
// peform data/error validation checks
if(!$c->save()) {
$cms->debug($c->errors());
}
It’s also possible to perform on the fly validation checks
if($c->set('customername',$_POST['name'])){
$cms->client->alert('You must enter a name');
return;
}
DataTables also has the ability to be able to link multiple tables to generate a resultset:
$c = $dm->openTable('Customers'); // nothing queried
$c->include('Orders','order_date','custid'); // nothing queried
echo $c->get('order_date'); // run query
Note that with DataTables nothing is queried until to first try to retrieve data. The include function allows you to perform adhoc queries across multiple tables:
$c->include('Orders','order_date','custid');
$c->include('CustomerDetails','*','custid');
In addition to the include function there’s also an attached function which used the lazy load design to retrieve information.
$c = $dm->openTable('Customers'); // nothing queried
$c->attach('Orders','*','custid'); // nothing queried
// echo 'Testing... '.$c->orders->get('orderid');
while ($c->next()){ // run customers query
echo 'Orders for '.$c->get('customername').'<br />';
echo $c->orders->toGrid(); // run orders query
};
The above will display the customer’s name and then render the customers orders in a grid. There is the toString() and toTable() functions.
On other cool feature of the DataManager is the abilit to use stored queries:
$c = $dm->openTable('Customers');
$c->include('Orders','order_date','custid');
// now store the structure as CustomerOrders
// for later use
$dm->storeTable($c,'CustomerOrders');
// now open it when needed
$c = $dm->openTable('CustomerOrders');
echo $c->toTable();
Now let’s do some filtering with our stored table:
$c->find('customername','Mary*');
// look for orders between 2005/1/1 and 2005/12/31
$c->find('order_date','>|<','2005/1/1|2005/12/31');
echo $c->toTable(array(
'border'=>2,
'fields'=>'custid,customername',
'columns'=>'ID,Customer name'
));
DataTables are not meant to replace core table functionality as it’s likely that they will be slower than native queries (yet to be tested). They will only be made available to allow users to easily create, store and manipulate data. There are two ways to manipulate data inside a DataTable. You can 1) use the graphical interface (aka Tables) or 2) Use the DataManager/DataTable APIs as shown above.
MODX Staff
