...
PDA

Pagination Functions

Phil Jackson
05-08-2010, 11:59 AM
func.php:


<?php
function pag_get_data($CON, $table_name, $col_array, $result_amount, $col_order_by, $where_clause, $get_page){
$col_fetch = implode("`, `", $col_array);
if( $get_page === false || $get_page == 1 ) {
$query = "SELECT `" . $col_fetch . "` FROM `" . $table_name . "` " . $where_clause . " ORDER BY `" . $col_order_by . "` LIMIT " . $result_amount;
$query = mysql_query( $query, $CON ) or die( "_error_ on line: " . __LINE__ . " of " . __FILE__ . mysql_error() );
}else{
$offset = (( $result_amount * $get_page ) - $result_amount) - 1;
$query = "SELECT `" . $col_fetch . "` FROM `" . $table_name . "` " . $where_clause . " ORDER BY `" . $col_order_by . "` LIMIT " . $result_amount . " OFFSET " . $offset;
$query = mysql_query( $query, $CON ) or die( "_error_ on line: " . __LINE__ . " of " . __FILE__ . mysql_error() );
}
if( mysql_num_rows( $query ) != 0 ) {
while( $rows = mysql_fetch_array( $query ) ) {
foreach( $col_array as $col_name ) {
$results_array[$col_name][] = $rows[$col_name];
}
}
return $results_array;
}else{
return false;
}
}

function page_get_links($CON, $table_name, $count_col, $result_amount, $where_clause, $get_page){
$query = "SELECT COUNT(`" . $count_col . "`) FROM `" . $table_name . "` " . $where_clause;
$query = mysql_query( $query, $CON ) or die( "_error_ on line: " . __LINE__ . " of " . __FILE__ . mysql_error() );
$pg_amount = ceil( mysql_result($query, 0) / $result_amount);
if( isset( $_GET['next'] ) ) {
$step = floor( $get_page / 10 );
$step_ = $_GET['next'];
}else{
$step = 0;
$step_ = 0;
}
if($get_page === false ) { $get_page = 1; }
$str = '';

for($i = $step_ . 0; $i < $pg_amount && $i < (($step_ + 1) * 10); $i++){
if( isset( $_GET['next'] ) ){
if(($i+1) == $get_page) {
$str .= '<a href="?page=' . ($i+1) . '&next=' . (($step_ + 1) - 1) . '" class="page-link page-selected">' . ($i+1) . '</a>';
}else{
$str .= '<a href="?page=' . ($i+1) . '&next=' . (($step_ + 1) - 1) . '" class="page-link">' . ($i+1) . '</a>';
}
}else{
if(($i+1) == $get_page) {
$str .= '<a href="?page=' . ($i+1) . '&next=0" class="page-link page-selected">' . ($i+1) . '</a>';
}else{
$str .= '<a href="?page=' . ($i+1) . '&next=0" class="page-link">' . ($i+1) . '</a>';
}
}
}
if( $step_ > 0 )
$str = '<a href="?page=' . ($i-10) . '&next=' . ($step_ - 1) . '" id="prev-button"> prev </a>' . $str;
if( ( $i%10 ) == 0 )
$str .= '<a href="?page=' . ($i+1) . '&next=' . ($step_ + 1) . '" id="next-button"> next </a>';
return $str;
}
?>


index.php


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pagination Test</title>
<style type="text/css">
body { text-align:center; }
a.page-link, a#prev-button, a#next-button, a.page-link:visited, a#prev-button:visited, a#next-button:visited { background-color:#EEF2F9; font-size:12px; padding:2px; margin:2px; display:inline-block; border:1px solid #9CAFCF; color:#069; text-decoration:none; text-align:center; width:30px; }
a#prev-button, a#prev-button:visited { width: 40px; background-color:#DBE3EE; }
a#next-button, a#next-button:visited { width: 40px; background-color:#DBE3EE; }
a.page-link:hover, a#prev-button:hover, a#next-button:hover, a.page-selected, a.page-selected:visited { background:#069; color:#FFF; }
table{ margin:0 auto; }
</style>
</head>
<body>
<?php

define( "DB_USERNAME", "******" );
define( "DB_PASSWORD", "******" );
define( "DB_SERVER", "*********" );
define( "DB_NAME", "*********" );

$CON = mysql_connect( DB_SERVER, DB_USERNAME, DB_PASSWORD ) or die( "_error_ on line: " . __LINE__ . " of " . __FILE__ . mysql_error() );
$DB = mysql_select_db( DB_NAME, $CON );

include("func.php");
if( isset( $_GET['page'] ) ) {
$get_page = mysql_real_escape_string($_GET['page']);
}else{
$get_page = false;
}

