Jump to content


Photo

search_results pagination problem


  • Please log in to reply
No replies to this topic

#1 abandonship

abandonship
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 26 March 2006 - 03:52 AM

My search results page and pagination work fine for page 1, but subsequent pages break due to the fact that my SQL string is regenerated each time the page is refreshed (I lose the values in my query string). How can I maintain the same SQL query string that is generated on the first page hit??? Anyone want to take a stab at this???


//I took out some pieces from yesterday to simplify it for debugging
<?php
include('../includes/test_shared_functions.php');
include('includes/admin_functions.php');
//include('functions/strMaker.php');
///////////////////////////////////////////////////////////
/// CLASS PAGER /////
///////////////////////////////////////////////////////////
class Pager
{
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;
}
}

///////////////////////////////////////////////////////////
/// CREATE SCHOOLDIST SQL STRING APPEND /////
///////////////////////////////////////////////////////////

$searchparam_counter = 0;
$array = array(0);//set array variable instantiate array and initialize index sub 0 value of 0
echo "value of array at zero is:".$array[0];

/////// CREATE SCHOOLDIST SQL STRING APPEND
if ( $_GET['schooldist'] ){

$schooldist=$_GET['schooldist'];

//store sql string in $array
$array[$searchparam_counter] = " schooldist=$schooldist ";
echo "What is in the array after saving schooldist <br>";
print_r($array);

//echo $array[$searchparam_counter];
++$searchparam_counter;
}

/*
$arrayLength = count($arrayName);

for ($i = 0; $i < $arrayLength; $i++){
echo "arrayName at[" . $i . "] is: [" .$arrayName[$i] . "]<br>\n";
}
*/

///////////////////////////////////////////////////////
// CREATE SQL QUERY TO CALC NUMBER OF PAGES ///
// String saved as $getpages_str
////////////////////////////////////////////////////////
if( $searchparam_counter == 0 ) {
$getpages_str = "select count(*) from PROPERTY";
}else {
$getpages_str = "select count(*) from PROPERTY WHERE ";

for ( $i = 0; $i < $searchparam_counter; ++$i ) {

if ( $i >= 1 ) {
$getpages_str .= " AND ";
}

$getpages_str .= $array[$i];

if ( $i == $searchparam_counter-1 ){
$getpages_str .= ";";

}

}//end for loop


echo "final GETPAGE string is:".$getpages_str."<br>"; // use for debug
}


//$getpages_str = "select count(*) from PROPERTY where bedrooms=4 AND baths=3;";

db_connect();
// get the pager input values
$page = $_GET['page'];
$limit = 5;

//Run *Page*Count Query
$page_result = mysql_query($getpages_str);
$total = mysql_result($page_result, 0, 0);
echo "Total num of records retrieved is: ".$total;

// work out the pager values
$pager = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit = $pager->limit;
$page = $pager->page;

echo "offset is: ".$offset;
echo "Page is: ".$page;
echo "Limit is: ".$limit;


////////////////////////////////////////////////////////
// CREATE SQL QUERY STATEMENT TO RETRIEVE RECORDS ///
////////////////////////////////////////////////////////
if( $searchparam_counter == 0 ) {

$str = "select * from PROPERTY ";

}else {

$str = "select * from PROPERTY WHERE ";

for ( $i = 0; $i < $searchparam_counter; ++$i ) {

if ( $i >= 1 ) {
$str .= " AND ";
}

$str .= $array[$i];

}//end for loop



$str .= " limit $offset, $limit;";
// Note:you have to know $offset and $limit before you can run the query


echo "Query Records string is:".$str; // use for debug

}









//////////////////// RUN MySQL QUERY RETRIEVE DATA ////////////////////////
$result = mysql_query($str); //Note: use $str here! NOT $getpages_str

// Test for no search results in a separate function
if( !$result){
echo "Search produced no results.";
return; //return...
}


// use $result here to output page content
print('<table width="750" border="1" cellspacing="0" cellpadding="0">');

