Good day everybody, please I need another assistance. I’m working on how to paginate my returned data from mysql and below is the method that fetches the list of asset from the database.
I am using a pagination script I found online and making use of the script involves these lines of code to be added to th index for it to work:
<?php
$pages = new Paginator;
$pages->items_total = $num_rows[0];
$pages->mid_range = 9;
$pages->paginate();
echo $pages->display_pages();
?>
The script itself, I mean the class is here:
class Paginator{
var $items_per_page;
var $ipp;
var $items_total;
var $current_page;
var $num_pages;
var $mid_range;
var $low;
var $limit;
var $return;
var $default_ipp;
var $querystring;
var $ipp_array;
function Paginator()
{
if(!isset($_GET['page']))
{
$_GET['page'] = 1;
}
if(!isset($_GET['ipp']))
{
$_GET['ipp'] = $this->default_ipp;
}
$this->current_page = 1;
$this->mid_range = 7;
$this->ipp_array = array(10,25,50,100,'All');
$this->items_per_page = (!empty($_GET['ipp'])) ? $_GET['ipp']:$this->default_ipp;
}
function paginate()
{
if(!isset($this->default_ipp)) $this->default_ipp=25;
if($_GET['ipp'] == 'All')
{
$this->num_pages = 1;
// $this->items_per_page = $this->default_ipp;
}
else
{
if(!is_numeric($this->items_per_page) OR $this->items_per_page <= 0) $this->items_per_page = $this->default_ipp;
$this->num_pages = ceil($this->items_total/$this->items_per_page);
}
$this->current_page = (isset($_GET['page'])) ? (int) $_GET['page'] : 1 ; // must be numeric > 0
$prev_page = $this->current_page-1;
$next_page = $this->current_page+1;
if($_GET)
{
$args = explode("&",$_SERVER['QUERY_STRING']);
foreach($args as $arg)
{
$keyval = explode("=",$arg);
if($keyval[0] != "page" And $keyval[0] != "ipp") $this->querystring .= "&" . $arg;
}
}
if($_POST)
{
foreach($_POST as $key=>$val)
{
if($key != "page" And $key != "ipp") $this->querystring .= "&$key=$val";
}
}
if($this->num_pages > 10)
{
$this->return = ($this->current_page > 1 And $this->items_total >= 10) ? "<a class=\\"paginate\\" href=\\"$_SERVER[PHP_SELF]?page=$prev_page&ipp=$this->items_per_page$this->querystring\\">« Previous</a> ":"<span class=\\"inactive\\" href=\\"#\\">« Previous</span> ";
$this->start_range = $this->current_page - floor($this->mid_range/2);
$this->end_range = $this->current_page + floor($this->mid_range/2);
if($this->start_range <= 0)
{
$this->end_range += abs($this->start_range)+1;
$this->start_range = 1;
}
if($this->end_range > $this->num_pages)
{
$this->start_range -= $this->end_range-$this->num_pages;
$this->end_range = $this->num_pages;
}
$this->range = range($this->start_range,$this->end_range);
for($i=1;$i<=$this->num_pages;$i++)
{
if($this->range[0] > 2 And $i == $this->range[0]) $this->return .= " ... ";
// loop through all pages. if first, last, or in range, display
if($i==1 Or $i==$this->num_pages Or in_array($i,$this->range))
{
$this->return .= ($i == $this->current_page And $_GET['page'] != 'All') ? "<a title=\\"Go to page $i of $this->num_pages\\" class=\\"current\\" href=\\"#\\">$i</a> ":"<a class=\\"paginate\\" title=\\"Go to page $i of $this->num_pages\\" href=\\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page$this->querystring\\">$i</a> ";
}
if($this->range[$this->mid_range-1] < $this->num_pages-1 And $i == $this->range[$this->mid_range-1]) $this->return .= " ... ";
}
$this->return .= (($this->current_page < $this->num_pages And $this->items_total >= 10) And ($_GET['page'] != 'All') And $this->current_page > 0) ? "<a class=\\"paginate\\" href=\\"$_SERVER[PHP_SELF]?page=$next_page&ipp=$this->items_per_page$this->querystring\\">Next »</a>\
":"<span class=\\"inactive\\" href=\\"#\\">» Next</span>\
";
$this->return .= ($_GET['page'] == 'All') ? "<a class=\\"current\\" style=\\"margin-left:10px\\" href=\\"#\\">All</a> \
":"<a class=\\"paginate\\" style=\\"margin-left:10px\\" href=\\"$_SERVER[PHP_SELF]?page=1&ipp=All$this->querystring\\">All</a> \
";
}
else
{
for($i=1;$i<=$this->num_pages;$i++)
{
$this->return .= ($i == $this->current_page) ? "<a class=\\"current\\" href=\\"#\\">$i</a> ":"<a class=\\"paginate\\" href=\\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page$this->querystring\\">$i</a> ";
}
$this->return .= "<a class=\\"paginate\\" href=\\"$_SERVER[PHP_SELF]?page=1&ipp=All$this->querystring\\">All</a> \
";
}
$this->low = ($this->current_page <= 0) ? 0:($this->current_page-1) * $this->items_per_page;
if($this->current_page <= 0) $this->items_per_page = 0;
$this->limit = ($_GET['ipp'] == 'All') ? "":" LIMIT $this->low,$this->items_per_page";
}
function display_items_per_page()
{
$items = '';
if(!isset($_GET[ipp])) $this->items_per_page = $this->default_ipp;
foreach($this->ipp_array as $ipp_opt) $items .= ($ipp_opt == $this->items_per_page) ? "<option selected value=\\"$ipp_opt\\">$ipp_opt</option>\
":"<option value=\\"$ipp_opt\\">$ipp_opt</option>\
";
return "<span class=\\"paginate\\">Items per page:</span><select class=\\"paginate\\" onchange=\\"window.location='$_SERVER[PHP_SELF]?page=1&ipp='+this[this.selectedIndex].value+'$this->querystring';return false\\">$items</select>\
";
}
function display_jump_menu()
{
$option = '';
for($i=1;$i<=$this->num_pages;$i++)
{
$option .= ($i==$this->current_page) ? "<option value=\\"$i\\" selected>$i</option>\
":"<option value=\\"$i\\">$i</option>\
";
}
return "<span class=\\"paginate\\">Page:</span><select class=\\"paginate\\" onchange=\\"window.location='$_SERVER[PHP_SELF]?page='+this[this.selectedIndex].value+'&ipp=$this->items_per_page$this->querystring';return false\\">$option</select>\
";
}
function display_pages()
{
return $this->return;
}
}
The creator of the script specified that the query should follow this format: SELECT title FROM articles WHERE title != ‘’ ORDER BY title ASC $pages->limit with special reference to $pages->limit; because its needed by the Paginator class to function.
Here is my challenge, the method that gets the list of asset to be paginated uses this code in my asset manager class:
<?php
public static function getAssetList( $numRows=100000000, $order="productName ASC" ) {
$conn = DatabaseManager::getConnection();
$sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(PurchaseDate) AS purchaseDate FROM table_asset
ORDER BY " . mysql_escape_string($order) . " LIMIT :numRows";
$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->execute();
$list = array();
while ( $row = $st->fetch() ) {
$asset = new AssetManager( $row );
$list[] = $asset;
}
// Now get the total number of articles that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
}
?>
The challenge is how to incorporate $pages->limit into the sql query that get the result I need from the databse. How do i reconcile this? basically replacing $pages->limit with LIMIT :numRows as shown above. I don't know how to get it done because when I used $pages, there was fatal error telling me I was using non object variable.
thanks!