[RESOLVED] Where to add pagination code

[hadoob024]

I've read several tutorials and a ton of posts, and the code for implementing pagination seems to be pretty similar (the methodology at least). Anyway, I see how the code works, but I'm not sure how to apply it to my site in particular. I have a page "search.php", that has all the form information and creates sessions and passes this information as hidden tokens (to prevent against abuse of my forms). It passes the info to "searchresults.php". "searchresults.php" has the following structure:

1) check SESSION variables (to prevent against CSR, session hijacking, and session fixation)
2) initialize my variables which will store the info passed from the form
3) check/screen info passed through POST and set my variables equal to these values
4) validate information
5) connect to db
6) create and format results of SQL search string based on form information and by using SQL ORDER command
7) query db
8) if results found, display them, if not, then display generic message
9) free resultset
10) clean up SESSION, close tables, end script

So this layout works fine right now, but I want to add pagination and I guess my question is where/how to implement the pagination code given the layout of my "searchresults.php" page? Will all the SESSION stuff that I have mess this up? Will my form variables keep getting reset in between pages? Will the query be able to be passed ok?

[devinemke]

a common approach is to store the search criteria data in the session, do a COUNT() query to get the total number of rows that match, then use the LIMIT clause in your SELECT query to only show a certain number of rows (like say 25 at a time). then pass the offset number for the LIMIT clause to the "prev" and "next" links via a GET variable. with some simple math with the count and the passed offset you can calculate whether you are at the beginning or end of the total result set.

[hadoob024]

Interesting. I see what you're saying. But would I be able to implement what you're saying in the "searchresults.php" page that I have? Or would I have to show the first section of the search results in the "searchresults.php" page, and then link to another php script/page, to handle the rest of the results?

[devinemke]

hadoob024 wrote:

Interesting. I see what you're saying. But would I be able to implement what you're saying in the "searchresults.php" page that I have? Or would I have to show the first section of the search results in the "searchresults.php" page, and then link to another php script/page, to handle the rest of the results?

i personally try to consolidate related code into a single script. i use conditional logic to either output the search form or the search results (for whatever page of the result set). this makes it easier to maintain with shared access to common functions/variables.

[hadoob024]

That makes sense. I went the other route and split it up among 2 pages. Do you still implement stuff to counter CSRF, session hijacking, etc.? If so, how do you implement pagination among all of this?

[hadoob024]

I messed around with the code for a while and finally figured it out. For anyone that has their code split into 2 pages (form page and form processor page), here's my solution for anyone else in the same boat:

Form processor page:
1) First I create the SQL search string.
2) Then I run the query to determine the total number of results returned
3) I then save the search string, the number of results per page, and the total number of results returned by the query. I save them in temp variables for the current script and also $_SESSION variables to save the values for the next script.
4) I then append to my SQL search string a LIMIT value and hardcode in the LIMIT values from 0 to the number of results per page, and then run the query
5) Count and print the rows.
6) This is where you start incorporating the pagination code. I hardcode in the value for page. I set it equal to 1.

7) Print out the results from the first section
8) If the total results exceeds the results per page, print out links at the bottom to point to another page.

This comes out to:

//Test query the listings db with the SQL search string that we've created
//just to get the total number of records found (for pagination)
$searchresults = mysql_query($searchstring);
if (!$searchresults)
{
	$problemtext = 'Error searching listings db. \r\n '.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}
//count the number of rows in the result set
$totalnumrows = mysql_num_rows($searchresults);

//setting variables and SESSION variables to help with pagination
$_SESSION['sqlsearchstring'] = $searchstring;
$_SESSION['resultsperpage'] = 10;
$_SESSION['numberofrows'] = $totalnumrows;
$resultsperpage = 10;
$searchstring .= " limit 0,10";

//Query the listings db with the SQL search string that we've created
$searchresults = mysql_query($searchstring);
if (!$searchresults)
{
	$problemtext = 'Error searching listings db. \r\n '.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}


//count the number of rows in the result set
$numrows = mysql_num_rows($searchresults);