# ENTER THE COLUMNS FROM YOUR TABLE THAT WILL BE USED IN THE QUERY
$col_array = array('DATE', 'STAND_COST', 'LOCATION', 'EVENT_NAME', 'ID');
# ADD A WHERE CLAUSE IF NEEDED
# $where_clause = "WHERE DATE >= '" . time() . "'";
$where_clause = "";
$table_name = 'nnn_event_listings';
$display_amount = 10;
$order_by = 'ID';
# FINSISH

$data_result = pag_get_data( $CON, $table_name, $col_array, $display_amount, $order_by, $where_clause, $get_page );
$link_result = page_get_links($CON, $table_name, $order_by, $display_amount, $where_clause, $get_page);

# BELOW IS AN EXAMPLE OF HOW TO USE THE DATA RETRIEVED

if( $data_result ) {
echo "<table>";
echo " <thead>";
echo " <th>Event Name</th><th>Stand Cost</th><th>Location</th><th>Date</th>";
echo " </thead>\n";
echo " <tbody>\n";

foreach( $data_result['EVENT_NAME'] as $key => $event ) {
echo '<tr><td>' . $event . '</td><td>' . $data_result['STAND_COST'][$key] . '</td><td>' . $data_result['LOCATION'][$key] . '</td><td>' . date("d/m/Y", $data_result['DATE'][$key]) . '</td></tr>';
}

echo " </tbody>\n";
echo "</table>\n";

echo $link_result;
}

# FINISH

?>
</body>
</html>


demo image:

http://www.actwebdesigns.co.uk/pag_image.jpg
Phil Jackson
05-08-2010, 12:09 PM
simply edit the following:


# ENTER THE COLUMNS FROM YOUR TABLE THAT WILL BE USED IN THE QUERY
$col_array = array('DATE', 'STAND_COST', 'LOCATION', 'EVENT_NAME', 'ID');
# ADD A WHERE CLAUSE IF NEEDED
# $where_clause = "WHERE DATE >= '" . time() . "'";
$where_clause = "";
$table_name = 'nnn_event_listings';
$display_amount = 10;
$order_by = 'ID';
# FINSISH


And Also edit the example given:


# BELOW IS AN EXAMPLE OF HOW TO USE THE DATA RETRIEVED

if( $data_result ) {
echo "<table>";
echo " <thead>";
echo " <th>Event Name</th><th>Stand Cost</th><th>Location</th><th>Date</th>";
echo " </thead>\n";
echo " <tbody>\n";

foreach( $data_result['EVENT_NAME'] as $key => $event ) {
echo '<tr><td>' . $event . '</td><td>' . $data_result['STAND_COST'][$key] . '</td><td>' . $data_result['LOCATION'][$key] . '</td><td>' . date("d/m/Y", $data_result['DATE'][$key]) . '</td></tr>';
}

echo " </tbody>\n";
echo "</table>\n";

echo $link_result;
}

# FINISH
Phil Jackson
05-09-2010, 08:08 AM
I have also just finished an adaptation of the code above. It allows overide of the SELECT items to make more adaptable.

In this example I have applied a search function and the ability to narrow results via drop down menu:

Index.php snip
( $get will need to be replaced with $_GET )

<?php

if( isset( $get['page'] ) ) {
$get_page = mysql_real_escape_string($get['page']);
}else{
$get_page = false;
}

# ENTER THE COLUMNS FROM YOUR TABLE THAT WILL BE USED IN THE QUERY
$col_array = array('DATE', 'STAND_COST', 'LOCATION', 'EVENT_NAME', 'ID', 'TEMP_ADDRESS');
$table_name = 'nnn_event_listings';
$display_amount = 10;
$order_by = 'DATE';
$return_dir = '?navigation=events';
if( isset($get['plus-month']) && preg_match("#^[2-6]$#is", $get['plus-month']) ) {
$max = $get['plus-month'];
}else{
$max = 2;
}
$max_date = strtotime(date("d-m-Y") . "+ $max month");
$min_date = strtotime(date("d-m-Y") . "- 1 month");
if( isset( $get['event-search'] ) ){
# ADD A WHERE CLAUSE IF NEEDED
$where_clause = "WHERE `DATE` <= '$max_date' AND `DATE` >= '$min_date' HAVING `DETAILS` LIKE '%" . $get['event-search'] . "%'";
# ADD A SELECT BYPAS IF NEEDED
$select_bypas = "ID`, `STAND_COST`, `LOCATION`, `EVENT_NAME`, `TEMP_ADDRESS`, `DATE`, CONCAT( LOWER(LOCATION), LOWER(EVENT_NAME) ) AS `DETAILS";
$search_phrase = $get['event-search'];
}else{
# ADD A WHERE CLAUSE IF NEEDED
$where_clause = "WHERE `DATE` <= '$max_date' AND `DATE` >= '$min_date'";
# ADD A SELECT BYPAS IF NEEDED
$select_bypas = '';
$search_phrase = '';
}
# FINSISH
$data_result = pag_get_data( $CON, $table_name, $col_array, $display_amount, $order_by, $where_clause, $select_bypas, $get_page );
$link_result = page_get_links($CON, $table_name, $order_by, $display_amount, $where_clause, $select_bypas, $get_page, $search_phrase, $max, $data_result['count'], $return_dir );


