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.

13 comments

Arnout wrote 2 years 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 wrote 2 years ago

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

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

Ronald Miller wrote 2 years 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

broodro0ster wrote 1 year ago

I'd like to add an extra column with a link to an edit page. The ID of the item should be included as a GET parameter in the link. What's to correct way to do this?

I can add an extra column in the datagrid with a link, but I can't find out how I can include the ID of the item. I'm using a BackendDataGridDB object as the source if that matters.

Bauffman wrote 1 year ago

in the 'url/link' part of the column you add you can do something like this 'myurl?i[id]'

That way you can include the column value in your link.

broodro0ster wrote 1 year ago

Thanks Bauffman, I tried [id] and $this->datagrid->getColumn('id')->getValue() yesterday, but both methods returned [id]. The problem was that I was using addURLParameter (Fork CMS) to buildup the link. I dropped that method and now it's working.

Thanks!

resolutionariesinc.com wrote 1 year ago

We stumbled over here from a different web page and thought
I may as well check things out. I like what I see
so i am just following you. Look forward to looking over
your web page again.

Ruben wrote 1 year ago

Thanks for your marvelous posting! I genuinely enjoyed reading it,
you can be a great author.I will ensure that I bookmark
your blog and will often come back in the future. I want to encourage that you continue your great job, have a nice evening!

Lela wrote 1 year ago

Thanks very interesting blog!

E-mail Auto-Responder Series wrote 1 year ago

This piece of writing gives clear idea designed for the new visitors of blogging,
that really how to do blogging and site-building.

films vostfr wrote 1 year ago

Hi, I do think this is a great web site. I stumbledupon it ;)
I will come back once again since I book marked it. Money and freedom is the greatest way to change, may you
be rich and continue to help other people.

Larhonda wrote 1 year ago

Hello there! I know this is somewhat off topic but I was
wondering if you knew where I could locate a captcha plugin for
my comment form? I'm using the same blog platform as yours and I'm having problems finding one?
Thanks a lot!

Hus i Antalya wrote 1 year ago

of course like your web-site however you need to test the
spelling on several of your posts. Several of them are rife with
spelling issues and I in finding it very bothersome to tell the
reality however I will definitely come back again.