Trouble making Pagination Work with WHERE Clause

[phpdoodler]

Hi:

I'm in my infancy of working with php. I've have tried multiple php scripts that i found for pagination with my SQL query without success. All of them succeed in displaying the navigation and providing the count of the number of pages there should be; however, all the scripts I've tried display the records all on one page. I believe there is a conflict with the WHERE clause, but it's a necessity and I cannot eliminate it. Here is my latest attempt. I am hoping someone will assist with a solution. I apologize in advance for the length.

My php script for my query:

<?php 
$image_dir = '/images/inventory/';

$pl = trim($_GET['pl']);
$ph = trim($_GET['ph']);

if(empty($ph)) 
    $ph = 999999; 
else 
    $ph = (int)$ph; //make integer 

if(empty($pl)) 
    $pl = 0; 
else 
    $pl = (int)$pl; //make integer

//echo "pl: $pl <br/>";
//echo "ph: $ph <br/>";


$new_pre = $_GET['new_pre'];

//echo "new_pre: $new_pre <br/>";


$hull_material = $_GET['hull_material'];

//echo "hull_material: $hull_material <br/>";


$make = $_GET['make'];

$callforprice = $_GET['callforprice'];

//echo "make: $make <br/>";

//$where_condition = "WHERE condition = '$new' 
//AND condition = '$pre-owned'"; 


switch ($new_pre) {
    case "b":
        $where_new_pre = "AND new_pre LIKE '%'";
        break;
    case "n":
        $where_new_pre = "AND new_pre = 'new'"; 
        break;
    case "p":
        $where_new_pre = "AND new_pre = 'pre-owned'"; 
        break;
}

switch ($hull_material) {
    case "a":
		$where_hull_material = "AND hull_material = 'aluminum'";
        break;
    case "f":
        $where_hull_material = "AND hull_material = 'fiberglass'"; 
        break;
    case "b":
        $where_hull_material = "AND hull_material LIKE '%'"; 
        break;
}

switch ($make) {
    case "any":
        $where_make = "AND make LIKE '%'";
        break;
    case "contender":
        $where_make = "AND make = 'contender'"; 
        break;
    case "century":
        $where_make = "AND make = 'century'"; 
        break;
	case "hydra-sports":
        $where_make = "AND make = 'hydra-sports'"; 
        break;
    case "alweld":
        $where_make = "AND make = 'alweld'"; 
        break;
	case "hewes":
        $where_make = "AND make = 'hewes'"; 
        break;
    case "cobia":
        $where_make = "AND make = 'cobia'"; 
        break;
	case "maverick":
        $where_make = "AND make = 'maverick'"; 
        break;
    case "pathfinder":
        $where_make = "AND make = 'pathfinder'"; 
        break;
	case "twin vee":
        $where_make = "AND make = 'twin vee'"; 
        break;
}

$where_price = "WHERE price > $pl 
	AND price < $ph"; 	

$where = " $where_price $where_new_pre $where_hull_material $where_make "; 


$simple = TRUE;
if ($pl <> 0) $simple = FALSE;
if ($make <> '') $simple = FALSE;
if ($new_pre <> '') $simple = FALSE;
if ($hull_material <> '') $simple = FALSE;


$sql_simple = "SELECT * FROM boats ORDER BY price DESC";

$sql_search = "SELECT * FROM boats 
	$where 
	ORDER BY price DESC";

if ($simple) $sql = $sql_simple;
	else $sql = $sql_search;

//echo "query: $query <br/>";

?>

Here's my display:

<?php 
// Connects to your Database 
require_once('inc/dbstuff.php');
include('ps_pagination.php');
$rs = mysql_query($sql) 
or die(mysql_error());
Echo "<table width=400 class=thrColLiqHdr>"; 
while($row = mysql_fetch_array($rs))
{ 
Echo "<tr>";
Echo "<th>".$row['year'] . ' ' . $row['make'] . ' ' . $row['model'] . "</th>";
Echo "<th></th>";  

Echo "<th></th>";  

Echo "<th></th>"; 
Echo "<th>". (empty($row['price'])? $row['callforprice'] : '$' . $row['price']) . "</th></tr>\n"; 

Echo "<td><img src=\"$image_dir{$row['pix']}\" width=\"132\" height=\"99\" /></td>"; 
Echo"<td><strong>Location:</strong> ".$row['location'] . "</td> ";
Echo"<td><strong>Condition:</strong> ".$row['new_pre'] . "</td> ";
Echo"<td><strong>Engine:</strong> ".$row['engine_make'] . "</td> "; 
Echo"<td>".'<form id="search-form" action="" method="get">' 
          .'<input name="boat_details" type="submit" value="Details" />' 
          ."</form></td></tr>\n"; 
} 
Echo "</table>";

