Paginating Database Records with the jQuery Quick Pagination Plug-in

As I expressed in the course of the previous section, in this particular example I’m going to use a PHP class for pulling user-related data from the MySQL table just created. However, it’s possible to obtain the same results by using a different server-side language; keep this in mind, especially if you’re not familiar with PHP or you simply don’t feel comfortable working with it.

Having clarified that point, it’s time to show the definition of the PHP class. Among other useful tasks, it allows us to connect to MySQL, perform hard-coded SQL queries and fetch database rows via a set of straightforward methods. Here’s how it looks:

(MySQLAdapter.php)

<?php

class MySQLAdapter

{

private $_config = array();

private $_link = null;

private $_result = null;

private static $_instance = null;

private static $_connected = false;

// return Singleton instance of MySQLAdapter class

public static function getInstance(array $config = array())

{

if (self::$_instance === null)

{

self::$_instance = new self($config);

}

return self::$_instance;

}

// private constructor

private function __construct(array $config)

{

if (count($config) < 4)

{

throw new MySQLAdapterException('Invalid number of connection parameters');

}

$this->_config = $config;

}

// prevent cloning class instance

private function __clone(){}

// connect to MySQL

private function connect()

{

// connect only once

if (self::$_connected === false)

{

list($host, $user, $password, $database) = $this->_config;

if ((!$this->_link = mysqli_connect($host, $user, $password, $database)))

{

throw new MySQLAdapterException('Error connecting to MySQL : ' . mysqli_connect_error());

}

self::$_connected = true;

unset($host, $user, $password, $database);

}

}

// perform query

public function query($query)

{

if (is_string($query) and !empty($query))

{

// lazy connect to MySQL

$this->connect();

if ((!$this->_result = mysqli_query($this->_link, $query)))

{

throw new MySQLAdapterException('Error performing query ' . $query . ' Error : ' . mysqli_error($this->_link));

}

}

}

// fetch row from result set

public function fetch()

{

if ((!$row = mysqli_fetch_object($this->_result)))

{

mysqli_free_result($this->_result);

return false;

}

return $row;

}

// get insertion ID

public function getInsertID()

{

if ($this->_link !== null)

{

return mysqli_insert_id($this->_link);

}

return null;

}

// count rows in result set

public function countRows()

{

if ($this->_result !== null)

{

return mysqli_num_rows($this->_result);

}

return 0;

}

// close the database connection

function __destruct()

{

is_resource($this->_link) and mysqli_close($this->_link);

}

}

Even though the definition of the brand new “MySQLAdapter” class shown above seems to be rather complex at first sight, in reality its driving logic is quite simple. As I said before, this class simply establishes a connection to the database server, runs queries and retrieves rows in results sets. Of course, there are a couple of additional methods, such as “getInsertId()” and “countRows()” that speak for themselves, so I won’t waste your valuable time explaining what they do.

In addition, you may have noticed that “MySQLAdapter” throws a couple of custom exceptions inside some methods, so in keeping with this, below I included the class that originates those specific exception objects. Here it is:

(MySQLAdapterException.php)

<?php

class MySQLAdapterException extends Exception{}

Now things are becoming a little more interesting, as I already defined a sample MySQL abstraction class for fetching user data, which will be fancily paginated thanks to the functionality of the Quick Pagination jQuery plug-in. But wait a minute! I’m getting ahead of myself, as there are some additional things that must be done before reaching that glorious point.

Since both the retrieval of database records and the pagination process will take place in a separate area of the web page, in the last part of this article I’m going to create the static segments of this page. They will be responsible for displaying the header and footer sections respectively.

Do you want to see how these static sections will be created? Then read the lines to come.