Sorting records with pagination problem!

[greens85]

Hi all,

 

I am currently returning candidates from a database initially just by the highest candidate id to the lowest... i.e. newest candidates to oldest.

 

I have introduced a sort feature which works just fine, the problem is I am displaying my results over a few pages using pagination.

 

The problem arises once I sort my results and then try to click onto the next page of results. Basically it is just resetting the sort as the sort relies on a button being pressed, which obviously isn't happening when the user is trying to get the next page of results.

 

I'm not sure exactly which section of code would be relevant to this so I have posted the whole script... additionally if you need to take a look at it in action you can do so here:

 

http://www.beta.teachingagencies.co.uk/search_jobseekers.php

 

As I say I know the problem is that when the page is changed the value is lost, but I don't have a clue how to solve the problem!

 

Any help or advice would be appreciated.

 

Many thanks,

 

Greens85

 

 

 

[attachment deleted by admin]

[PFMaBiSmAd]

The page requests that are due to clicking on a pagination link need to use the SAME query that was originally used.

 

The best way of accomplishing this would be to include the necessary values in the pagination URLs so that the same query can be built and executed. This would allow someone to bookmark a specific page and come back to the same results at a later time.

[greens85]

Hi PFMaBiSmAd,

 

Many thanks for your prompt reply!

 

"The page requests that are due to clicking on a pagination link need to use the SAME query that was originally used."

 

By this are you actually saying that I need to somehow pass the SQL query into say this part of the script?

 

echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a>";

 

Or would it actually just be a case of using $_GET therefore passing the values to the URL?

 

Sorry for my lack of understanding on this one!

[PFMaBiSmAd]

You would pass the values for $region, $city, $interests, $experience, and $sortby as separate $_GET values in the URL.

 

Passing the whole or part of the query in the URL would be a security hole because anyone could alter it to inject sql. You would need to completely validate the passed query before using it and that would take many time more code than if just the values are passed.

 

I also notice that your code contains some duplicated code to build the two queries in it now. You need to consolidate and simply to avoid multiple copies of the same code in one script. The only difference between the two queries is the first one uses count(*) and has no LIMIT clause and the second one has the actual SELECT list and does have a LIMIT clause. You should build the middle part of the query only once in the code and reuse that in both queries.

[greens85]

Hi,

 

As you responding I think I have got this working...

 

Not sure if it is done the best way possible but it seems to me that it at least works:

 

I have changed from $_POST to $_GET in the following places:

 

$sortby = mysql_real_escape_string($_GET['sortby']);

 

<form action="" method="get" id="sortby">

 

Then I have amended the pagination to the following:

 

echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&sortby=$_GET[sortby]'><</a>";

 

As I say, it seems to work, but I don't have any idea how safe or efficient this code is.

 

I noticed your comment about the two queries, I got them from a tutorial about pagination, so wasn't too keen on messing with them for fear of breaking my pagination part! I did however notice they were pretty much doing the same thing!