//if there are no records that fit the search criteria
if ($numrows == 0)
{
	echo '<table align="center" height="37"><tr><td><img src = "images/nosearchresults.jpg" alt="No Search Results" /></td></tr></table><br /><BR />';
}
//else records matched our search criteria, and a display preference other
//than "show all" was chosen
elseif (($displaypref == "10 results per page") || ($displaypref == "25 results per page"))
{
	//Since this will be the first page in our result set, we
	//set the $page variable equal to 1.
    $page = 1;

//We want to display the current range of listings being shown.
//This helps us determine how many listings are being shown on the
//current page.
if ($totalnumrows <= $resultsperpage)
	$endrange = $totalnumrows;
else
	$endrange = $resultsperpage;

echo '<table align="center" height="37"><tr><td><img src = "images/searchresults.jpg" alt="Search Results" /></td></tr></table><br />';
echo '<div align="left"><B> &nbsp Listings 1-'.$endrange.' of '.$totalnumrows.'</B></div>';

//call this script which loops through and prints out the results
include('/blah/blah/dbresults.php');

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($totalnumrows / $resultsperpage);

//Build page number hyperlinks for pagination
echo '<br /><div align="center">Result Pages:<br />';

//Build Previous Link
if($page > 1)
{
	$prev = ($page - 1);
	echo '<a href="pageresults.php?page='.$prev.'"><<Previous</a> ';
}

//Building page number links
for($i = 1; $i <= $total_pages; $i++)
{
	if(($page) == $i)
		echo "<B> [$i] </B>";
	else
		echo '<a href="pageresults.php?page='.$i.'">'.$i.'</a> ';
}

// Build Next Link
if($page < $total_pages)
{
	$next = ($page + 1);
	echo '<a href="pageresults.php?page='.$next.'">Next>></a> ';
}

echo '</div>';

I then call the file "pageresults.php" and this handles the pagination, including if someone clicks on page 1 to go back to the first section of the result set. In this file I call "pageresults.php" with the appropriate value for the $page $GET variable
Second page (used for pagination):

session_start();
if ((!isset($_SESSION['sqlsearchstring'])) || (!isset($_SESSION['resultsperpage'])) || (!isset($_SESSION['numberofrows'])))
	errorcheck(2, 'User attempted accessing "pageresults.php" without going through "searchlistings.php" or "pageresults.php".');

//to determine page #
if(!isset($_GET['page']))
	$page = 1;
else
	$page = $_GET['page'];

//Define the number of results per page.  This value is stored in
//a SESSION variable set by the form processing page.
$max_results = $_SESSION['resultsperpage'];

//Figure out the limit for the query based
//on the current page number.
$from = (($page * $max_results) - $max_results);


//connecting to database to continue with pagination
include('/blah/blah/dbconnect.php');


//Define the SQL search string that we're going to run.  This base
//value is stored in a SESSION variable set by the form processing
//page.  We then concatenate parts to define the values for the
//LIMIT part of the SQL search string.
$searchstring = $_SESSION['sqlsearchstring']." limit ".$from.",".$max_results;

//Query the listings db with the SQL search string that we've created
$searchresults = mysql_query($searchstring);
if (!$searchresults)
{
	$problemtext = 'Error searching listings db. \r\n '.mysql_error($connresult);
	trigger_error($problemtext, E_USER_ERROR);
}


//Determines the beginning part of the range being shown
$beginningrange = $from + 1;


//We want to display the current range of listings being shown.
//This helps us determine how many listings are being shown on the
//current page.
if (($from + $max_results) < $_SESSION['numberofrows'])
	$endrange = $from + $max_results;
else
	$endrange = $_SESSION['numberofrows'];


//continue with displaying results
echo '<table align="center" height="37"><tr><td><img src = "images/searchresults.jpg" alt="Search Results" /></td></tr></table><br />';
echo '<div align="left"><B> &nbsp Listings '.$beginningrange.'-'.$endrange.' of '.$_SESSION['numberofrows'].'</B></div>';


//call this script which loops through and prints out the results
include('/blah/blah/dbresults.php');


// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil(($_SESSION['numberofrows']) / $max_results);

//Build page number hyperlinks for pagination
echo '<br /><div align="center">Result Pages:<br />';

//Build Previous Link
if($page > 1)
{
	$prev = ($page - 1);
	echo '<a href="pageresults.php?page='.$prev.'">&lt&lt Previous</a> ';
}

//Building page number links
for($i = 1; $i <= $total_pages; $i++)
{
	if(($page) == $i)
		echo "<B> [$i] </B>";
	else
		echo '<a href="pageresults.php?page='.$i.'">'.$i.'</a> ';
}

// Build Next Link
if($page < $total_pages)
{
	$next = ($page + 1);
	echo '<a href="pageresults.php?page='.$next.'">Next >></a> ';
}

echo '</div>';

Hope this helps someone!