title: Previous/next record based on search result
body: <h2>Some background</h2>
  
  <p>The system in question has some ten thousand records which the user is allowed to browse or search through a "massive" array of different search criteria. The result is then displayed in a paginated table ranging from a few pages to every result in the database.</p>
  
  <p>If the user has made a lot of filtering and ended up with a result and clicks an arbitrary record she is then taken to a new view <code>/edit/{record_id}</code> where she can view all the details of said record. It's easy to go back to the last search result at any time, as the criteria is retained in the search bar, but:</p>
  
  <h2>What I'm trying to achieve</h2>
  
  <p>If the user is to do a lot of changes based on the search result, it would be <em>very</em> convenient with previous and next buttons to access the adjacent records without returning to the result.</p>
  
  <p>I've been able to do this on a basic level using <code>id</code>s</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT table.*, pn.p as previous_question, pn.n as next_question
     from table,
     (
         SELECT id, lead(id) OVER w as n, lag(id) OVER w as p
         FROM table
         window w as (order by id $order)
     ) as pn
     where table.id = $id
         and pn.id = $id
  </code></pre>
  
  <p>This obviously only works for the simplest of cases where the records are in the same order as in the database. What I want is for them to work in <em>every case</em> of data order. Needless to say, the are nearly infinite ways to sort this data.</p>
  
  <h2>What I've been thinking</h2>
  
  <p><strong>Create some kind of function/tool</strong> that will let me keep the SQL from the search and perform it again like above, only with search criteria and offset in stead of <code>id</code>s</p>
  
  <p><strong>Save a list</strong> of the order of all the records in the result, either in the <code>$_SESSION</code>, database or somewhere else, and then simply traverse the list to get the <code>id</code>s I need.</p>
  
  <h2>Tools at hand</h2>
  
  <p>PHP, PostgreSQL, JS/jQuery.</p>
  
  <h2>TL;DR</h2>
  
  <p>I want to be able to get to the next search result without returning to go back to the search, no matter how many times the user clicks "next".</p>
  
  <h1>----------</h1>
  
  <p>Any input would be appreciated. Links to relevant articles, ways to approach this or even the whole shebang ;). It would also be nice to be able to solve this without eating all the servers memory, or excess db access, although resources aren't really an issue here.</p>