Pagination and Filtering

PHPDevShell offers a plugin (Pagination) that empowers you to easily create database listings that enable your users to:

• Search for records
• Filter by column
• Page through records

To make it as easy as possible we will use the ExamplePlugin as reference for this tutorial, you will find it easier to follow if you open the ExamplePlugin.

It all happens in the Model

Like you would expect the hardcore queries happens in the model, and this is where we will start with this guide. Lets open

plugins/ExamplePlugins/models/list-example.query.php

To begin the pagination process one needs to override the invoke method of your model, like this:

class ExamplePlugin_readExampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			id, example_name, example_note, alias
		FROM
			_db_ExamplePlugin_example
    ";

	/**
	 * Initiate query invoke command.
	 * @param int
	 * @return array
	 */
	public function invoke($parameters)
	{
		// Pagination will happen here...
	}
}

The next step is to call the pagination plugin, this is simply done by:

	public function invoke($parameters)
	{
		// Initiate pagination plugin.
		$pagination = $this->factory('pagination');
	}

This will give us access to all the needed features of the pagination class.

Selecting the Columns

The columns assignment serves many purposes, writing code only once you will;

• Set what columns to search in when using the search box.
• Creates the html for the heading tags.
• Creates the ordering and filtering icons for the tags.

You will be assigning values to the pagination object array called

$pagination->columns;

Array Key

The key of the array is the column name inside the tag of the auto created header. It can be left empty to include the specified database column name in the value field without showing it in the header.

Value

The value part is the database column/field name. This will be included in the selections for the search filter. This value can also be left empty, and if a key is specified, the header tag will still be created for alternative columns.

Assigning the columns for pagination and filtering:

		// Assign columns and th headings.
		$pagination->columns = array(
			_('Example ID') => 'id',
			_('Example Name') => 'example_name',
			_('Example Notes') => 'example_note',
			_('Example Alias') => 'alias',
			_('Edit Example') => '',
			_('Delete Example') => '');

Now that we have set our column we are ready to call our query above. When no pagination is required, we would normally just call the parent invoke method

$example_array = parent::invoke();

however, since we want to call the paginations plugins query method (so we can manipulate the sql), we do this instead:

$get_results = $pagination->query($this->sql);

This now contains the array of data required to complete the columns results. Before we continue we will look at what else is returned to us after we used the query call.

Just like with invokeQuery you can also pass values to replace %u, %s etc with. This is passed the same way as invokeQuery like this:

$get_results = $pagination->query($this->sql, $var1, $var2, $var3, etc...);

Search Form

Creating your search for is easy, the nice thing about the search form is that is remembers what you searched per user. Draw the search form calling a single method:

$RESULTS['searchForm'] = $pagination->searchForm();

Column th Headings

The returned and drawn up html th heading is gathered by using method:

$RESULTS['th'] = $pagination->th();

Pagination Links

We would also need the links to allow the navigation through the different pages. We get this using this method:

$RESULTS['pagination'] = $pagination->navPages();

Other properties...

There are a few methods that can be override, however, the provided properties will mostly be enough to get around.

Condition

For pagination, the system adds its own condition to the sql, $pagination->condition is to manually specify what to add before its own condition starts. But as I mentioned this will be added automatically by the system and can be left alone.

PHPDevShell will automatically determine whether AND or WHERE should be used. You can use

$pagination->condition = 'AND'

if you manually want to determine this. However, if I used WHERE in query above, AND would have been used automatically.

Extra SQL

You can add extra sql at the end of the query using

$pagination->extraCond = 'more sql...'

This is simply a condition that will be added after all SQL, it could be usefull in some cases, however, it will mostly not be useful. Useful for;

$pagination->extraCond = 'GROUP BY student_name';

Limiting paging results

PHPDevShell will use the page result limit as set in the general settings ui, however, it can be override by using

$pagination->limitCount = 50;

Note that limitCount needs to be before all the other calls in order to work properly, like this:

$pagination->limitCount = 50;
$get_results = $pagination->query($this->sql);
$RESULTS['th'] = $pagination->th();
$RESULTS['searchForm'] = $pagination->searchForm();
$RESULTS['pagination'] = $pagination->navPages();

Looping and collecting results

The last part in the model is to collect the results in our results array, add the extra edit and delete colums and return it to our controller.

Looping will be done normally and reassigned to our $RESULTS array to return it to the controller.

		// Simply loop through the results and build values.
		foreach ($get_results as $e) {
			$id = $e['id'];
			$example_name = $e['example_name'];
			$example_note = $e['example_note'];
			$alias = $e['alias'];

			$RESULTS['list'][] = array(
				'id' => $id,
				'example_name' => $example_name,
				'example_note' => $example_note,
				'alias' => $alias,
				'edit_example' => "<a href=\"{$page_edit}{$id}\" class=\"button\">{$this->template->icon('key--pencil', _('Edit Example'))}</a>",
				'delete_example' => "<a href=\"{$page_delete}{$id}\" {$this->core->confirmLink(sprintf(_('Are you sure you want to DELETE : %s'), $example_name))} class=\"button\">{$this->template->icon('key--minus', _('Delete Example'))}</a>"
			);
		}
		if (! empty($RESULTS['list'])) {
			return $RESULTS;
		} else {
			$RESULTS['list'] = array();
			return $RESULTS;
		}

We now have the pagination results and all the html we need to build a fully paginated page.

Our Controller

The controller part of things are just as easy, we will simply invoke the query and assign our different parts to different variables for the view to handle. You will see that the different parts of $RESULTS will be re split here;

		// Lets pass the array to the view so he can loop and output the results.
		$RESULTS = $this->db->invokeQuery('ExamplePlugin_readExampleQuery');

		// Load views plugin.
		$view = $this->factory('views');

		// Assign different parts of our pagination to different variables.
		$view->set('pagination', $RESULTS['pagination']);
		$view->set('searchForm', $RESULTS['searchForm']);
		$view->set('th', $RESULTS['th']);
		$view->set('RESULTS', $RESULTS['list']);

		// Output to views.
		$view->show();

This is basically all we need from the controller.

View

The last remaining step is to place the passed variables in the view for processing. These variables contains standard strings and arrays. So it does not matter what type of view you have, you can just loop through it using appropriate methods. We now simply fill in the variables where we want them.

{$searchForm}
<table class="floatHeader">
    <thead>
		<tr>
			{$th}
		</tr>
    </thead>
    <tbody>
        {foreach item=groups from=$RESULTS}
        {strip}
        <tr>
            <td>
                {$groups.id}
            </td>
            <td>
                {$groups.example_name}
            </td>
            <td>
                {$groups.example_note}
            </td>
            <td>
                {$groups.alias}
            </td>
            <td>
                {$groups.edit_example}
            </td>
            <td>
                {$groups.delete_example}
            </td>
        </tr>
        {/strip}
        {foreachelse}
        <tr>
           <td class="no_results" colspan="6">
            {_e('Your filter request does not match any data.')}
           </td>
        </tr>
        {/foreach}
    </tbody>
	<tfoot>
        <tr>
            <td colspan="6">
                {$pagination}
            </td>
        </tr>
	</tfoot>
</table>

{$searchForm}
<style type="text/css">
    th:nth-child(1) { width: 5%; }
    th:nth-child(2) { width: 15%; }
    th:nth-child(3) { width: 65%; }
    th:nth-child(4) { width: 5%; }
    th:nth-child(5) { width: 5%; }
    th:nth-child(6) { width: 5%; }
</style>

<table class="floatHeader">
.
.
.

This should now work, make sure to check the ExamplePlugin to view full mvc for the code to make sense.