Incrementing through a MySql query list

[jlanders]

HI All,

I have been searching for pointer to a problem I have for sometime and I am just not having luck finding answers.

 

I have a sql query that pulls anywhere from a 200 to a 2000 list of rows from the database.  I display this list on a php page with pagination.  When I click on a row from that list to display details, I go to a different page and display that data.  

 

I am trying to figure out a way to provide a "next" button and then step into the next record from the original query.

 

Any suggestions or pointers on how to do that?  It is a wide open question I know.

 

 

[Muddy_Funster]

we'll need more info on the mechanisms you are using to help properly.

[mac_gyver]

you would take the currently selected id and find a row with where the id is greater than that id, limit 1

[mikosiko]

you would take the currently selected id and find a row with where the id is greater than that id, limit 1

That is not necessarily true... according to the OP:

 

I am trying to figure out a way to provide a "next" button and then step into the next record from the original query.

is no warranty that the original query has the id's in sequence.. the WHERE and ORDER BY clauses (if present) can obviously affect the selected id's as well as their order.

One possible solution is store in an array the selected id's in the order of the original query and use next () and prev () as needed.

[kicken]

The easiest thing to do would be to save a list of ID's into a $_SESSION variable (or memcache or another DB table) and then track which ID you are currently using. Even with a 2000 item list, storing only the IDs into a session variable shouldn't cause any major problems.

 

A site I maintain has a similar feature (though the records list is typically small, 10-15 items max) and I handle it using two $_SESSION variables:

$_SESSION['openItems'] = array(/* list of IDs the user has opened for viewing */);
$_SESSION['currentItem'] = [0 - count($_SESSION['openItems'])-1]; /* The index of the currently viewed item. */

Previous/Next operations are just a matter of -1 or +1 to $_SESSION['currentItem'] (with appropiate overflow handling). Then $_SESSION['openItems'][$_SESSION['currentItem']] gets the ID of the record to view. Edited August 7, 2013 by kicken

[jlanders]

you would take the currently selected id and find a row with where the id is greater than that id, limit 1

 

Great minds!.  Thank you for your post mac.  Especially considering I did not give you much to go on.  =/  The limit 1 function is how it is written now and it does work, however I needed a method to move up and down the original query list. Without storing the original order as Miko suggests... it shuffles things around.

[jlanders]

That is not necessarily true... according to the OP:

 

is no warranty that the original query has the id's in sequence.. the WHERE and ORDER BY clauses (if present) can obviously affect the selected id's as well as their order.

One possible solution is store in an array the selected id's in the order of the original query and use next () and prev () as needed.

 

You are exactly right Miko, This is the problem that I was trying to solve the most efficient way possible and pass that list back and forth between pages.

[jlanders]

The easiest thing to do would be to save a list of ID's into a $_SESSION variable (or memcache or another DB table) and then track which ID you are currently using. Even with a 2000 item list, storing only the IDs into a session variable shouldn't cause any major problems.

 

A site I maintain has a similar feature (though the records list is typically small, 10-15 items max) and I handle it using two $_SESSION variables:

$_SESSION['openItems'] = array(/* list of IDs the user has opened for viewing */);
$_SESSION['currentItem'] = [0 - count($_SESSION['openItems'])-1]; /* The index of the currently viewed item. */

Previous/Next operations are just a matter of -1 or +1 to $_SESSION['currentItem'] (with appropiate overflow handling). Then $_SESSION['openItems'][$_SESSION['currentItem']] gets the ID of the record to view.

 

 

I thought of this too, but I was afraid of the long list of items in an session variable.  I tried this and it worked perfectly.  Just as you suggest the overhead was minimal.

 

So what I did is I saved off all the record Id's as I looped through the row list and then pushed the array into a session variable.  This worked awesome as I did not even have to worry about pulling from the $POST variables everything was already sitting in the session variable.

 

Thanks for Everyone's thoughts and support.  Awesome forum!