1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 9:51 am Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Paginating a complex search query

         

macuser

1:37 am on Aug 19, 2010 (gmt 0)

10+ Year Member



I am a newbie and I have been having a terrible time trying to paginate a search result on a page i am developing. The page uses a radius search API to determine search results from a mySQL database that are within a user defined radius of a user defined zipcode. I am using POST format to submit variables into the SQL search. I have used a number of scripts suggested by others on this forum and while the pagination scripts work to limit the search for the first page I cannot get them to work for "next" pages. The problem has got to be re-running the search and I tried using session variables but still have problems. The other problem with using session variables is that I have to first have the search form go to one page to convert the POST variables to session variables then I have to have the search run on a separate page, this seems inefficient. I have therefore tried to convert the POST variables to GET variables. The code I am using is as follows:


<?php require_once('Connections/connection.php'); ?>
<?php
if (!isset($_SESSION)) {
session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
// For security, start by assuming the visitor is NOT authorized.
$isValid = False;

// When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
// Therefore, we know that a user is NOT logged in if that Session variable is blank.
if (!empty($UserName)) {
// Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
// Parse the strings into arrays.
$arrUsers = Explode(",", $strUsers);
$arrGroups = Explode(",", $strGroups);
if (in_array($UserName, $arrUsers)) {
$isValid = true;
}
// Or, you may restrict access to only certain users based on their username.
if (in_array($UserGroup, $arrGroups)) {
$isValid = true;
}
if (($strUsers == "") && true) {
$isValid = true;
}
}
return $isValid;
}

$MM_restrictGoTo = "/names/loginerror.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {
$MM_qsChar = "?";
$MM_referrer = $_SERVER['PHP_SELF'];
if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)
$MM_referrer .= "?" . $QUERY_STRING;
$MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
header("Location: ". $MM_restrictGoTo);
exit;
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_connection, $connection);
$query_rslogin = "SELECT userid, username FROM users";
$rslogin = mysql_query($query_rslogin, $connection) or die(mysql_error());
$row_rslogin = mysql_fetch_assoc($rslogin);
$totalRows_rslogin = mysql_num_rows($rslogin);
?>
<?php require_once('Connections/connection.php');
require_once("BoundaryWizard.php");
require_once("DistanceWizard.php"); ?>
<?php

if(isset($_POST['userzipcode'])){
$zip = '&userzipcode=' . $_POST['userzipcode'];
} else {
$zip = '';
}

if(isset($_POST['distance'])){
$radius = '&distance=' . $_POST['distance'];
} else {
$radius = '';
}

if(isset($_POST['name'])){
$name = '&make=' . $_POST['name'];
} else {
$name = '';
}

if(isset($_POST['year'])){
$year = '&year=' . $_POST['year'];
} else {
$year = '';
}
// Simulate data entry. Normally, these two values
// will come from a user-submitted form.
$zip = (isset($_POST['userzipcode']))? $_POST['userzipcode']:$GET['userzipcode']; // our origin ZIP code (a string)
$radius = (isset($_POST['distance']))? $_POST['distance']:$GET['distance']; // the boundary's radius
$unit = Measurement::MILES; // our desired unit of measure
$name = (isset($_POST['name']))? $_POST['make']:$GET['name']; // "Measurement" is included through one of the wizard files
$year = (isset($_POST['year']))? $_POST['year']:$GET['year']; // we already included.
// the boundary's radius
$unit = Measurement::MILES; // our desired unit of measure
// we already included.
// we already included.



// Set up our query to see if the ZIP exists.
$query = "SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode='$zip' LIMIT 1";

// Run the query.
$result = @mysql_query($query);

if (!$result)
{
// Explain SQL error.
echo "<p><b>ERROR ></b> There was an error executing the database query to find ZIP coordinates:</p><p>".mysql_error()."</p>";
exit;
}
elseif (@mysql_num_rows($result) == 0)
{
// ZIP code was not found in database.
echo "<p><b>ERROR ></b> 0 results returned; does the ZIP code exist in the database?";
exit;
}
else
{
// ZIP code exists, now find the nearby zip codes.
$row = mysql_fetch_array($result);
$latitude = $row['Latitude'];
$longitude = $row['Longitude'];

// Construct the BoundaryWizard object.
// It will perform the calculation to get our boundary.
$boundaryWizard = new BoundaryWizard();

// Create the coordinate of the origin ZIP code.
$originCoord = new Coordinate($latitude, $longitude);

// Calculate.
$boundary = $boundaryWizard->CalculateBoundary($originCoord, $radius, $unit);

// Retrieve our bounds.
$northern = $boundary->North();
$southern = $boundary->South();
$eastern = $boundary->East();
$western = $boundary->West();

$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);