while( $row = mysql_fetch_array($result) ) {

$vimage_name = $row['image_name'];
$vschooldist = $row['schooldist'];
$vbedrooms = $row['bedrooms'];
$vbaths = $row['baths'];
$vprice = $row['price'];

print("<tr>
<td width='75' align='center'><img src='../images/".$vimage_name."' width='75'/></td>
<td width='100' align='center'>$vschooldist</td>
<td width='50' align='center'>$vbedrooms</td>
<td width='50' align='center'>$vbaths</td>
<td width='450' align='left'>$vprice</td>
</tr>
");

} // end while loop

print('</table>');

//NOTE: FORMAT PAGE LINKS IN A TABLE
// 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
echo "Previous";
else // not the first page, link to the previous page
echo "<a href=\"search_results_sunday.php?page=" . ($page - 1) . "\">Previous</a>";

for ($i = 1; $i <= $pager->numPages; $i++) {
echo " | ";
if ($i == $pager->page)
echo " $i";
else
echo "<a href=\"search_results_sunday.php?page=$i\"> $i</a>";
}

if ($page == $pager->numPages) // this is the last page - there is no next page
echo "Next";
else // not the last page, link to the next page
echo "<a href=\"search_results_sunday.php?page=" . ($page + 1) . "\"> Next</a>";

?>





<?php

include('../includes/test_shared_functions.php');
include('includes/admin_functions.php');

/*
Use city as a category...
City: <select name='Any'>
<option value='Colorado Springs'>Colorado Springs</option>
<option value='Denver Metro'>Denver</option>
<option value='Castle Rock'>Castle Rock</option>
</select> <br><br>
*/

print("
<form action='search_results_sunday.php' method='get'>

School District:
<select name='schooldist'>
<option value=''>Any</option>
<option value='3'>3</option>
<option value='11'>11</option>
<option value='20'>20</option>
<option value='49'>49</option>
</select> <br><br>

Bedrooms:
<select name='bedrooms'>
<option value=''>Any</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
<option value='6'>6</option>
</select> <br><br>

Baths:
<select name='baths'>
<option value=''>Any</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
<option value='6'>6</option>
</select> <br><br>

Min. Price:
<select name='minprice'>
<option value=''>Any</option>
<option value='0'>0</option>
<option value='20000'>20,000</option>
<option value='40000'>40,000</option>
<option value='60000'>60,000</option>
<option value='80000'>80,000</option>
<option value='100000'>100,000</option>
<option value='120000'>120,000</option>
<option value='140000'>140,000</option>
<option value='160000'>160,000</option>
<option value='180000'>180,000</option>
<option value='200000'>200,000</option>
<option value='220000'>220,000</option>
<option value='240000'>240,000</option>
<option value='260000'>260,000</option>
<option value='280000'>280,000</option>
<option value='300000'>300,000</option>
<option value='320000'>320,000</option>
<option value='340000'>340,000</option>
<option value='360000'>360,000</option>
<option value='380000'>380,000</option>
<option value='400000'>400,000</option>
<option value='500000'>500,000</option>
</select> <br><br>

Max. Price:
<select name='maxprice'>
<option value=''>Any</option>
<option value='0'>0</option>
<option value='20000'>20,000</option>
<option value='40000'>40,000</option>
<option value='60000'>60,000</option>
<option value='80000'>80,000</option>
<option value='100000'>100,000</option>
<option value='120000'>120,000</option>
<option value='140000'>140,000</option>
<option value='160000'>160,000</option>
<option value='180000'>180,000</option>
<option value='200000'>200,000</option>
<option value='220000'>220,000</option>
<option value='240000'>240,000</option>
<option value='260000'>260,000</option>
<option value='280000'>280,000</option>
<option value='300000'>300,000</option>
<option value='320000'>320,000</option>
<option value='340000'>340,000</option>
<option value='360000'>360,000</option>
<option value='380000'>380,000</option>
<option value='400000'>400,000</option>
<option value='500000'>500,000</option>
</select> <br><br>

<input name='submit' type='submit' value='Search' />
<input name='reset' type='reset' value='Clear Form' />

</form>
");

?>


/*Since I have more space, I've included my search.php code below: */


<?php

print("
<form action='search_results6.php' method='post'>

School District:
<select name='schooldist'>
<option value=''>Any</option>
<option value='3'>3</option>
<option value='11'>11</option>
<option value='20'>20</option>
<option value='49'>49</option>
</select> <br><br>

Bedrooms:
<select name='bedrooms'>
<option value=''>Any</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
<option value='6'>6</option>
</select> <br><br>

Baths:
<select name='baths'>
<option value=''>Any</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
<option value='6'>6</option>
</select> <br><br>

Min. Price:
<select name='minprice'>
<option value=''>Any</option>
<option value='0'>0</option>
<option value='20000'>20,000</option>
<option value='40000'>40,000</option>
<option value='60000'>60,000</option>
<option value='80000'>80,000</option>
<option value='100000'>100,000</option>
<option value='120000'>120,000</option>
<option value='140000'>140,000</option>
<option value='160000'>160,000</option>
<option value='180000'>180,000</option>
<option value='200000'>200,000</option>
<option value='220000'>220,000</option>
<option value='240000'>240,000</option>
<option value='260000'>260,000</option>
<option value='280000'>280,000</option>
<option value='300000'>300,000</option>
<option value='320000'>320,000</option>
<option value='340000'>340,000</option>
<option value='360000'>360,000</option>
<option value='380000'>380,000</option>
<option value='400000'>400,000</option>
<option value='500000'>500,000</option>
</select> <br><br>

Max. Price:
<select name='maxprice'>
<option value=''>Any</option>
<option value='0'>0</option>
<option value='20000'>20,000</option>
<option value='40000'>40,000</option>
<option value='60000'>60,000</option>
<option value='80000'>80,000</option>
<option value='100000'>100,000</option>
<option value='120000'>120,000</option>
<option value='140000'>140,000</option>
<option value='160000'>160,000</option>
<option value='180000'>180,000</option>
<option value='200000'>200,000</option>
<option value='220000'>220,000</option>
<option value='240000'>240,000</option>
<option value='260000'>260,000</option>
<option value='280000'>280,000</option>
<option value='300000'>300,000</option>
<option value='320000'>320,000</option>
<option value='340000'>340,000</option>
<option value='360000'>360,000</option>
<option value='380000'>380,000</option>
<option value='400000'>400,000</option>
<option value='500000'>500,000</option>
</select> <br><br>

<input name='submit' type='submit' value='Search' />
<input name='reset' type='reset' value='Clear Form' />

</form>
");

?>




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users