Location: PHPKode > scripts > AJAX Paginator > ajaxpaginator.class.php
<?php
/**
 * Paginator
 * @desc Ajax pagination class
 * @author Omar Abdallah
 * @license GPL
 * @version 1.5
 * @todo add SQL_CALC_FOUND_ROWS
 */

class AjaxPaginator{
	
	/**
	 * total pages found for this query
	 * @var integer
	 */
	public $totalPages;
	
	/**
	 * 
	 * @var integer
	 */
	public $recordsPerPage ='10';
	
	/**
	 * Total records found from the query (without LIMIT)
	 * @var integer
	 */
	public $affected_rows;
	
	/**
	 * current page id
	 * @var integer
	 */
	public $pageId;
	
	/**
	 * the offset or the record to start from (used in the LIMIT clause)
	 * @var integer
	 */
	public $offset;
	
	/**
	 * The raw query submitted by the user
	 * @var string
	 */
	private $query;
	
	/**
	 * MySQLi connection Object
	 * @var object
	 */
	private $conn;
	
	/**
	 * mysqli_result object made for more flexibility
	 * for more info: http://us.php.net/manual/en/class.mysqli-result.php
	 * @var object
	 */
	public $result;
	
	/**
	 * the search query used in the like clause if it was specified
	 * @var string
	 */
	public $searchQuery='';
	
	/**
	 * fields to search in
	 * @var string | array
	 */
	public $fields='';
	
	/**
	 * How many links for the next and the previous
	 * @var integer
	 */
	public $linksCountLimit = 4;
	
	/**
	 * Debug variable
	 * @var bool
	 */
	public $debug=false;
	
	/**
	 * Group By clause
	 * @var string
	 */
	private $groupBy = '';
	
	/**
	 * Order By clause
	 * @var string
	 */
	private $orderBy = '';
	
	
	/**
	 * 
	 * @param $in_pageID id of the current page
	 * @param $in_recordsPerPage number of records per page
	 * @param $in_query SQL query
	 * @param $in_conn database connection object(mysqli)
	 * @return
	 */
	function __construct($in_pageID=1,$in_recordsPerPage,$in_query,$in_conn){
		$this->pageId = intval($in_pageID);
		$this->recordsPerPage = $in_recordsPerPage;
		$this->query = $in_query;		
		$this->conn = $in_conn;
		
		// order by position
		$orderbyPos = stripos($this->query,'ORDER BY');
		if($orderbyPos !== FALSE){
			$this->orderBy = " ". substr($this->query, $orderbyPos);
			$this->query = substr($this->query, 0, $orderbyPos);
		}
		// group by position
		$groupbyPos = stripos($this->query,'GROUP BY');
		if($groupbyPos !== FALSE){
			$this->groupBy = " ". substr($this->query,$groupbyPos);
			$this->query = substr($this->query,0,$groupbyPos);
		}
	}
	
	private function getAffectedRows(){
		if (! empty ( $this->searchQuery )) {
			$this->addSearch();
		}
		$result = $this->conn->query($this->query. $this->groupBy . $this->orderBy);
		
		if($result== FALSE){
			$err = 'Error: Cannot get affected rows';
			if($this->debug){
				$err .="<br /><b>Query: </b>". $this->query. $this->groupBy . $this->orderBy;
				$err .="<br /><b>Error: </b>". $this->conn->error;
			}
			throw new Exception($err);
		}
		
		$affected_rows = $this->conn->affected_rows;
		return $affected_rows;
	}
	
	/**
	 * The Core function which does the actual pagination
	 * 
	 * @return array Associative array of rows returned
	 */
	public function paginate(){
		
		$this->offset = $this->getOffest();
		
		$this->affected_rows = $this->getAffectedRows();
		$this->totalPages = $this->getTotalPages();
        
        if($this->totalPages==0)$this->totalPages =1;
        		
        // construct the pagination query: in_query + groupby clause + order by clause + limit clause
		$pageQ = $this->query . $this->groupBy . $this->orderBy. " LIMIT " . $this->offset . " , " . $this->recordsPerPage . ";";
		if ($this->debug){
			echo $pageQ;
		}
		
		$this->result = $this->conn->query ( $pageQ );
		if ($this->result == false) {
			$err = 'Error: Cannot Execute Pagination Query';
			if($this->debug){
				$err .="<br /><b>Query</b>". $pageQ;
			}
			throw new Exception ( $err );
		}
		$rows  = array();
		while ( $row = $this->result->fetch_assoc () ) {
			$rows[] = $row;
		}
		
		return $rows;
	}
	