/* Max results per page */
$max_results = 5;

/* Calculate the offset */
$from = (($page * $max_results) - $max_results);

/* Query the db for total results. You need to edit the sql to fit your needs */
$result = mysql_query("SELECT DISTINCT names.nameid, names.userid, names.name, names.model, names.year, names.city, names.state, names.zipcode, names.description, names.trades, users.userid, users.username, zips.Latitude, zips.Longitude
FROM rsnames AS names
INNER JOIN users
ON names.userid = users.userid
INNER JOIN ZIPCodes AS zips
ON names.zipcode = zips.ZIPCode
WHERE Latitude BETWEEN $southern AND $northern
AND Longitude BETWEEN $western AND $eastern
AND zips.CityType='D'
AND Latitude != 0
AND Longitude != 0
AND names.year LIKE '$year'
AND names.make LIKE '$names'");

$total_results = mysql_num_rows($result);

$total_pages = ceil($total_results / $max_results);



$queryNearestZips = "SELECT DISTINCT names.nameid, names.userid, names.name, names.model, names.year, names.city, names.state, names.zipcode, names.description, names.trades, users.userid, users.username, zips.Latitude, zips.Longitude
FROM rsnames AS names
INNER JOIN users
ON names.userid = users.userid
INNER JOIN ZIPCodes AS zips
ON names.zipcode = zips.ZIPCode
WHERE Latitude BETWEEN $southern AND $northern
AND Longitude BETWEEN $western AND $eastern
AND zips.CityType='D'
AND Latitude != 0
AND Longitude != 0
AND names.year LIKE '$year'
AND names.make LIKE '$names'");
LIMIT $from, $max_results";

// Now do it!
$resultNearestZips = @mysql_query($queryNearestZips);

if (!$resultNearestZips)
{
// Explain the error...
echo "<p><b>ERROR ></b> There was an error executing the database query to find nearby stores by ZIP:</p><p>".mysql_error()."</p>";
exit;
}
else
{
// Now let's go through each ZIP code and find out how
// far they are from the origin coordinate and display it.
$distanceWizard = new DistanceWizard();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<link href="SpryAssets/MenuBarFormat.css" rel="stylesheet" type="text/css" />
<link href="CSS/tableformat.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
body {
background-color: #CCC;
}
-->
</style></head>

<body>
<h2 align="center"><img src="/names/names.gif" width="314" height="87" alt="names" />

<?php require_once('includes/menubar.php');
echo "<br /><br /><h2>Some Available People Near ".$zip."</h2>"; ?>
<h2 align="center">
</h2>
<table border="4" align="center" cellpadding="4" cellspacing="4">
<tr align="center">
<th><div align="center">
<h2>Year</h2>
</div></th>
<th><div align="center">
<h2>Name</h2>
</div></th>
<th><div align="center">
<h2>Model</h2>
</div></th>
<th><div align="center">
<h2>Description</h2>
</div></th>
<th><div align="center">
<h2>Trades</h2>
</div></th>
<th><div align="center">
<h2>User</h2>
</div></th>
<th><div align="center">
<h2>City</h2>
</div></th>
<th><div align="center">
<h2>State</h2>
</div></th>
<th><div align="center">
<h2>Zipcode</h2>
</div></th>
<th><div align="center">
<h2>Distance</h2>
</div></th>
</tr>
<p>
<?php
// Make output pretty and organized.


$dataset;

// Loop through each result
$i = 0;
while ($rowZIP = mysql_fetch_array($resultNearestZips))
{
// Get the relative ZIP code's coordinate.
$relativeCoord = new Coordinate($rowZIP['Latitude'], $rowZIP['Longitude']);

// Calculate the distance.
$distance = $distanceWizard->CalculateDistance(
$originCoord,
$relativeCoord,
$unit
);

// This next "if" statement is necessary because of the box/circle model.
// (See the documentation.) A boundary is a square but distances are calculated
// using a radius (on a circle). This circle fits within that square and so
// there are 'corners' that aren't quite within the desired distance.
if ($distance <= $radius)
{
// We want to sort this by distance ASC, so the closest cities are displayed
// first. Let's put the City & Zip & Distance values in an array for this example.
// Normally you will use whichever values you need and in the order that
// you need them.
$dataset[$i]['year'] = $rowZIP['year'];
$dataset[$i]['name'] = $rowZIP['make'];
$dataset[$i]['nameid'] = $rowZIP['carid'];
$dataset[$i]['model'] = $rowZIP['model'];
$dataset[$i]['description'] = $rowZIP['description'];
$dataset[$i]['trades'] = $rowZIP['trades'];
$dataset[$i]['username'] = $rowZIP['username'];
$dataset[$i]['city'] = $rowZIP['city'];
$dataset[$i]['state'] = $rowZIP['state'];
$dataset[$i]['zipcode'] = $rowZIP['zipcode'];
$dataset[$i]['Distance'] = $distance;
$i ++;
}

}

// Sorts a 2D array given a key from the 2nd dimension
// of the array in ascending order.
function sort_2d_array_asc(&$array, $innerkey)
{
$dim = array();
foreach ($array as $innerarray)
$dim[] = $innerarray[$innerkey];
array_multisort($dim, $array);
}

// Perform sort.
sort_2d_array_asc($dataset, "Distance");

// Display results.

foreach ($dataset as $resultset)
{
echo "<tr>\n\t<td>" . $resultset['year'] . "</td>\n";
echo "\t<td>" .$resultset['namee']."</td>\n";
echo "\t<td><a href=detail.php?carid={$resultset['nameid']}>".$resultset['model']."</a></td>\n";
echo "\t<td>" . $resultset['description'] . "</td>\n";
echo "\t<td>" . $resultset['trades'] . "</td>\n";
echo "\t<td>" . $resultset['username'] . "</td>\n";
echo "\t<td>" . $resultset['city'] . "</td>\n";
echo "\t<td>" . $resultset['state'] . "</td>\n";
echo "\t<td>" . $resultset['zipcode'] . "</td>\n";
echo "\t<td><b>" . round($resultset['Distance'], 2) . " miles</b></td>\n</tr>\n";
}

echo "</table>";
}
}