if( $data_result ) {
foreach( $data_result['results']['EVENT_NAME'] as $key => $event ) {
$event_name = $event;
$location = $data_result['results']['LOCATION'][$key];
$temp_address = $data_result['results']['TEMP_ADDRESS'][$key];
$date = date("d/m/Y", $data_result['results']['DATE'][$key]);
echo "<tr><td>$event_name</td><td>$location</td><td>$date</td><td><a class=\"ev-details\" href=\"http://nnn-profile.co.uk/?event=$temp_address\" target=\"_blank\">more info...</a></td></tr>";
}
}else{

}

?>
</tbody>
</table>

<?php
if( $data_result ) {
echo $link_result;
}
?>


func.php:


function pag_get_data($CON, $table_name, $col_array, $result_amount, $col_order_by, $where_clause, $select_bypas, $get_page){
if( ! empty( $select_bypas ) ) {
$col_fetch = $select_bypas;
}else{
$col_fetch = implode("`, `", $col_array);
}
$count_query = "SELECT `" . $col_fetch . "` FROM `" . $table_name . "` " . $where_clause . " ORDER BY `" . $col_order_by;
if( $get_page === false || $get_page == 1 ) {
$query = "SELECT `" . $col_fetch . "` FROM `" . $table_name . "` " . $where_clause . " ORDER BY `" . $col_order_by . "` LIMIT " . $result_amount;
}else{
$offset = (( $result_amount * $get_page ) - $result_amount) - 1;
$query = "SELECT `" . $col_fetch . "` FROM `" . $table_name . "` " . $where_clause . " ORDER BY `" . $col_order_by . "` LIMIT " . $result_amount . " OFFSET " . $offset;
}
$count_query = mysql_query( $count_query, $CON ) or die( "_error_ on line: " . __LINE__ . " of " . __FILE__ . mysql_error() );
$num = mysql_num_rows( $count_query );
$query = mysql_query( $query, $CON ) or die( "_error_ on line: " . __LINE__ . " of " . __FILE__ . mysql_error() );
if( mysql_num_rows( $query ) != 0 ) {
while( $rows = mysql_fetch_array( $query ) ) {
foreach( $col_array as $col_name ) {
$results_array[$col_name][] = $rows[$col_name];
}
}
return array('results' => $results_array, 'count' => $num);
}else{
return false;
}
}
function page_get_links($CON, $table_name, $count_col, $result_amount, $where_clause, $select_bypas, $get_page, $search_phrase, $max, $num, $return_dir){
if( ! empty( $search_phrase ) )
$search_phrase = "&event-search=$search_phrase";
else
$search_phrase = "";
if( ! empty( $max ) )
$max = "&plus-month=$max";
else
$max = "";
$pg_amount = ceil( $num / $result_amount);
if( isset( $_GET['next'] ) ) {
$step = floor( $get_page / 10 );
$step_ = $_GET['next'];
}else{
$step = 0;
$step_ = 0;
}
if($get_page === false ) { $get_page = 1; }
$str = '';

for($i = $step_ . 0; $i < $pg_amount && $i < (($step_ + 1) * 10); $i++){
if( isset( $_GET['next'] ) ){
if(($i+1) == $get_page) {
$str .= '<a href="' . $return_dir . '&page=' . ($i+1) . '&next=' . (($step_ + 1) - 1) . $search_phrase . $max . '" class="page-link page-selected">' . ($i+1) . '</a>';
}else{
$str .= '<a href="' . $return_dir . '&page=' . ($i+1) . '&next=' . (($step_ + 1) - 1) . $search_phrase . $max . '" class="page-link">' . ($i+1) . '</a>';
}
}else{
if(($i+1) == $get_page) {
$str .= '<a href="' . $return_dir . '&page=' . ($i+1) . '&next=0' . $search_phrase . $max . '" class="page-link page-selected">' . ($i+1) . '</a>';
}else{
$str .= '<a href="' . $return_dir . '&page=' . ($i+1) . '&next=0' . $search_phrase . $max . '" class="page-link">' . ($i+1) . '</a>';
}
}
}
if( $step_ > 0 )
$str = '<a href="' . $return_dir . '&page=' . ($i-10) . '&next=' . ($step_ - 1) . $search_phrase . $max . '" id="prev-button"> prev </a>' . $str;
if( ( $i%10 ) == 0 )
$str .= '<a href="' . $return_dir . '&page=' . ($i+1) . '&next=' . ($step_ + 1) . $search_phrase . $max . '" id="next-button"> next </a>';
return $str;
}



screenshot:

http://www.actwebdesigns.co.uk/pag_demo2.jpg

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum