I'm looking for an elegant solution to the following problem:
I currently have a page listing entries ORDER BY date, with a pagination LIMIT. Here's a snippet of my code:
<?php
$intPageNumber = 1;
if ( isset($this->GET['p']) && (integer)$this->GET['p'] > 1 ) $intPageNumber = (integer)$this->GET['p'];
$strLimit = " LIMIT " . (($intPageNumber-1)*$intItemsPerPage) . ", " . $intItemsPerPage;
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM jos_h_testimonials ORDER BY date DESC ".$strLimit;
// code to load some records here
// ...
// take care of pagination
$db->setQuery('SELECT FOUND_ROWS()');
$intTotalRecords = $db->LoadResult();
$this->NumberOfPages = ceil( $intTotalRecords / $intItemsPerPage);
$this->CurrentPage = $intPageNumber;
?>
Table schema:
CREATE TABLE IF NOT EXISTS `jos_h_testimonials` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`author` varchar(255) NOT NULL,
`testimonial` text NOT NULL,
`rating` int(11) NOT NULL,
`date` int(11) NOT NULL,
`hgroup_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)
This is quite a standard way of handling listing entries in a paged fashion.
My problem is that the client wants to special ordering - they wish to have entries with a certain value for the hgroup_id
foreign key to appear at the top.
The two solutions I can think of are:
- Load the entire list and re-order it using PHP, then handle pagination using PHP;
- Run two queries - one to select entries with a desired foreign key, the second one to select all other entires. Merge result sets and.. handle pagination using PHP.
Either way, my elegant single-query SQL_CALC_FOUND_ROWS approach to pagination won't work. I was curious if there's some MySQL "magic" that I could employ in the ORDER_BY clause?
I hope the above makes sense..