<?php
/*******************************************************************************
* This is a class for spliting page in Interbase/Firebird. As you know, 'LIMIT' clause is supported in MySQL SQL syntax, it is easy to
* paginating data. Since other DB don't support this clause, it is difficult to split page if the SQL result set is huge.
* This class use a loop to move your result set point to according to your specify page, next you can get desire result set.
* If your DB is other than Interbase, it also can be used after making a litte modification, (only use php standard DB function of
* your DB to change ibase_query, ibase_fetch_row, ibase_fetch_object).
*
* Functions :
* function SplitPageResults($currentpagenumber, $sql_query)
* -- Construct function, analyst sql statment and get total record number, prepare public variable.
* -- $currentpagenum - current page no.
* -- $sql_query - sql statement.
* function display_links()
* -- Display page links. e.g. Page : << 5 of 10 >>
* function display_count()
* -- Display record count. e.g. Displaying 31 to 60 (of 77 records)
* function fetchrow(&$sql_result, $type)
* -- fetch sql result one by one.
* -- $sql_result sql result variable. in future you will use $sql_result[..] to load result.
* -- $type 0 - use ibase_fetch_row to fetch data.
* 1 - use ibase_fetch_array to fetch data.
*
* function goheader( &$sql_result, $type)
* -- move db result pointer into correct position. -- it will be called inside function fetchrow.
* -- $sql_result sql result variable. in future you will use $sql_result[..] to load result.
* -- $type 0 - use ibase_fetch_row to fetch data.
* 1 - use ibase_fetch_array to fetch data.
* Attention :
* 1. display_count(), display_links() can't be appeared inside "<form></form>" tags because there are "<form>" tags inside.
* 2. page number are specified on URL, e.g. http://www.yourcompany.com/display.php?page=10, page number = 10
* 3. At present it can't work very well when your sql statement includes keyword 'DISTINCT'.
*
* @version 0.1
* @author Kelvin Ma - Span International
* @email : hide@address.com
*
* Some codes are from osCommerce, Open Source E-Commerce Solutions, http://www.oscommerce.com
*
* Released under the GNU General Public License
******************************************************************************/
/*
Here is an example to show how to use this class.
$sql = 'SELECT FID, FACTORYNAME FROM FACTORY';
$page_split = new SplitPageResults($sql);
echo "<table width=100% BORDER=0 CELLPADDING=0 CELLSPACING=0><tr><td align=left>".$page_split->display_count()."</td><td align=right>".$page_split->display_links()."</td></tr></table>";
echo "<TABLE BGCOLOR=2A66AA BORDER=0 CELLPADDING=0 CELLSPACING=0><TR><TD><TABLE BORDER=0 CELLPADDING=1 CELLSPACING=1><TR BGCOLOR=#3388EE><TD COLSPAN=5><FONT SIZE=2 COLOR=WHITE><CENTER><B>Factory list</B></CENTER></FONT></TD></TR><TR BGCOLOR=#6BAAEE><TD>FACTORY ID</TD><TD>FACTORY NAME</TD></TR>";
while ($page_split->fetchrow($ibrow, 0)) {
echo "<tr><td>".$ibrow[0]."</td><td>".$ibrow[1]."</td></tr>";
}
echo "</TABLE></TD></TR></TABLE>";
*/
define('TEXT_DISPLAY_NUMBER_OF_REVIEWLIST', 'Displaying <b>%d</b> to <b>%d</b> (of <b>%d</b> records)');
define('PREVNEXT_BUTTON_PREV', '<<');
define('PREVNEXT_BUTTON_NEXT', '>>');
define('TEXT_RESULT_PAGENATION', 'Page %s of %d');
define('DEFAULT_DISPLAY_SEARCH_RESULTS',30);
class SplitPageResults {
var $max_rows_per_page = DEFAULT_DISPLAY_SEARCH_RESULTS;
var $query_numrows;
var $firstline;
var $endline;
var $currentline;
var $sqlquery;
var $current_page_number;
var $gofirst = 0;
function SplitPageResults($sql_query) {
global $HTTP_GET_VARS;
if (!isset($HTTP_GET_VARS['page']) || $HTTP_GET_VARS['page']=='') $this->current_page_number = 1;
else $this->current_page_number = $HTTP_GET_VARS['page'];
$this->max_rows_per_page = DEFAULT_DISPLAY_SEARCH_RESULTS;
$sql_query = strtoupper($sql_query);
$pos_to = strlen($sql_query);
$pos_from = strlen($sql_query) - strpos (strrev($sql_query), 'MORF ') - 5;
$pos_group_by = strpos($sql_query, ' GROUP BY', $pos_from);
if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;
$pos_having = strpos($sql_query, ' HAVING', $pos_from);
if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;
$pos_order_by = strpos($sql_query, ' ORDER BY', $pos_from);
if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;
$reviews_count_query = ibase_query("SELECT COUNT(*) AS TOTAL " . substr($sql_query, $pos_from, ($pos_to - $pos_from)));
$reviews_count = ibase_fetch_row($reviews_count_query);
$this->query_numrows = $reviews_count[0];
$this->sqlquery = ibase_query($sql_query);
}
function display_links() {
global $REQUEST_URI, $HTTP_GET_VARS, $HTTP_POST_VARS;
$num_pages = intval($this->query_numrows / $this->max_rows_per_page);
if ($this->query_numrows % $this->max_rows_per_page) $num_pages++;
if ($num_pages > 1) {
$display_links = "<form name='pagenation' method='get' action='".replaceurl($REQUEST_URI,'page','')."'>";
foreach ($HTTP_GET_VARS as $key=>$value){
if ($key != 'page') $display_links .= "<input type=hidden name='".$key."' value='".$value."'>";
}
foreach ($HTTP_POST_VARS as $key=>$value){
if ($key != 'page') $display_links .= "<input type=hidden name='".$key."' value='".$value."'>";
}
if ($this->current_page_number > 1) {
$display_links .= "<a href='".replaceurl($REQUEST_URI,'page',($this->current_page_number - 1))."'>". PREVNEXT_BUTTON_PREV . "</a> ";
} else {
$display_links .= PREVNEXT_BUTTON_PREV . ' ';
}
$s = "<select name=page size=1 onchange='this.form.submit();'>";
for ($i=1; $i<=$num_pages; $i++) {
$s .= "<option value=$i";
if ($i==$this->current_page_number) $s .= " selected";
$s .= ">$i</option>";
}
$s .= "</select>";
$display_links .= sprintf(TEXT_RESULT_PAGENATION, $s, $num_pages);
if (($this->current_page_number < $num_pages) && ($num_pages != 1)) {
$display_links .= ' <a href="' . replaceurl($REQUEST_URI,'page',($this->current_page_number + 1)) . '">' . PREVNEXT_BUTTON_NEXT . '</a>';
} else {
$display_links .= ' ' . PREVNEXT_BUTTON_NEXT;
}
} else {
$display_links = sprintf(TEXT_RESULT_PAGENATION, $num_pages, $num_pages);
}
$display_links .= '</form>';
return $display_links;
}
function display_count() {
$to_num = ($this->max_rows_per_page * $this->current_page_number);
if ($to_num > $this->query_numrows) $to_num = $this->query_numrows;
$from_num = ($this->max_rows_per_page * ($this->current_page_number - 1));
if ($to_num == 0) {
$from_num = 0;
} else {
$from_num++;
}
return sprintf(TEXT_DISPLAY_NUMBER_OF_REVIEWLIST, $from_num, $to_num, $this->query_numrows);
}
function goheader( &$sql_result, $type) {
$num = ($this->current_page_number - 1) * $this->max_rows_per_page;
if ($num > 0) {
for ($i=1;$i<=$num;$i++) {
if ($type==0) $sql_result = ibase_fetch_row($this->sqlquery);
else $sql_result = ibase_fetch_object($this->sqlquery);
}
}
$this->currentline = $num + 1;
$this->endline = ($this->max_rows_per_page * $this->current_page_number);
if ($this->endline > $this->query_numrows) $this->endline = $this->query_numrows;
$this->firstline = ($this->max_rows_per_page * ($this->current_page_number - 1));
if ($this->endline == 0) {
$from_num = 0;
} else {
$from_num++;
}
}
function fetchrow(&$sql_result, $type) {
if ($this->gofirst == 0) {
$this->goheader($sql_result,$type);
$this->gofirst = 1;
}
if ($this->currentline > $this->endline) return false;
else {
if ($type==0) $sql_result = ibase_fetch_row($this->sqlquery);
else $sql_result = ibase_fetch_object($this->sqlquery);
$this->currentline ++;
}
return true;
}
}
function replaceurl($url,$field,$value)
{
$i = strpos($url,'&'.$field);
if ($i==false) {
$i = strpos($url,'?'.$field);
$sign = '?';
} else $sign = '&';
if ($i == false) {
if ($value=='') return $url;
else {
$i = strpos($url, '?');
if ($i==false) return $url."?$field=$value";
else return $url."&$field=$value";
}
} else {
$s1 = substr($url,$i);
$j = strpos($s1,'&');
if ($j==false) {
if ($value=='') $s2 = substr($url,0,$i);
else $s2 = substr($url,0,$i) . $sign .$field."=".$value;
} else {
if ($value=='') $s2 = substr($url,0,$i) . substr($s1, $j);
else $s2 = substr($url,0,$i) . $sign .$field."=".$value . substr($s1, $j);
}
return $s2;
}
}
?>