Murray Picton

Find previous and next row in MySQL

Next and previous buttons are used all over the web. There are many ways to do them and the differences of each situation lend themselves to different ways of doing them. I recently needed to find a way of finding the next and previous rows of a large MySQL table where I am not doing a traditional “LIMIT x,y” clause. With some help from the LinkedIn MySQL Professionals group I was able to work out the best way to do it in my situation.

First of all, let’s take a look at a simple MySQL statement:

SELECT foo FROM bar ORDER BY foo LIMIT 0, 10

Anyone that has a limited knowledge of MySQL should realise that this is going to return the first 10 rows of foo from the bar table in order of foo. This is the simplest way of paginating in MySQL and by altering the first parameter after the LIMIT keyword will allow us to go to other pages.

This however, was was not going to give me what I needed in the situation I found myself in. What I know is the unique id of a specific row and need to get the next and previous rows. The easiest way to do this is with PHP:

<?php
$result = mysql_query("SELECT id FROM foo ORDER BY id"); //Get all our ids from foo
$currentRow = 5; //Id of the current row
$previousId = $nextId = null;
while($row = mysql_fetch_assoc($result)) { //Loop through our rows
	
	if($row['id'] < $currentRow) //If we haven't hit our current row yet
		$previousId = $row['id']; //Set our previousId to the current row
	
	if($row['id'] > $currentRow) { //If the id is bigger then our current row
		$nextId = $row['id']; //Set our nextId to the current row
		break; //Exit out of our loop
	}
}
?>

This will give us 2 ids, one for the previous row and one for the next. This is perfect for the situation I was in, except for one thing. The table that I am wanting to get the previous and next rows from is huge. If I were to get the previous and next rows with this method I would have to get the whole table out for every page load and loop through loads and loads of rows. This is really inadequate and would give a huge performance hit on my page.

To demonstrate the best method, I am going to introduce another field to my table – dateadded. This is being introduced because it is not unique – this could be the same for more than 1 row whereas our id field is unique for each row. Let’s take a look at how to do this:

<?php
$currentId = 5; //Our current id
$currentDate = "2010-09-01"; //Our current date
$next = mysql_query("SELECT id FROM foo WHERE dateadded >= '$currentDate' AND id > $currentId ORDER BY dateadded, id LIMIT 1"); //Get our next row
$previous = mysql_query("SELECT id FROM foo WHERE dateadded <= '$currentDate' AND id < $currentId ORDER BY dateadded, id LIMIT 1"); //Get our previous row
?>

As you can see – by refining our WHERE clause and introducing proper ORDER BY clauses, we have managed to just get the single previous and next rows that we need. We have introduced a second query to get to this, but it is much quicker and more efficient than getting out the whole table and looping through just to get two results.

Thanks for reading. If you have enjoyed this, please take a look at some of my other posts and as always, check back soon for more great articles.

Update

With thanks to Richard’s comment below I have discovered that the above code only works in certain circumstances. In order to make it work always, I will have to use the STRCMP function that I discovered here. So, let’s have a look at the updated code:

<?php
$currentId = 5; //Our current id
$currentDate = "2010-09-01"; //Our current date
$next = mysql_query("SELECT id FROM foo WHERE STRCMP(CONCAT(dateadded,id), '$currentDate$currentId') = 1 ORDER BY dateadded, id LIMIT 1"); //Get our next row
$previous = mysql_query("SELECT id FROM foo WHERE STRCMP(CONCAT(dateadded,id), '$currentDate$currentId') = -1 ORDER BY dateadded, id LIMIT 1"); //Get our previous row
?>

The STRCMP function compares the first argument with the second and returns 1 if the item is next according to the ORDER BY clause and -1 if it is previous. This makes our next/previous idea much simpler than before. The reason I have to CONCAT my fields is to make sure that they are all used, if I just used dateadded in my STRCMP, I would end up with the same problem previously discussed where equal values get missed. The order of my CONCAT MUST be the same as the order of my ORDER clause for this to work correctly.

Once again, thanks for reading. I hope the update helps!