Select the previous and next entry knowing id
Faced recently with the task of showing the [FORWARD] [BACK] buttons on the preview page. But the complexity of the problem is that the sorting can be on an arbitrary field of the table.
Formulation of the problem:
CREATE TABLE `contacts` (
ʻid` int (11) NOT NULL AUTO_INCREMENT,
`name_prefix` varchar (50) DEFAULT NULL,
`name` varchar (100) DEFAULT NULL,
`infix` varchar (100) DEFAULT NULL,
`surname` varchar (100) NOT NULL,
`primary_email` varchar (100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8;
')
For clarity, I will explain where it all works:
There are two pages working with this table:
- index, it displays all entries from the contacts table, there is a filter and there is a sorting by columns
- view, view the current entry from the contacts table. On this page there are buttons [FORWARD] [BACK], taking into account the filter and sorting specified on the index page;
The difficulty arose in these two buttons.
Thus, the task is reduced to getting the id of the previous and next record relative to the current id, taking into account the filter and arbitrary sorting (by any of the fields).
If there was a sorting by id - everything would be clear, with a fixed sorting, the issue seems to be solved too http://habrahabr.ru/blogs/mysql/85945/
For simplicity and brevity, I will show the solution using PHP.
Decision:
Well, that's all. If anyone has a more elegant solution WELCOME!
Formulation of the problem:
CREATE TABLE `contacts` (
ʻid` int (11) NOT NULL AUTO_INCREMENT,
`name_prefix` varchar (50) DEFAULT NULL,
`name` varchar (100) DEFAULT NULL,
`infix` varchar (100) DEFAULT NULL,
`surname` varchar (100) NOT NULL,
`primary_email` varchar (100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8;
')
For clarity, I will explain where it all works:
There are two pages working with this table:
- index, it displays all entries from the contacts table, there is a filter and there is a sorting by columns
- view, view the current entry from the contacts table. On this page there are buttons [FORWARD] [BACK], taking into account the filter and sorting specified on the index page;
The difficulty arose in these two buttons.
Thus, the task is reduced to getting the id of the previous and next record relative to the current id, taking into account the filter and arbitrary sorting (by any of the fields).
If there was a sorting by id - everything would be clear, with a fixed sorting, the issue seems to be solved too http://habrahabr.ru/blogs/mysql/85945/
For simplicity and brevity, I will show the solution using PHP.
Decision:
/* .*/
$sqlConditions = " WHERE name ='test' " ;
$sqlOrder = " ORDER BY name, surname" ;
$curId = 33; //ID
/* , , id */
$sql = "SELECT rownumb,id FROM (
SELECT
@rownumb := @rownumb+1 AS rownumb,
`Contact`.`id` FROM `contacts` AS `Contact`
,(SELECT @rownumb := 0) al
$sqlConditions $sqlOrder
) AS src " ;
/* */
$rowNumber = $ this ->query($sql. " WHERE src.id = $curId" );
/* */
if (!empty($rowNumber[0][ 'src' ][ 'rownumb' ])) {
$rowNumber = $rowNumber[0][ 'src' ][ 'rownumb' ];
} else {
return false ;
}
if ($rowNumber > 1) { /* $rowNumber > 1 ( )*/
/* rownumb*/
$res = $ this ->query($sql. " WHERE id <> $curId LIMIT " .($rowNumber - 2). " ,2" );
/* prevId*/
if (!empty($res[0][ 'src' ][ 'id' ])){
$result[ 'prevId' ] = $res[0][ 'src' ][ 'id' ];
}
/* NextId */
if (!empty($res[1][ 'src' ][ 'id' ])){
$result[ 'nextId' ] = $res[1][ 'src' ][ 'id' ];
}
} else { /* $rowNumber == 1 */
/* */
$res = $ this ->query($sql. " WHERE rownumb > $rowNumber LIMIT 1" );
/* NextId*/
if (!empty($res[0][ 'src' ][ 'id' ])){
$result[ 'nextId' ] = $res[0][ 'src' ][ 'id' ];
}
}
* This source code was highlighted with Source Code Highlighter .Well, that's all. If anyone has a more elegant solution WELCOME!
Source: https://habr.com/ru/post/114372/
All Articles