if(isset($_POST['userzipcode'])){
$zip = '&userzipcode=' . $_POST['userzipcode'];
} else {
$zip = '';
}

if(isset($_POST['distance'])){
$radius = '&distance=' . $_POST['distance'];
} else {
$radius = '';
}

if(isset($_POST['make'])){
$name = '&name=' . $_POST['name'];
} else {
$name = '';
}

if(isset($_POST['year'])){
$year = '&year=' . $_POST['year'];
} else {
$year = '';
}
echo "<div align=center>";


if($page > 1)
{
echo "&lt;&lt;<a href=searchresults.php?page='$prev'.'$zip'.'$radius'.'$name'.'$year'.>&nbsp;Previous</a> - ";
}

for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
echo "$i";
}
else
{
echo "<a href=searchresults.php?&page='.$i.'$zip'.'$radius'.'$name'.'$year'.> $i </a>";
}
}

if($page < $total_pages)
{
echo " - <a href=searchresults.php?page='$next.'$zip'.'$radius'.'$name'.'$year'.>Next</a>&nbsp;&gt;&gt;";
}
elseif ($page = $total_pages)
{
}

echo "</div>";
mysql_close($dbconn);
?>

</p>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($rslogin);
?>

coopster

8:33 pm on Dec 15, 2010 (gmt 0)

WebmasterWorld Administrator 10+ Year Member



Welcome to WebmasterWorld, macuser.

Did you resolve your pagination issues?

There are many ways to handle pagination and the complexity of the query really doesn't come into play. What does is exactly what you described, grabbing the previous result starting point and using that in the next invocation. Using a GET request variable is quite popular.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 9:51 am Apr 28, 2026