/*
 * Create a PS_Pagination object
 * 
 * $conn = MySQL connection object
 * $sql = SQl Query to paginate
 * 10 = Number of rows per page
 * 5 = Number of links
 * "param1=valu1&param2=value2" = You can append your own parameters to paginations links
 */
$pager = new PS_Pagination($conn, $sql, 10, 5);

/*
 * Enable debugging if you want o view query errors
*/
$pager->setDebug(true);

/*
 * The paginate() function returns a mysql result set
 * or false if no rows are returned by the query
*/
$rs = $pager->paginate();
if(!$rs) die(mysql_error());

//Display the full navigation in one go
echo $pager->renderFullNav();

echo "<br />\n";

/*
 * Or you can display the individual links for more
 * control over HTML rendering.
 * 
*/

//Display the link to first page: First
//echo $pager->renderFirst();

//Display the link to previous page: <<
//echo $pager->renderPrev();

/*
 * Display page links: 1 2 3
 * $prefix = Will be prepended to the page link (optional)
 * $suffix = Will be appended to the page link (optional)
 * 
*/
//echo $pager->renderNav('<span>', '</span>');

//Display the link to next page: >>
//echo $pager->renderNext();

//Display the link to last page: Last
//echo $pager->renderLast();

?>  

[phpdoodler]

Here's the paginator class (not my own script, trying to adapt to accomplish the pagination):

<?php
/**
 * PHPSense Pagination Class
 *
 * PHP tutorials and scripts
 *
 * @package		PHPSense
 * @author		Jatinder Singh Thind
 * @copyright	Copyright (c) 2006, Jatinder Singh Thind
 * @link		http://www.phpsense.com
 */

// ------------------------------------------------------------------------


