pagination and array sort problem

[samusk]

I am seeing a problem with my code when pagination was implemented. The code retrieves all the records from the database and sorts them with an algorithm that gets the zipcode from your ip address and finds the distance from you to the zip code in each record. Works great without pagination. However, I noticed the algorithm is restarting each time I go to a new page with pagination. I need to see if there is a quick solve of this.

 

The records are put into a 2d array and sorted by distance that is the closest to you. I need to find a solution to this. Any help would be great. Here is my source if anyone wants to look at it for better reference:

 

function getPagerData($numHits, $limit, $page) 
{ 
	$numHits  = (int) $numHits; 
	$limit    = max((int) $limit, 1); 
	$page     = (int) $page; 
	 $numPages = ceil($numHits / $limit); 
	$page = max($page, 1); 
	$page = min($page, $numPages); 
	$offset = ($page - 1) * $limit; 
	$ret = new stdClass; 
	$ret->offset   = $offset; 
	 $ret->limit    = $limit; 
	 $ret->numPages = $numPages; 

	$ret->page     = $page; 
	return $ret; 

} 

$z = new zipcode_class;
//$num = mysql_num_rows($result);
// get page no from user to move user defined page    
    	$page = $_GET['page'];  
// no of elements per page
$limit = 12;

// simple query to get total no of entries
$result = mysql_query("select count(*) from horses");
$total = mysql_result($result, 0, 0);
// work out the pager value
$pager = getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit = $pager->limit;
$page = $pager->page;

// use the pager Values to fetch data 
$query = "SELECT * FROM horses LIMIT $offset,$limit";
//print "<pre>$query</pre>";
$result = mysql_query($query);

//$z = new zipcode_class;
//$query = "SELECT * FROM horses";
//$result = mysql_query($query);
$horse_ad = array();
while($row = mysql_fetch_array($result)){
	$id	   = $row['id'];
        $name	= $row['horse_name'];
	$price = $row['horse_price'];
	$city  = $row['horse_city'];
	$state = $row['horse_state'];
	$foal  = $row['foal_date'];
	$zipTwo   = $row['horse_zip'];
	$pic_thumb = $row['pic1'];
        $miles = $z->get_distance($zipOne, $zipTwo);

       $horse_ad[] = array("id" => $id, "name" => $name, "price" => $price, "city" => $city, "state" => $state, "foal" => $foal, "zip" => $zipTwo, "pic" => $pic_thumb ,"miles" => $miles);
}
echo "<table border='0' cellpadding='3'>


	$count = count($horse_ad)+1;
        $horse_ad = sortMultiArray($horse_ad, 'miles');
	for($x=0;$x<sizeof($horse_ad);$x++){
		$id = $horse_ad[$x]['id'];
		$name =$horse_ad[$x]['name'];
		$price = $horse_ad[$x]['price'];
		$city = $horse_ad[$x]['city'];
		$state = $horse_ad[$x]['state'];
		$foal = $horse_ad[$x]['foal'];
	        $zip = $horse_ad[$x]['zip'];
		$pic = $horse_ad[$x]['pic'];
		$miles = $horse_ad[$x]['miles'];
		if($i % $columns == 0) {
			//if there is no remainder, we want to start a new row
			echo "<tr>";
		}

		echo "<td><table width='240' height='150' border='3' style='border-collapse:collapse' cellpadding='0' cellspacing='0'>";
		echo "<tr><td align='center' colspan='3'><a href='details.php?code=".$id."&type=horses&name=".$name."'><img width='200' height='200' border='0' src=".$pic."></a></td></tr>";
	        echo "<tr><td align='center' class='header'>Price</td><td align='center' class='header'>Location</td><td align='center' class='header'>Foal Date</td></tr>";
		echo "<tr><td class='data'>$" . number_format($price, 2)."</td><td class='data'>".$city.",".$state . "</td><td class='data'>".$foal . "</td></tr>";
		echo "<tr><td class='data' colspan='3'>Distance: ". round($miles) ." Miles </td></tr>";
		echo "</table></td>";
		if(($count % $columns) == ($columns - 1) || ($count + 1) == $num) {
			//if there is a remainder of 1, end the row
			//or if there is nothing left in our result set, end the row
			echo "</tr>";
		}
		$i++;
	}
	echo "</table>
	</td>
	</tr>
	<tr>
		<td align='center'>";

		// use $result here to output page content 

		// output paging system (could also do it before we output the page content) 

		if ($page == 1) // this is the first page - there is no previous page 
			; 
			else            // not the first page, link to the previous page 
			echo '<a href=index.php?type=horses&page=' . ($page - 1) . '> << Previous ||</a>'; 
			for ($i = 1; $i <= $pager->numPages; $i++) { 
				echo ' | '; 
				if ($i == $pager->page) 
						echo 'Page '.$i; 
				else 
					echo "<a href=index.php?type=horses&page=".$i.">Page ".$i."</a>"; 
																	} 
if ($page == $pager->numPages) // this is the last page - there is no next page 
																		; 
else            // not the last page, link to the next page 
echo "<a href=index.php?page=" . ($page + 1) . "> || Next >></a>";  

echo "</td></tr>";

 

Here is the function that sorts the array

function sortMultiArray($array, $index, $order='asc', $natsort=FALSE, $case_sensitive=FALSE){ 
        if(is_array($array) && count($array)>0)  
        { 
           foreach(array_keys($array) as $key)  
               $temp[$key]=$array[$key][$index]; 
               if(!$natsort)  
                   ($order=='asc')? asort($temp) : arsort($temp); 
              else  
              { 
                 ($case_sensitive)? natsort($temp) : natcasesort($temp); 
                 if($order!='asc')  
                     $temp=array_reverse($temp,TRUE); 
           } 
           foreach(array_keys($temp) as $key)  
               (is_numeric($key))? $sorted[]=$array[$key] : $sorted[$key]=$array[$key]; 
           return $sorted; 
      } 
      return $array; 
    }

[rameshfaj]

for pagination u can use some predefined and tested components that u can find in this site also and for sorting the array u have the PHP in built functions.

[samusk]

You misunderstand. The pagination screws up the sort. Instead of sorting the entire recordset, it only sorts up to the limit variable. Then on the next page it starts over. For example, an OH zip code is not as far as a CA zipcode, but the pagination makes it sort the CA before the OH one. Totally not what it was supposed to do.