	/**
	 * Add search capability by manipulating the SQL query to handle the like clause
	 * 
	 * @return 
	 */
	private function addSearch(){
		$this->searchQuery = $this->conn->real_escape_string ( $this->searchQuery );
			if (is_array ( $this->fields )) {
				$count = count ( $this->fields );
				for($i = 0; $i < $count; $i ++) {
					// its the first field we have to check for WHERE clause
					if ($i == 0) {
						if (stripos ( $this->query, 'WHERE' )) {
							$this->query .= " AND ({$this->fields[0]} like '$this->searchQuery%'";
						} else {
							$this->query .= " WHERE ({$this->fields[0]} like '$this->searchQuery%'";
						}
					}else{
						$this->query .= " OR {$this->fields[$i]} like '$this->searchQuery%'";
					}
				}
				$this->query .= ") ";
			} else {
				// if only single field to search in
				if (stripos ( $this->query, 'where' )) {
					$this->query .= " AND name like '$this->searchQuery%'";
				} else {
					$this->query .= " WHERE name like '$this->searchQuery%'";
				}
			}
	}
	
	/**
	 * Gets the offset(start number of the LIMIT clause)
	 *
	 * @return string $offset 
	 */
	private function getOffest(){
		// multiply id with no_items_per_page
		$offset = ($this->pageId - 1) * $this->recordsPerPage;
		return $offset;
	}
	
	/**
	 * Get's the number of pages required to display the result
	 *
	 * @return string $totalPages
	 */
	public function getTotalPages(){
		// return the float to higher integer ex:4.5 to 5,3.2 to 4
		$totalPages= ceil($this->affected_rows/$this->recordsPerPage);
		
		return $totalPages;
	}
	
	/**
	 * Generates the pagination links at the bottom of the page
	 * @param $class css class that will be added to the <a> tag
	 * @return string
	 */
	public function getLinks($class = "") {
		
		$output = '';
		// if the current page is not the first
		if ($this->pageId > 1) {
			
			
			$count = 1;
			for($i = $this->pageId; $i >= 1; $i --) {
				if ($count > $this->linksCountLimit)
					break;
				
				if ($i == $this->pageId)
					continue;
				
				$output = "<a href='?page={$i}' id='{$i}' class='{$class}'>{$i}</a>\r\n" . $output;
				
				$count ++;
			}
			
			//previous page link
			$prevPage = $this->pageId - 1;
			$output = "<a href='?page={$prevPage}' id='{$prevPage}' class='" 
			. $class . "'>Previous</a>\r\n" . $output;
			
			if ($prevPage > 1){
				// first page link
				$output = "<a href='?page=1' id='1' class='" . $class 
				. "'>First</a>\r\n" . $output;
			}
			
			
			
		}
		
		$output .= "<span class='thispage'>{$this->pageId}</span>\r\n";
		
		// next pages
		$count = 1;
		for($i = $this->pageId; $i < $this->totalPages; $i ++) {
			if ($count > $this->linksCountLimit)
				break;
			
			if ($i == $this->pageId)
				continue;
			
			$output .= "<a href='?page={$i}' id='{$i}' 
				 class='{$class}'>{$i}</a>\r\n";
			
			$count ++;
		}
		// next and last links
		if ($this->pageId < $this->totalPages) {
			// next link
			$next = $this->pageId + 1;
			$output .= "<a href='?page={$next}' id='{$next}' class='{$class}'>Next</a>\r\n";
			
			if ($this->totalPages != $next){
				// last page link
				$output .= "<a href='?page={$this->totalPages}' id='{$this->totalPages}' class='{$class}'>Last</a>\r\n";
			}
			
		}
		
		return $output;
	}
}

?>
Return current item: AJAX Paginator