Note: each tutorial contains information about one specific part of Spoon. If something is unclear, post a comment.

Datagrid - part 1

Written by Dave Lens -

Prerequisites

What will you learn?

Tutorial

Intro

A datagrid represents data in a grid view. Whether it's used for statistics or mapping actions to certain records in your backend, in the end it comes down to giving users an overview of a specific data set.

Test data

We will use the same test data as illustrated in the SpoonDatabase tutorial. For your convenience, here's the download link again for the dump of test database spoondb.

Two types of datagrid sources

The first parameter of the SpoonDataGrid class requires a datagrid source object to work with. There are two different sources: database queries, or arrays.

Database queries

// load Spoon
require_once 'spoon/spoon.php';

// make a connection
$db = new SpoonDatabase('mysql', 'localhost', 'user', 'pass', 'spoondb');

// build query
$query = '
SELECT u.id, u.username, u.email, us.value AS website
FROM users AS u
INNER JOIN users_settings AS us ON us.user_id = u.id
WHERE us.name = ?';

// set parameters
$parameters = array();
$parameters[] = 'website';

// fetch the records we need
$records = $db->getRecords($query, $parameters);

// create a new source-object from a mysql query
$source = new SpoonDataGridSourceDB($db, array($query, $parameters));

Note: This is why you need to know how SpoonDatabase works, the parameter syntax is also used in SpoonDataGridSourceDB.

Arrays

// load Spoon
require_once 'spoon/spoon.php';

// set records
$records[] = array('name' => 'Dave', 'total_pizzas' => 5);
$records[] = array('name' => 'Davy', 'total_pizzas' => 6);
$records[] = array('name' => 'Tijs', 'total_pizzas' => 7);

// create a new source-object from an array
$source = new SpoonDataGridSourceArray($records);

For all further datagrid code examples in this and the following tutorials, we will assume you have a datagrid source set in the variable $source, as shown in the first example above.

A basic datagrid

The following example is the most basic datagrid you can have, with a few additions.

// create a new datagrid instance for a given datagrid source
$datagrid = new SpoonDatagrid($source);

// set compile directory for compiled datagrid templates
$datagrid->setCompileDirectory('/path/to/your/compile/dir');

// hide the 'id' column
$datagrid->setColumnHidden('id');

// sets column attributes - adds a css class "name" to the column "username"
$datagrid->setColumnAttributes('username', array('class' => 'username'));

// this changes the heading value for columns by key/pair values
$datagrid->setHeaderLabels(array('username' => 'name'));

// return the content
echo $datagrid->getContent();

HTML output:

<table>
	<thead>
		<tr>
			<th>name</th>
			<th>email</th>
			<th>website</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="username">Tijs</td>
			<td class="email">tijs@spoon-library.be</td>
			<td class="website">http://blog.verkoyen.eu</td>
		</tr>
		<tr>
			<td class="username">Davy</td>
			<td class="email">davy@spoon-library.be</td>
			<td class="website">http://blog.bauffman.be</td>
		</tr>
		<tr>
			<td class="username">Dave</td>
			<td class="email">dave@spoon-library.be</td>
			<td class="website">http://www.davelens.be</td>
		</tr>
	</tbody>
</table>

A basic datagrid, with paging and sorting

Let's expand our previous example and add paging and sorting functions to the whole. Here are the added functions:

  • setURL: Sets the default URL for the datagrid. It accepts datagrid URL variables.
  • setSortingColumns: Sets the columns that can be sorted on, as well as the default.
  • setPagingLimit: Sets the amount of maximum results for each page.
// create a new datagrid instance for a given datagrid source
$datagrid = new SpoonDatagrid($source);

// set the datagrid URL
$datagrid->setURL('?offset=[offset]&order=[order]&sort=[sort]');

// set compile directory for datagrid templates
$datagrid->setCompileDirectory('/path/to/your/compile/dir');

// hide the 'id' column
$datagrid->setColumnHidden('id');

// sets column attributes
$datagrid->setColumnAttributes('username', array('class' => 'username'));

// sets the header labels
$datagrid->setHeaderLabels(array('username' => 'name'));

// set the sorting columns
$datagrid->setSortingColumns(array('username', 'email'), 'username');

// set the paging limit
$datagrid->setPagingLimit(2);

// return the content
echo $datagrid->getContent();

If you take a look at the setURL() line, you will notice that offset, order and sort are encapsulated in brackets. These are handled internally by the class so all your datagrid links change dynamically whenever the sorting changes.

Also remember that if you intend to use pagination, you must set the datagrid URL for your next/previous links to work.

Aside from [offset], [order] and [sort], every column name encapsulated in brackets ( [ ] ) will point to the current active record's column value. If this seems confusing, don't worry. This feature is covered in part two of the datagrid series of tutorials.

Below is the HTML output of the example above. Notice that the headers now have links that, when clicked, change sorting. A "previous" and "next" option is added, as well as the number of available pages. Because our paging limit is set to 2 and there are a total of 3 records, we end up with a total of two pages:

<table>
	<thead>
		<tr>
			<th><a href="?offset=0&order=username&sort=desc" title="Sort ascending">name</a></th>
			<th><a href="?offset=0&order=email&sort=asc" title="Sorted ascending">email</a></th>
			<th>website</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td class="username">Dave</td>
			<td class="email">dave@spoon-library.be</td>
			<td class="website">http://www.davelens.be</td>
		</tr>
		<tr>
			<td class="username">Davy</td>
			<td class="email">davy@spoon-library.be</td>
			<td class="website">http://blog.bauffman.be</td>
		</tr>
	</tbody>
	<tfoot>
		<tr>
			<td colspan="3">
				&laquo; previous
				<strong>1</strong>
				<a href="?offset=2&order=name&sort=asc">2</a>
				<a href="?offset=2&order=name&sort=asc" title="next">next &raquo;</a>
			</td>
		</tr>
	</tfoot>
</table>

If you want a visual representation of how your columns are currently sorted, you can use setSortingIcons() to add images to your ascended / descended states.

Conclusion

You have learned to load data (coming from a database or an array) into a grid view. With just a few functions you have added paging and sorting and are able to add attributes or even hide a specified column. The next tutorial will show you how to manipulate data by mapping callback functions directly to your datagrid.

3 comments

Arnout

Arnout wrote 6 months ago

Is it possible to set a default sorting column that is hidden and no other other sorting columns?

Tried something like this:
$datagrid->setSortingColumns(array(), 'ColumnName');
But this gives following error: The column "" doesn't exist and therefor can't be sorted on.
Bauffman

Bauffman wrote 6 months ago

@arnout what you want to achieve is indeed possible. Try this:

$datagrid->setSortingColumns(array('ColumnName'), 'ColumnName');
Ronald Miller

Ronald Miller wrote 3 months ago

how you could use stored procedures? I get error when passing parameters
$ query = 'callclients_list ()'
/ / Set parameters
$ parameters = array ();
$ parameters [] = 'website';
I get the following error:
Catchable fatal error: Argument 1 passed to SpoonDatagridSourceArray::__construct() must be an array, null given, called in C:\xampp\htdocs\tracing\clients\index.php on line 140 and defined in C:\xampp\htdocs\Spoon\spoon\datagrid\source_array.php on line 43

Comment

Spoon documentation

Sites that love Spoon