florDatagrid: A simple php datagrid class
Posted by: Floresense Team
Overview:
Attached is a freeware php class (florDataGrid) that can be used to create quick tabular reports from MySQL data.
Specification:
> For PHP5.0 or above versions only.
> Depends on flor_db_process class version 1.0.1 (details below).
Features:
1. Easy to generate reports
2. Columns automatically determined and appropriate number of html table columns are created.
3. Can tweak color, padding, and other basic settings of the grid.
4. Allow setting a dataKey property for recording the key or id of each row in the grid. this is useful when a delete button on a cell is clicked.
5. Support an onRowItem_bound event for manipulating rows before they are generated.
6. Easy to add buttons in a specific column in the grid.
7. Submit form in which the grid is in, when a cell button is clicked, posting back information on which cell was clicked alongwith a datakey.
8. Easy to custom-size columns and the grid itself.
9. Generates html columns automatically, also supports implicitly declaring columns.
10. Easy to customize columns, header display names,
11. If there's no data or rows in a mysql query used for the grid, then the grid displays all the headers and prints the 'No Data' message below the header.
12. Pagination of data integrated. Both number based or alphabet index based paging.
Dependency:
This class depends on flor_db_process class version 1.0.1 class. Download/find it here.
Users of florDataGrid class are expected to have used flor_db_process class.
It is important that flor_db_process.class.php is included in your scripts that use the datagrid, because the datagrid class internally uses flor_db_process methods to process the data, do pagination, and related functions.
Note, that datagrid class doesn't use the flor_db_process class to query the database itself. Only you do the querying part and apply the result to the datagrid. You can use flor_db_process's methods to query the database since that is what the class was written for initially, but you can opt to using your own methods to query the database, and use flor_db_process for the sake of datagrid's dependency.
STEPS to use this class:
1. Download, extract and copy the two files in the package.
2. This class depends on flor_db_process.class.php, so you will need to have that class also in the same folder.
3. Include the classes flor_db_process (DBinit.php and flor_db_process.class.php) and Datagrid(datagrid.class.php) in all your php pages/scripts where you want to show a datagrid.
4. You can then code you grid in a few lines, as shown in below examples.
Usage:
First, the data:
For creating the grid, we need data.. so first write code that will execute a 'select' query and get a resultSet. You can use flor_db_process class for this, or use your own class or methods to get a 'resource' object similar to one that is output with mysql_query() method. The datagrid can understand only this object.
Preparing data for the grid using flor_db_process:
$dbp = new flor_db_process();
$query = "select * from geekr_cmsSections";
$dbp->fetchQuery($query);
$gridData = $dbp->resultSet;
$gridData will contain a 'resource' object. You can do a $gridData->num_rows to see the number of rows returned.
A simple grid:
Example to show the data in a default grid:
$dg = new DataGrid($gridData);
$dg->fontSize = .8;
$dg->autoColumns = true;
$dg->showGrid();
Since we set autocolumns to true, everything is taken care of, and the grid would be generated with the showGrid() method.
The header text is same as the column name returned by the MySQL query.
Example, to customize grid column positions, and header column names:
With a bigger table example like below, we get an unstructured grid.
We will structure the grid.
First, set autoColumns to false, so that we can customize the columns.
$dg->autoColumns = false;
Then, set the first first column
$dg->colSet[0] = new Column();
$dg->colSet[0]->displayName = "Headline";
$dg->colSet[0]->fieldName = "section_codeName";
$dg->colSet[0]->width = "70%";
The datagrid class instance supports a colSet array which has to be of type Column. Column is an internal declared class implemented in datagrid.class.php
The Column class has properties like displayName, fieldName, and width. We are instancing the class and setting it to the colSet at position 0 which will be interpreted as the first column in our grid.
The displayName property value defines the header text for the column.
The fieldName property value should be equivalent to a column name in the sql query of the grid data. In above example "section_codeName" is a field name of our MySQL table which we are querying.
The width property is set to 70% of the total width of the grid. The total width of the grid can be set using $dg->width = "500" for 500 pixels.. the default is 100% width for the grid.
Similarly, we define other columns.
$dg->colSet[1] = new Column();
$dg->colSet[1]->displayName = "Link";
$dg->colSet[1]->fieldName = "headLink";
$dg->colSet[1]->width = "20%";
$dg->colSet[2] = new Column();
$dg->colSet[2]->displayName = "Status";
$dg->colSet[2]->fieldName = "contentStatus";
$dg->colSet[2]->width = "10%";
$dg->colSet[3] = new Column();
$dg->colSet[3]->displayName = "Last Updated";
$dg->colSet[3]->fieldName = "LU";
$dg->colSet[3]->width = "20%";
Now, executing the script gives us a grid like below. Compare this with the grid image above.
Now we will format the date in the Last Updated column.
For this, we need to implement a event handler, to handle data before each row is generated. So we will change the format of last update date just before the row's html is generated.
We implement a method dg_row_bound() as below.
function dg_row_bound($rowItem=null)
{
return $rowItem;
}
And, we will tell about the existence of this method to the datagrid instance.
$dg->onRowItem_Bound = "dg_row_bound";
The above line sets dg_row_bound as the event handler for the event onRowItem_Bound.
You might know that PHP doesn't have events, so here, when we say events it may be confusing. We are actually not working on php events, but the Datagrid class is implemented such that after each row data is read and before each row is generated the property onRowItem_Bound is checked and if a value is present, the value is used like a method call. This process is a PHP 5.0 feature 'Dynamic functions', and hence this datagrid class is NOT compatible with php versions before 5.0
In the event handler, care is to be taken that the handler accepts an attribute and returns the attribute.. with or without modifications. If this function signature is not followed, the Datagrid class will throw errors.
You can test the code until now, and there won't be any change in the output, but if you add an echo "hello"; inside the dg_row_bound method, you will see it printed out each time a row is generated.
To format our Last updated column value, we will add code into the handler as below.
function dg_row_bound($rowItem=null)
{
$tmpCol1 = $rowItem[3];
$tmpCol1->HTMLContent = date("j M y H:i", strtotime($tmpCol1->HTMLContent));
return $rowItem;
}
The DataGrid class passes an array of 'cell' class instances which form a row, whenever it calls the row bound event handler.
The 'cell' class is an internal class implemented in datagrid.class.php itself.
The class has a property HTMLContent which will contain the value that is to be printed in a cell for the row. Try setting all HTMLContent property values to some value like "test" in your row bound handler, and you will see "test" appearing in the cells instead of the actual data. Also try appending content like $tmpCol1->HTMLContent .= "test";, and you will see test added to the actual contents of the cell.
The grid becomes:
Notice the format of the date in the Last Updated column.
Look-n-feel tweaks:
borderColor property:
$dg->borderColor = "gray"; //or values like #C0C0C0
//takes all values a typical css color attribute will take
//Default value is "black"
borderStyle:
$dg->borderStyle = "dotted"; //or solid, or dashed, etc.,
//takes all values like a typical css border-style attribute.
//default is "solid"
borderWidth:
$dg->borderWidth = 1; //integer
//takes integer values. sets the border width attribute of the grid's html table
//Default is 1
cellPadding:
$dg->cellPadding = 2; //integer
//takes integer values. sets the cellpadding attribute of the grid's html table
//Default is 2
headBackgroundColor:
$dg->headBackgroundColor = "#FFFFCC"; //or values like green
//takes color values like css background-color attribute.
//default is a blue shade #5B59B7
headForeColor:
$dg->headForeColor = "green"; //or values like #FFFFFF
//takes color values like css color attribute.
//default is "#FFFFFF";
fontSize:
$dg->fontSize = 1; //integer
//takes decimal values with a precision of 1. like .2, .8, 1, 1.2 etc.,
//sets the font size for all grid content.
//Default is 1.0
fontFamily:
$dg->fontFamily = "courier new"; //or Arial, verdana, etc.,
//takes font name values like a font-family css attribute.
//sets the font for the entire grid.
//Default is "Verdana"
See how the below set of changes affect the grid.
$dg->borderColor = "gray"; //or values like #C0C0C0
$dg->borderStyle = "dotted"; //or solid, or dashed, etc.,
$dg->borderWidth = 1; //integer
$dg->cellPadding = 2; //integer
$dg->headBackgroundColor = "#FFFFCC"; //or values like green
$dg->headForeColor = "green"; //or values like #FFFFFF
$dg->fontSize = .8; //integer
$dg->fontFamily = "courier new"; //or Arial, verdana, etc.,
$dg->showGrid();
NOTE: All properties should be set before calling showGrid() method.
showGrid() method should be the last method called for applying all properties and settings of a grid.
Adding buttons to the grid:
Lets take the case of the old shorter grid.. and try adding a delete button to allow users to delete rows from the grid.
For this, we add a new dummy column as below.
$dg = new DataGrid($gridData);
$dg->width = "300";
$dg->fontSize = .8;
$dg->autoColumns = false;
$dg->onRowItem_Bound = "dg_row_bound";
$dg->dataKey = "section_id";
$dg->colSet[0] = new Column();
$dg->colSet[0]->displayName = "Existing Sections";
$dg->colSet[0]->fieldName = "section_codeName";
$dg->colSet[0]->width = "70%";
$dg->colSet[1] = new Column();
$dg->colSet[1]->displayName = "";
$dg->showGrid();
Also, modify the grid row bound event handler,
function dg_row_bound($rowItem=null)
{
return $rowItem;
}
The grid becomes
Notice the dataKey property setting in the above code block.
The DataGrid class has a 'dataKey' property which you can set to the column name of the key or id field of the resultSet (which will be an unique dbvalue for each row).
For example, in our mysql statement resultSet, we will have a section_id field which is the primary key of the table queried. We will need this primary key value to update/delete that particular row in the database. So, we first set the dataKey property of the grid as $dg->dataKey = "section_id";
Next, we add the buttons.. Adding the buttons to each row is easy, but we need to add it such that when a row's delete button is clicked we can identify which row's button was clicked.
The DataGrid class passes an array of cell instances for each row which we used earlier to format the date for display in 'Last Update'. We have used the cell class's HTMLContent property earlier. Now we will use the other properties of the cell class.
The cell class supports the following properties.
rowId: Has the row id of the cell.
colId: Has the column id of the cell.
dataKey: Contains the dataKey field value for the row that the cell belongs to.
HTMLContent: Contains the content/value of the cell.
The rowId, and colId properties are integer values representing the cell's position in the grid. The first row's id is 0, and first column's id is 0...and not 1,1.
The dataKey property value depends on the datagrid's dataKey property.
While the grid's dataKey property mentions the field to use as the key, the cell's dataKey property gives the value or the key for that row.
We will use the rowId, colId and dataKey properties to name the delete buttons in each row, so we have all information about which row's button was exactly clicked.
In the row bound event handler,
$tmpCol = $rowItem[1];
$cellId = $tmpCol->rowId."_".$tmpCol->colId."#".$tmpCol->dataKey;
We prepare a cellId variable like above and add it to the button name like this.
<input type=submit name='btnSecDel_$cellId' value='Delete' />
This above line will now be set as the cell's content.
$tmpCol->HTMLContent = "<input type=submit name='btnSecDel_$cellId' value='Delete' />";
So, the event handler becomes,
function dg_row_bound($rowItem=null)
{
$tmpCol = $rowItem[1];
$cellId = $tmpCol->rowId."_".$tmpCol->colId."#".$tmpCol->dataKey;
$tmpCol->HTMLContent = "<input type=submit name='btnSecDel_$cellId' value='Delete' />";
return $rowItem;
}
Executing the script show the grid with the buttons.
Notice that we added buttons of type 'submit', and adding/making sure that our grid's showGrid() method is called within a <form> </form> tag makes the grid active, and clicking any of the delete buttons submit the form.
Now, we have completed all that is possible with the grid. Since we have the button name giving us the section_id(or dataKey) of the row whose delete button was clicked, we only have to check the $_POST array when the form is submitted and appropriately delete the row using the dataKey.
An example form submit handling code is as below:
if ($_POST) //if form was submitted
{
//check whether any delete button was clicked.
//if clicked, the button's name and value is submitted back, unclicked
//button names and values are not submitted.
foreach($_POST as $k=>$v)
{
//delete section
if (strpos($k,"btnSecDel")!==false && $v!="")
{
$dataKey = substr($k,strpos($k,"#")+1);
if (!section_del($dataKey))
{
$errMessg = $data->err;
}
break;
}
}
}
In the above code, section_del() is a method which i can implement the actual delete code.
Pagination:
Two modes of pagination is enabled in this datagrid. Alphabet index based, and numeric pagination. When you enable pagination, the page index gets printed automatically on top and bottom of the grid.. You can choose to disable the top or bottom page-index display as you choose.
Since pagination will print links for pages, you should make sure you print the datagrid within a <form></form> element. And the form tag can only be of the below format
... other datagrid initiation code <form name="frmAction" action="" method="POST">
<? $dg->showGrid();
?>
</form>
The form tag should have the same name 'frmAction', since this is hard-coded in the datagrid class. If you wish to change this, you should find-replace text 'frmAction' in datagrid.class.php with your form name.
Example : To enable alphabet index pagination at the top of the datagrid, you use below code, before you call $dg->showGrid();
$dg->pageByName = true;
$dg->pageByName_field = "dispName";
// the field in database by which we have to alphabetically page.
$dg->paging_indexAtBottom = false;
//disables showing page index at the bottom
To enable numeric index paging,
$dg->paging = true;
$dg->paging_byPageNos = true;
$dg->paging_NoOfRows = 30;
This grid class and the flor_db_process class helps us a lot in our projects.
Hope you find it useful too.
Advertisement
|
About / Contact
Home
Support Desk
