blog.swapnilsarwe.com

This post is about how to get a previous and next record as per the particular field of the table Eg: Date navigation

I learnt this from my recent requirement where I had the records on daily basis. I wanted very easy navigation with Previous Date & Next Date link to navigate to and fro.

REQUIREMENT:

Given a date – I should be able to get the previous date and next date
Possibly data for a particular date might be missing in this case navigation should not land on date for which data is not available.

For eg: I am on the page for 5th Jan 2013 – so my navigation should show links for 4th Jan 2013 as prev & 6th Jan 2013 as next. But what if I don’t have the data for 4th June, in such case prev link should navigate to 3rd Jan 2013.

Here instead of data you can put any field (which represent for eg: ID, RANK, SCORE, WEIGHT, PRICE) which you want it in a particular order.

QUESTION:

So how do we achieve it?

PLAN:

To get the previous record

– We query the table to get all the results less than current record and order it by descending and limit it to 1 to get the immediate previous record.

– Query:

	SELECT 
		`fields_you_need` 
	FROM 
		`table_name` 
	WHERE 
		`where_conditions_if_any` 
		AND `field_for_which_you_want_previous_record` > `current_value` 
	ORDER BY 
		`field_for_which_you_want_previous_record` DESC 
	LIMIT 1;

To get the next record

We query the table to get all the results greater than current record and order it by asc and limit it to 1 to get the immediate next record.

– Query:

	SELECT 
		`fields_you_need` 
	FROM 
		`table_name` 
	WHERE 
		`where_conditions_if_any` 
		AND `field_for_which_you_want_previous_record` > `current_value` 
	ORDER BY 
		`field_for_which_you_want_previous_record` ASC 
	LIMIT 1';

DRAWBACKS: Yes there is a drawback of hitting the DB twice to get the required information.

SOLUTION: Anyways, till the time we get the better solution, lets just put it in the function so that it can reusable and if we got any better solution in the future we just make the change in the function.

function getPrevNextRecord(
	$fieldsRequired, 
	$tableName, 
	$fieldToCompare, 
	$currentValue, 
	$whereConditions = false
)
{
	$whereConditions = ($whereConditions)?$whereConditions.' AND ':'';
	$arrNav = array();
	//query to fetch the details of previous record
	$sqlPrev = 'SELECT '.$fieldsRequired.' 
				FROM '.$tableName.' 
				WHERE ' .$whereConditions.$fieldToCompare.' < '.$currentValue.' 
				ORDER BY '.$fieldToCompare.' DESC 
				LIMIT 1';
	$rsPrev  = mysql_query($sqlPrev);
	$arrNav['prev'] = mysql_fetch_row($rsPrev);

	//query to fetch the details of next record
	$sqlNext = 'SELECT '.$fieldsRequired.' 
				FROM '.$tableName.' 
				WHERE ' .$whereConditions.$fieldToCompare.' > '.$currentValue.' 
				ORDER BY '.$fieldToCompare.' ASC 
				LIMIT 1';
	$rsNext = mysql_query($sqlPrev);
	$arrNav['next'] = mysql_fetch_row($rsNext);

	return $arrNav;
}

HOW TO USE:

Call the function with the required parameters and it will return you the array with 2 values viz: prev & next. If the record is not available it will return false.

$navigationDate = getPrevNextRecord(
	'created_date, title', 
	'posts', 
	'created_date', 
	'5', 
	'author="johnydepp" AND status="1"'
);

if($navigationDate['prev']){
	echo '<a title="'.$navigationDate['prev'][1].'" href="?date='.$navigationDate['prev'][0].'">Previous</a>';
}
if($navigationDate['next']){
	echo '<a title="'.$navigationDate['next'][1].'" href="?date='.$navigationDate['next'][0].'">Next</a>;';
}

I implemented it in some of my collections at my sub domain http://collections.swapnilsarwe.com/. I hope you find it helpful in some way or the other. Got any better solution regarding the query to get the result. Please post it below in the comment section.

Prev/Next pagination query - Date navigation - MySQL