mysqlgrid/mysqlgrid

Framework agnostic datagrid - Instantly convert any SQL SELECT statement into a fully featured datagrid

dev-master 2016-04-02 16:35 UTC

Requires

• php: >=5.3.0

MIT

• Tom Berthold

• datagrid
• data grid

README

A powerful and easy to use Ajax data grid for PHP/MySQL

Demo: http://mysqlgrid.com/demo/demo1.php

MySqlGrid lets you quickly create sortable, searchable, and paginated data grids from ANY MySQL SELECT statement including multi-table joins.

• Automatically creates input fields in which a substring can be entered to filter the results.
• Automatically creates dynamically generated drop-down selects.
• Detects date fields and automatically provides pop-up date picker dialogs so users can enter date filters.
• Optionally add controls to edit, view, or delete rows.

MySqlGrid was created with a focus on security, reliability, and simplicity. The learning curve is very minimal. Once you have a valid SQL query, you are minutes away from providing a flexible and beautiful datagrid that will allow your users to quickly find the information they need.

To use mySqlGrid follow these steps:

1. Install via Composer/Packagist. https://packagist.org/packages/mysqlgrid/mysqlgrid
   
2. Specify your mysqli database connection in dbconnect.php
   
3. In your script add "require __DIR__ . '/vendor/autoload.php';" per Composer standards.
   
4. Instantiate class Mysqlgridmain, passing in parameters for your SQL Select statement (and any other optional parameters).
   
5. In the body of your html you will need to create two div elements - one for the datagrid, and one for the pagination area. These two divs must be given ids of "mySqlGridTable" and "mySqlGridPagination", respectively.
   

For a basic example see basicgrid.php.

MySqlGrid works "out of the box" but provides many configuration parameters. Parameters are passed as an array into the Mysqlgridmain object. The parameters are as follows:

Parameter	Type	Description
sql	String	A standard mySQL SELECT statement. Any statement that returns rows from your mySQL database will be transformed into a grid. sql is actually the only parameter that is required to generate a grid.
includePath	String	This is the path to the mysqlgrid "src" directory. This is not needed if you do a standard installation via Composer, in which case mySqlGrid assumes the default Composer installation directory (vendor/mysqlgrid/mysqlgrid/src/). includePath needs to have a "/" as the last character.
gridId	String	This is needed if you want to have more than one grid on your page. If this paramater and its sister parameter "paginationId" are not set, MySqlGrid assumes the divs in your page will be "mySqlGridTable" and "mySqlGridPagination" respectively. For example:basicgrid.php. However, to include multiple grids on one page, each grid needs to have uniquely identified divs for both the grid itself and for the pagination area. In such a case you will need to specify paramaters gridId and paginationId. Here is an example: multigridSample.php
paginationId	String	See gridId above.
lineCount	Integer	The number of rows in each paginated grid. If lineCount is not specified mySqlGrid will display 25 rows per page.
database	String	In installations with multiple databases you can specify the database here. Then, in dbconnect.php you would add something like: if($optionsArray['database']) mysqli_select_db($mySqlGridConnection, $optionsArray['database']);
hideColumns	Array	Specifies columns in the mySQL result set that will not be displayed on the grid. This is handy when you want to get a table's primary key value that means nothing to the user, but will be used to perform an action on a selected row. (See parameters gridControlHtml and gridControlKey)
hideSelects	Array	Specifies columns that will not be given dynamic drop-down select capability. They will still be searchable by substring.
noSelects	Boolean	When set to true this parameter will remove dynamic drop-down select capability from all columns.
noReport	Boolean	Removes the "Report View" button. When the user selects "Report View" button they see a simplified view of the grid table without any controls.
defaultOrderBy	String	You can include a default "ORDER BY" clause in your SQL, but for performance reasons it's better to specify this as a MySqlGrid parameter. Typically this might look something like: 'ORDER BY Last_Name DESC'
noSearch	Boolean	Removes entire search row.
noToolTip	Boolean	Removes the question mark that pops up a tooltip.
noReset	Boolean	Removes the "Reset" button.
noPaginate	Boolean	When set to true this parameter will remove pagination capability. Use with caution: activating noPaginate will cause all rows to be downloaded to the browser at once. If the SQL result set consists of many thousands of rows this might not be wanted.
alwaysPaginate	Boolean	Removes the button: "No Pagination"
gridControlKey	String	Used in conjuction with gridControlHtml. This will be the name of the column that represents the unique identifier of the returned result set. Typically this would be a Primary Key field that was included in the SELECT statement but was hidden from the user using hideColumns.
gridControlHtml	String	Used in conjuction with gridControlKey. This represents the html that creates controls for individual rows in the grid. You can style the controls as buttons for "view" "update", "delete" for example. Or you can use img tags to make clickable icons that perform actions on the selected row. The key thing to understand is that wherever you place the string: "gridControlKey" it will be replaced with the value found in the column designated by gridControlKey. Here are some examples of what this might look like: 'gridControlHtml' => "<a href='myprocesspage.php?editkey=gridControlKey'>Edit</a> <a href='myprocesspage.php?deletekey=gridControlKey'>Delete</a>" 'gridControlHtml' => "<input type='button' onClick='location.href=\"?showUpdateForm=gridControlKey\"' value=\"Edit\">" 'gridControlHtml' => "<img onClick=\"view('gridControlKey');\" src='mysqlgrid/view.png'><img onClick=\"edit('gridControlKey');\" src='mysqlgrid/update.png'><img onClick=\"kill('gridControlKey');\" src='mysqlgrid/delete.png'>"

Other Notes:

MySqlGrid checks to see if jQuery is present, and if not, automatically loads it from a local copy. If you otherwise need to load jQuery in your script it is recommended to add it in the head section prior to instantiating the Mysqlgridmain object.

Acknowledgments

A huge thank you to Czarek Tomczak for ExpandSelect.js and to botmonster for bootpag. You guys are JavaScript geniuses!