Hi,
I have recently managed to integrate a dhtmxGrid inside my element_form.php page. So far I just managed to load the data from the database in an Excel-like manner, but I would like to go on and be able to use the grid to update and manage the related db table. Since I am not so well trained in MVC structure, I am sure there is a much more efficient way to achieve my scope, hence: PLEASE, ANY SUGGESTION to improve the following is really appreciated
A) THE SCOPE - Bibliography example
The scope of the grid inside element_form.php is handling a many to many relation inside the backend without abandoning the element view.
Let's say we are using element_form.php to describe an element and that we want to add references to a bibliography for this element. We need three tables:
Tab1: mycom_element -> contains id plus other info
Tab2: mycom_bib -> contains id plus title,author_name, ecc... for each book we will refer to
Tab3: mycom_bibrel -> contains id, id_el, id_bib plus eventually other info (page number, type of bibliographic relation, ecc...)
What I want to have in my backend is: a bib view, where I can insert my books description, and an element view, where, among the other fields, I have a grid, where I can call (using a drop-down inside a cell) the book title, insert the additional info, and, pressing a "save button" inside the grid, update the mycom_bibrel table (that remains, so to say, hidden from the main component view).
B ) THE INSTRUMENT
I have used
dhtmlxGrid
This grid comes with a very nice server-side connector...anyhow, I couldn't manage to have it working
Hence, if someone can tell me how to bring connector.php up to work inside a Joomla! component, I will be REALLY glad to simplify A LOT the procedure I followed.
First of all one has to download the package from the site, and move the dhtmlx folder inside the admin folder -> update accordingly the root mycom.xml file.
C) PART 1 (actually the unique one so far
) -> loading data inside the grid.
I will assume that the database tables contains the following data (NOTE -> since one can not use the dhtmlxGrid to write yet, at this stage the relations inside mycom_bibrelations have to be inserted manually, using PHPMyAdmin or similar):
mycom_element [id_el, name]
1 Element 1
2 Element 2
mycom_bib [id, title, author_name, author_surname]
1 Alice in Wonderland Lewis Carroll
2 Siddharta Herman Hesse
3 Henry V William Shakespeare
mycom_bibrelations [id, id_el, id_bib]
1 1 1
2 1 2
3 2 2
4 2 3
C1) Querying the database
This part of course would not be necessary if the dhtmlxConnector were working. But since it's not...
Inside element_form.php (let's say after </fieldset>, not very nice but ok for a quick test), insert the database query
<?php
$idel=$this->item->id;
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->select(array('title', 'auth_surname', 'auth_name'))
->from('#__mycom_bibrelations as a')
->join('INNER', '#__mycom_bib as b ON (a.id_bib=b.id)')
->where ('a.id_el='.$idel);
$db->setQuery($query);
$res = $db->loadAssocList();
?>
Luckily the dhtmlxGrid accepts as input many formats, in this case JSON is the more appropriate, I think, but it requires a specific structure ->
see here for more details.
The result of json_encode() does not match the correct structure required by dhtmlx and one has either to manipulate the $res array, or manipulate the result of json_encode($res). I chose the first option.
C2) Creating the function that converts the query result into JSON for dhtmlx
Inside the helpers folder, create a myhelper.php file that contains the following code:
<?php defined('_JEXEC') or die();
/**
* Code necessary to generate a well formatted JSON for dhtmlx input
*
*
* @access public
* @param array $results The array coming from a db query
*
* @return string $datasting The correct JSON string
*/
abstract class dhtmlxInput {
function queryToJson ($results=array())
{
//Convert StdObject to Array -> code from http://www.if-not-true-then-false.com
function objectToArray($d) {
if (is_object($d)) {
// Gets the properties of the given object
// with get_object_vars function
$d = get_object_vars($d);
}
if (is_array($d)) {
/*
* Return array converted to object
* Using __FUNCTION__ (Magic constant)
* for recursive call
*/
return array_map(__FUNCTION__, $d);
}
else {
// Return array
return $d;
}
}
$newresult=objectToArray($results);
/* Arrays coming from Joomla! db loadAsoocList() are 2D associative.
* The JSON needed by dhtmlx is not compliant
* with the result coming from json_encode(), hence we need to parse the array and build the JSON string.
* For that purpose, an internal index $key is used, with Column/Row numbers as limits.
*/
$nrow = count($newresult,0);
$ncol = (count($newresult,1)/count($newresult,0))-1;
$datastring="{rows:[";
foreach ($newresult as $rowkey => $rowval)
{
$datastring=$datastring . "{ id:" . $rowkey . ", data:[";
$key=0;
foreach ($rowval as $colkey => $colval)
{
if ($key < $ncol-1)
$datastring=$datastring. "\"" . $colval. "\",";
elseif (($key = $ncol-1) and ($rowkey != $nrow-1))
$datastring=$datastring. "\"" . $colval. "\"] },";
elseif (($key=$ncol-1) and ($rowkey = $nrow-1))
$datastring=$datastring. "\"" . $colval. "\"] }] }";
$key++;
}
}
return $datastring;
}
}
At the beginning of element_form.php (e.g. after element.form is called) insert the following:
<?php JLoader::register('dhtmlxInput', JPATH_COMPONENT_ADMINISTRATOR.'/helpers/myhelper.php'); ?>
Then append this line to the former query code:
<?php $datagrid=dhtmlxInput::queryToJson($res); ?>
C3) Getting dhtmlx to work
Inside helper.php, function headerDeclarations() insert, after the jQuery calls, the following:
$doc->addScript($componentUrlAdmin . '/dhtmlxGrid/codebase/dhtmlxcommon.js');
$doc->addScript($componentUrlAdmin . '/dhtmlxGrid/codebase/dhtmlxgrid.js');
$doc->addScript($componentUrlAdmin . '/dhtmlxGrid/codebase/dhtmlxgridcell.js');
Also add the dhtmlx css files in the "css part" of the function (some lines below):
$doc->addStyleSheet($componentUrlAdmin . '/dhtmlxGrid/codebase/dhtmlxgrid.css');
$doc->addStyleSheet($componentUrlAdmin . '/dhtmlxGrid/codebase/skins/dhtmlxgrid_dhx_skyblue.css');
Now, inside element_form, one can call the dhtmlx grid. This is the basic view, but there are several option like sortable columns, pagination, etc... that one could implement by simply loading some additional .js files (not tried yet).
So, inside element_form.php append the following code to the previous one:
<?php
//echo $data inside dhtmlxGrid;
echo <<<EOD
<div id="gridbox" style="width:630px; height:370px;"></div>
<script>
mygrid = new dhtmlXGridObject("gridbox");
mygrid.setImagePath('components/com_manta/dhtmlxGrid/codebase/imgs/');
mygrid.setHeader("Title, Author Surname, Author Name");
mygrid.setInitWidths("*,*,*");
mygrid.setColTypes("ed,ed,ed");
mygrid.setSkin("dhx_skyblue");
mygrid.init();
mygrid.parse($datagrid, "json");
</script>
EOD;
?>
Now when opening an element view, at the end of the field list, a grid should appear, with three columns, listing the bibliography related to the element, according to what inserted in the "hidden" table mycom_bibrelations.
That's all by now
