RGrid for PHP: Datagrid class for PHP & MySQL

Introduction

This is a PHP datagrid class which can show the MySQL result set which you pass to it as a paged list. It can be completely styled using CSS (which looking at the examples, I'm sure you'll be grateful for). A quick example:

/**
    * Include the RGrid code
    */
    require_once('RGrid.php');
    
    $params['hostname'] = 'localhost';
    $params['username'] = 'root';
    $params['password'] = '[PASSWORD]';
    $params['database'] = 'phpguru';

    $sql = "SELECT cm_id,
                   ne_title,
                   cm_author,
                   cm_datetime,
                   cm_status,
                   cm_neid
              FROM comments,
                   news
             WHERE cm_status = 'ACTIVE'
               AND cm_neid = ne_id
          ORDER BY cm_id DESC";

    /**
    * Create the datagrid with the connection parameters and SQL query
    * defined above. You MUST specify an ORDER BY
    * clause (with ASC/DESC direction indicator) - if not then ordering
    * will be disabled.
    */
    $grid = RGrid::Create($params, $sql);
    
    $grid->Display();
?>

This will show a bare bones list of comments from this website. You will probably want to customise this quite a bit (appearance wise), which you can do with CSS, and with the various methods available to you (eg.SetDisplayNames()).

Features

Order by any of the columns in both ascending and descending directions

Tailored to MySQL

Allows any HTML in the columns (eg. Pictures)

Easily change the look and feel by using CSS

Friendly column names

Alternative column or row color for easy reading

Customisable mouse over highlight

You can hide columns

Automatic record paging (defaults to 25 records per page)

Fully supports all browsers: Internet Explorer, Firefox, Chrome, Safari and Opera

Handles large database recordsets.

Examples

Below is an IFRAME showing one of the example scripts which you can get from the download area. Bear in mind that the example scripts look more complicated than would be typical as they show off everything (or try to) and the HTML and CSS are in the same file as the PHP.

Example 1 :: Example 2 :: Example 3 :: Example 4 :: Example 5 :: Example 6 :: Example 7 :: Example 8 Example 9

Customisation

You can customise the "look and feel" using CSS. The grid uses a single table, which has the class datagrid. The headers are inside a tag, and the paging correspondingly in a tag. Using CSS you can then create corresponding CSS rules for them:

.datagrid thead th {
    background-color: #ddd;
    border-top: 2px solid #bbb;
    padding-left: 5px;
    text-align: left;
}

Naturally the body of the table uses a along with cells.

Structure

The tag structure of the datagrid is explained by this picture. Consider it "nice" structure, as it should be. The table has a class attribute of datagrid, the headers are in cells, which themselves are in a tag. The body rows are in cells, and a tag. And the footer is in a tag.
Doing it this way allows finer grained control over the style. eg: .datagrid thead th is the CSS selector that you would use to change the style of the headers.

Method reference

What follows is a list of public methods and what they do. You will probably be able to guess from their names though. They're in no particular order.

Static methods

RGrid::CreateFromArray($array)
Returns an RGrid object created using the (associative) array that you pass to it (called statically).

RGrid::Create($connection[, $sql])
Returns an RGrid object based on the parameters that you pass to it. $connection can either be a MySQL connection and the second an SQL query which provides the desired results. Or $connection can be an (associative) array (and the second argument omitted) to create a datagrid based on that.

Regular methods

Constructor (eg $grid = new RGrid($connection[, $resultset]))
Creates a new RGrid object. You should pass it your MySQL connection ($connection) and the result set that was returned from your call to mysql_query(). The result set is what the datagrid will show. If you want an array based datagrid then you can pass an array as the first argument and no second argument. There's no real difference between using the second method versus the static shortcut methods above.

$grid->SetDisplayNames($names)
This function sets the display names of the columns. You should pass it an associative array of column names (the actual column name as the index, and the desired name as the value). See the example scripts for an example.

$grid->HideColumn('column', ...)
Specifies the given column names (actual names) as hidden - ie not displayed. Give one or more column names.

$grid->NoSpecialChars('column', ...)
Specifies that the given column names will not have htmlspecialchars() applied to them. You need to use this if you want to have HTML in your column.

$grid->NoSort('column', ...)
Specifies the given column names cannot be sorted (ie they're not linked). Give the function one or more column names.

$grid->GetPageCount()
Returns the number of pages in the dataset.

$grid->GetRowCount()
Returns the number of rows in the result set.

$grid->GetResultset()
Returns the underlying MySQL result set.

$grid->GetConnection()
Returns the under lying MySQL connection.

$grid->SetHeaderHTML($html)
Sets the header HTML that is displayed above the actual datagrid. This is not related to the column headers - it is displayed above the actual datagrid.

$grid->SetConnection($connection)
Sets the MySQL connection. Not that you should need to, but here it is.

$grid->SetPerPage($num)
Sets the amount of rows that are displayed per page.

$grid->SetResultset($resultset)
Sets the MySQL result set that is used to make the datagrid.

$grid->AddCallback($function)
This is what you can use to customise the datagrid. The argument is the name of a function that is called for every row in the datagrid. The function is passed the row as the only argument. Specify the argument to be passed by reference. Like this:

$grid->AddCallback('RowCallback');

function RowCallback(&$row)
{
    if ($row['status'] == 'PASS') {
        $row['status'] = 'PASS';
    } else {
        $row['status'] = 'FAIL';
    }
}

$grid->Display()
Shows the datagrid. If you want it assigned to a variable, then you can use output buffering (ob_start() etc).

PHP Guru

Saturday, June 5, 2010