class PS_Pagination {
	var $php_self;
	var $rows_per_page = 10; //Number of records to display per page
	var $total_rows = 100; //Total number of rows returned by the query
	var $links_per_page = 5; //Number of links to display per page
	var $append = ""; //Paremeters to append to pagination links
	var $sql = "";
	var $debug = false;
	var $conn = false;
	var $page = 1;
	var $max_pages = 0;
	var $offset = 0;

/**
 * Constructor
 *
 * @param resource $connection Mysql connection link
 * @param string $sql SQL query to paginate. Example : SELECT * FROM users
 * @param integer $rows_per_page Number of records to display per page. Defaults to 10
 * @param integer $links_per_page Number of links to display per page. Defaults to 5
 * @param string $append Parameters to be appended to pagination links 
 */

function PS_Pagination($connection, $sql, $rows_per_page = 10, $links_per_page = 5, $append = "") {
	$this->conn = $connection;
	$this->sql = $sql;
	$this->rows_per_page = (int)$rows_per_page;
	if (intval($links_per_page ) > 0) {
		$this->links_per_page = (int)$links_per_page;
	} else {
		$this->links_per_page = 5;
	}
	$this->append = $append;
	$this->php_self = htmlspecialchars($_SERVER['PHP_SELF'] );
	if (isset($_GET['page'] )) {
		$this->page = intval($_GET['page'] );
	}
}

/**
 * Executes the SQL query and initializes internal variables
 *
 * @access public
 * @return resource
 */
function paginate() {
	//Check for valid mysql connection
	if (! $this->conn || ! is_resource($this->conn )) {
		if ($this->debug)
			echo "MySQL connection missing<br />";
		return false;
	}

	//Find total number of rows
	$all_rs = @mysql_query($this->sql );
	if (! $all_rs) {
		if ($this->debug)
			echo "SQL query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
		return false;
	}
	$this->total_rows = mysql_num_rows($all_rs );
	@mysql_close($all_rs );

	//Return FALSE if no rows found
	if ($this->total_rows == 0) {
		if ($this->debug)
			echo "Query returned zero rows.";
		return FALSE;
	}

	//Max number of pages
	$this->max_pages = ceil($this->total_rows / $this->rows_per_page );
	if ($this->links_per_page > $this->max_pages) {
		$this->links_per_page = $this->max_pages;
	}

	//Check the page value just in case someone is trying to input an aribitrary value
	if ($this->page > $this->max_pages || $this->page <= 0) {
		$this->page = 1;
	}

	//Calculate Offset
	$this->offset = $this->rows_per_page * ($this->page - 1);

	//Fetch the required result set
	$rs = @mysql_query($this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}" );
	if (! $rs) {
		if ($this->debug)
			echo "Pagination query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
		return false;
	}
	return $rs;
}

/**
 * Display the link to the first page
 *
 * @access public
 * @param string $tag Text string to be displayed as the link. Defaults to 'First'
 * @return string
 */
function renderFirst($tag = 'First') {
	if ($this->total_rows == 0)
		return FALSE;

	if ($this->page == 1) {
		return "$tag ";
	} else {
		return '<a href="' . $this->php_self . '?page=1&' . $this->append . '">' . $tag . '</a> ';
	}
}

/**
 * Display the link to the last page
 *
 * @access public
 * @param string $tag Text string to be displayed as the link. Defaults to 'Last'
 * @return string
 */
function renderLast($tag = 'Last') {
	if ($this->total_rows == 0)
		return FALSE;

	if ($this->page == $this->max_pages) {
		return $tag;
	} else {
		return ' <a href="' . $this->php_self . '?page=' . $this->max_pages . '&' . $this->append . '">' . $tag . '</a>';
	}
}

/**
 * Display the next link
 *
 * @access public
 * @param string $tag Text string to be displayed as the link. Defaults to '>>'
 * @return string
 */
function renderNext($tag = '&gt;&gt;') {
	if ($this->total_rows == 0)
		return FALSE;

	if ($this->page < $this->max_pages) {
		return '<a href="' . $this->php_self . '?page=' . ($this->page + 1) . '&' . $this->append . '">' . $tag . '</a>';
	} else {
		return $tag;
	}
}

/**
 * Display the previous link
 *
 * @access public
 * @param string $tag Text string to be displayed as the link. Defaults to '<<'
 * @return string
 */
function renderPrev($tag = '&lt;&lt;') {
	if ($this->total_rows == 0)
		return FALSE;

	if ($this->page > 1) {
		return ' <a href="' . $this->php_self . '?page=' . ($this->page - 1) . '&' . $this->append . '">' . $tag . '</a>';
	} else {
		return " $tag";
	}
}

/**
 * Display the page links
 *
 * @access public
 * @return string
 */
function renderNav($prefix = '<span class="page_link">', $suffix = '</span>') {
	if ($this->total_rows == 0)
		return FALSE;

	$batch = ceil($this->page / $this->links_per_page );
	$end = $batch * $this->links_per_page;
	if ($end == $this->page) {
		//$end = $end + $this->links_per_page - 1;
	//$end = $end + ceil($this->links_per_page/2);
	}
	if ($end > $this->max_pages) {
		$end = $this->max_pages;
	}
	$start = $end - $this->links_per_page + 1;
	$links = '';

	for($i = $start; $i <= $end; $i ++) {
		if ($i == $this->page) {
			$links .= $prefix . " $i " . $suffix;
		} else {
			$links .= ' ' . $prefix . '<a href="' . $this->php_self . '?page=' . $i . '&' . $this->append . '">' . $i . '</a>' . $suffix . ' ';
		}
	}

	return $links;
}

/**
 * Display full pagination navigation
 *
 * @access public
 * @return string
 */
function renderFullNav() {
	return $this->renderFirst() . '&nbsp;' . $this->renderPrev() . '&nbsp;' . $this->renderNav() . '&nbsp;' . $this->renderNext() . '&nbsp;' . $this->renderLast();
}

/**
 * Set debug mode
 *
 * @access public
 * @param bool $debug Set to TRUE to enable debug messages
 * @return void
 */
function setDebug($debug) {
	$this->debug = $debug;
}
}
?>

This is about the third script I'm trying.

Thanks!

[dagon]

you need to use the classes append method to add the query additional clauses to the url, that you then pick up on subsequent pages.

[phpdoodler]

Hi,

Thank you for your response. Do you think you could provide some specifics about how to do what you've suggested? I noticed in the script for the paginator class that there are two variables, append and sql that I did not have completed. Is this where you're suggesting I accomplish the append? If so, how do I do this?

Thanks!

[dagon]

you use the apend method to pass the extra values you need for the query

[phpdoodler]

Can you explain how to do this? I don't know. I'm an amateur.

[phpdoodler]

Anyone?