Page 1 of 1

Pagination with Sort Problem

Posted: Mon Oct 26, 2009 1:54 am
by green_coder
I really need someone clue bout this

let say i've displayed 4 records within 2 pages

Name Gender <--------------Page1
---------------------
Aaliyah Female
Barry Male
-----------------------
[1][2] Next

Name Gender <---------------page2
---------------------
Christ Male
Denny Male
---------------------
[1][2]


at the top of a page [Name] and [Gender] are links to do the sort.
The problem occurs on page 2 when i click on the [Name] to do the sort

as the result will be like this
Name Gender
---------------------
Aaliyah Female
Barry Male
-----------------------

the right one should look like this

Name Gender
---------------------
Denny Male
Christ Male
---------------------
Can anyone help me, especially with the MYSQL..i've tried OFFSET but still the same
thx in advance

Re: Pagination with Sort Problem

Posted: Mon Oct 26, 2009 2:03 am
by rajkumar_pb
^^ If you want to do reverse sort, then just change the query(assuming that you're retrying valued from DB) to get the values in descending order and link that to the name field...

BTW Which pattern are u using to do this. I used AJAX+JSP to do so.

Re: Pagination with Sort Problem

Posted: Mon Oct 26, 2009 2:09 am
by green_coder
still don't get what you mean..Below is the code

I need someone helps regarding pagination problem...i actually want to make my page limited to let say 50

so it will look like this

page1=0-50
page2=51-100

Then i want to sort let say based on the name of a person by giving <a href> and ORDER i can do the sort, but the problem occurs when i want to sort records on page 2, everything got messed up

Can anyone help me..below is the code:

<?php

$var2 =$_GET['title'] ;
$var3 =$_GET['field'] ;
$var4 =$_GET['country'] ;

$orderby = $_GET['orderby'];
if(empty($_GET['orderby'])){
$orderby="firstname";}

$sort=$_GET['sort'];
if(empty($_GET['sort'])){
$sort="DESC";}

if($sort=="DESC"){
$sort="ASC";}else
if($sort=="ASC"){
$sort="DESC";}


$trimmed2 = trim($var2); //trim whitespace from the stored variable
$trimmed3 = trim($var3);
$trimmed4 = trim($var4);

$table = "members";


$field_to_search2 = "title";
$field_to_search3 = "field";
$field_to_search4 = "country";


$query = "SELECT COUNT(*)FROM $table WHERE $field_to_search2 LIKE '%$trimmed2%'AND $field_to_search3 LIKE '%$trimmed3%' AND $field_to_search LIKE '%$trimmed%' AND $field_to_search4 LIKE '%$trimmed4%' ORDER BY $orderby $sort";

$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrow = $r[0];

$rowsperpage = 100;
$totalpages = ceil($numrow / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {

$currentpage = (int) $_GET['currentpage'];

} else {
$currentpage = 1;
}
if ($currentpage > $totalpages) {

$currentpage = $totalpages;
}
if ($currentpage < 1) {

$currentpage = 1;
}
$offset = ($currentpage - 1) * $rowsperpage;

$query5 = "SELECT * FROM $table WHERE $field_to_search2 LIKE '%$trimmed2%'AND $field_to_search3 LIKE '%$trimmed3%' AND $field_to_search LIKE '%$trimmed%' AND $field_to_search4 LIKE '%$trimmed4%' AND $field_to_search5 LIKE '%$trimmed5%' ORDER BY $orderby $sort";

$result5 = mysql_query($query5);
$count5 = mysql_num_rows($result5);

$query = "SELECT * FROM $table WHERE $field_to_search2 LIKE '%$trimmed2%'AND $field_to_search3 LIKE '%$trimmed3%' AND $field_to_search4 LIKE '%$trimmed4%'ORDER BY $orderby $sort LIMIT $offset, $rowsperpage";

$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);

$count = mysql_num_rows($result);

$info = mysql_fetch_array($result);

$numresults=mysql_query($query);

$numrows=mysql_num_rows($numresults);
$mypage=$_SERVER['PHP_SELF'];


if ($numrows == 0 )


{



echo "<center><font face=arial size=2><p>Search </font>"."<font face=arial size=2> returned zero results</font></p></center>";

}



if (empty($s))

{

$s=0;

}


// get results

$result = mysql_query($query) or die("Couldn't execute query");



if($numrows > 1 ){$return = "results";}

else{ $return = "result"; }


echo "<center><table><tr><td><a href=page.php?title=$var2&field=$varx3&q=$var&country=$var4&campus=$var5&orderby=firstname&sort=$sort&search2=Find class=sort>Name</a></td><td width = 70 bgcolor=brown><a href=page.php?title=$var2&field=$var3&country=$var4&orderby=gender&sort=$sort&search2=Find class=sort>gender</a></td></tr></table></center>";



$count = $s + 1 ;

while ($r= mysql_fetch_array($result))

{

$gender = $r["gender"];
$firstname1 = $r["firstname"];

$field = $r["field"];
$country = $r["country"];

$count++ ;




echo "<center><table><tr><td>$firstname</td><td>$gender</td><td>$country</td></tr></table>";

}
$range = 5;

echo"<center>";
if ($currentpage > 1) {

$var2 =$_GET['title'] ;
$var3 =$_GET['field'] ;
$var4 =$_GET['country'] ;




echo " <a href='{$_SERVER['PHP_SELF']}?title=$var2&field=$var3&country=$var4&search2=Find&currentpage=1'><font face=verdana size=2>First</font></a><font face=verdana size=2> |</font>";
$prevpage = $currentpage - 1;
echo " <a href='{$_SERVER['PHP_SELF']}?title=$var2&field=$var3&country=$var4&search2=Find&currentpage=$prevpage'><font face=verdana size=2>Previous</font></a><font face=verdana size=2> | </font>";
}

for ($x = ($currentpage - $range); $x < ($currentpage + $range); $x++) {

if (($x > 0) && ($x <= $totalpages)) {

if ($x == $currentpage) {

echo "<font face=verdana size=2> [<b>$x</b>] </font>";


} else {

echo " <a href='{$_SERVER['PHP_SELF']}?title=$var2&field=$var3&country=$var4&search2=Find+resume&currentpage=$x'><font face=verdana size=2>$x</font></a> ";
}
}
}

if ($currentpage < $totalpages) {

$nextpage = $currentpage + 1;

echo "<font face=verdana size=2>|</font> <a href='{$_SERVER['PHP_SELF']}?title=$var2&field=$var3&country=$var4&search2=Find&currentpage=$nextpage'><font face=verdana size=2>Next</font></a><font face=verdana size=2> | </font>";

echo "<a href='{$_SERVER['PHP_SELF']}?title=$var2&field=$var3&country=$var4search2=Find&currentpage=$totalpages'><font face=verdana size=2>Last</font></a>";
}
echo"</center>";


?>