$prevRecord/$nextRecords and Categories

Hi Perch,

Great idea!

I'll add this feature to the next release of CMS Builder, but since the change is so small I thought you might want to make it yourself to your CMS Builder so you can start using it right away. That is, if you're feeling adventurous!

Open up cmsAdmin/lib/viewer_functions.php in a text editor, look for the following section, and add the line in red:

// get mysql to figure out which nums are prev, next, first, and last
// NOTE: "The order of evaluation for expressions involving user variables is undefined..." See: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
$query = "SELECT \n";
$query .= " IF(@firstNum, NULL, @firstNum:=num),\n"; // get firstRecordNum
$query .= " @lastNum := num,\n"; // get lastRecordNum
$query .= " IF(num='$targetNum', (@foundTarget:=1) AND (@prevNum:=@lastSeenNum), @lastSeenNum:=num),\n"; // get prevRecordNum
$query .= " IF(@foundTarget=1 AND num !='$targetNum' AND @nextNum = 0, @nextNum := num, null)\n"; // get nextRecordNum
$query .= "FROM `$mysqlTableName` \n";
if (@$options['where']) { $query .= "WHERE {$options['where']} \n"; }
$query .= "ORDER BY $orderBy \n";
mysql_query($query) or die("MySQL Error: ". htmlspecialchars(mysql_error()). "\n");

Now you can add a 'where' clause to the options you send to getPrevAndNextRecords(). For example:

list($prevRecord, $nextRecord, $firstRecord, $lastRecord) = getPrevAndNextRecords(array(
'tableName' => 'articles',
'recordNum' => $record['num'],
'where' => mysql_escapef('category = ?', $record['category']), // optional - defaults to all records in section
'orderBy' => 'createdDate', // optional - defaults to schema listPageOrder
));

I hope this helps! Please let me know if you have any questions.