Query next/previous record

Question

Possible Duplicate:
What is the SQL for 'next' and 'previous' in a table?

I'm trying to find a better way to get the next or previous record from a table. Let's say I have a blog or news table:

CREATE TABLE news (
    news_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    news_datestamp DATETIME NOT NULL,
    news_author VARCHAR(100) NOT NULL,
    news_title VARCHAR(100) NOT NULL,
    news_text MEDIUMTEXT NOT NULL
);

Now on the frontend I want navigation buttons for the next or previous records, if i'm sorting by news_id, I can do something rather simple like:

SELECT MIN(news_id) AS next_news_id FROM news WHERE news_id > '$old_news_id' LIMIT 1
SELECT MAX(news_id) AS prev_news_id FROM news WHERE news_id < '$old_news_id' LIMIT 1

But the news can be sorted by any field, and I don't necessarily know which field is sorted on, so this won't work if the user sorts on news_author for example.

I've resorted to the rather ugly and inefficient method of sorting the entire table and looping through all records until I find the record I need.

$res = mysql_query("SELECT news_id FROM news ORDER BY `$sort_column` $sort_way");
$found = $prev = $next = 0;
while(list($id) = mysql_fetch_row($res)) {
    if($found) {
        $next = $id;
        break;
    }
    if($id == $old_news_id) {
        $found = true;
        continue;
    }
    $prev = $id;
}

There's got to be a better way.

Edit, clarifications:

Why dont I use limit? I would need to know the position in the result set of the current record, which I don't. If this were your typical pagination, and I had query strings like ?startpage=n then yes that would work, I could just increment $startpage and add LIMIT $startpage,1 to the query. But I have urls like news/news_id-news-title-here that are rewritten to ?news_id=n, so I don't know what the startpage is. Even if I did, what if the user gets there via an external link? What if new posts are added while the user is reading the current page?

Don't get too stuck on the specifics of the example above, the real question is this:

Given a unique record id and an arbitrary sort column, is there a way to determine which records fall immediately before and after that specific record, without looping through the entire record set.

Just curious. Do you have anything to protect $sort_column from injection? — Your Common Sense, Apr 02 '10 at 13:41
@Shrapnel, yes... above is a hypothetical example but in real world code I would validate `$sort_column` against an array of valid, sortable columns `$sort_column = (in_array($sort_column, $valid_columns)) ? $sort_column : $default_sort_column;` — Rob, Apr 02 '10 at 13:47

Rufinus · Answer 1 · 2010-04-02T14:05:40.833

0

Why you dont use limit ?

e.g. you listing 10 items per page. Limit 1,10 the next 10 items: Limit 11,10 and so on.

no matter if you display one or 100 items per page, the system is always the same.

EDIT: more explanation needed, if you have a detail page, you most certainly will come to it via a list, the list was created with a certain query, so you know the position in this list, and can give this param to the detail page. so when you there have prev/next links you can call the query with a limit of 1 and the offset from your params.

EDIT 2: You can use a query to find the position of the row based on your order but this will only work with DESC:

SELECT  COUNT(*)+1 FROM news ma JOIN news mp ON (mp.$sort_column, mp.news_id) > (ma.$sort_column, ma.news_id) WHERE ma.news_id = $news_id;

edited Apr 02 '10 at 14:05

answered Apr 02 '10 at 13:37

Rufinus

29,200
6
68
84

Limit can't be used to show certain record. as it's url must be always the same. Absolute, not relative. So, it must be `id`, not position – Your Common Sense Apr 02 '10 at 13:39
To do that I would first need to know the position of the current record in the result set, which I don't. I only know the `news_id`, so I'd still have to loop through the entire result set and count positions until I found the match. – Rob Apr 02 '10 at 13:42
edit my post. rob seems to understand what i meant. nevermind, but rob, looping through all items dont work for mixed sorting. as i see it you only can store the offset of the newsitem as param and use it for prev/next link, or you have to store the list query parts to find the position on the detail page. – Rufinus Apr 02 '10 at 13:49

Your Common Sense · Answer 2 · 2010-04-02T13:54:01.450

0

why don't you use the same way for the news_author you used for the news_id?

EDIT: There is one pitfall: news_author is not unique for sure. So, you will need to order news_author query by 2 fields : news_author, news_id. So, you will need 2 conditions to get next author

edited Apr 02 '10 at 13:54

answered Apr 02 '10 at 13:38

Your Common Sense

156,878
40
214
345

Hmmm, I guess I assumed that `MIN` and `MAX` wouldn't work on a `VARCHAR` field... – Rob Apr 02 '10 at 13:45
@Rob, Ah I didn't notice that. But you don't need min and max on the set that consists only one row :) just `select news_id ...` – Your Common Sense Apr 02 '10 at 13:48

score 0 · Answer 3 · answered Apr 03 '10 at 03:13

in pseudo code: $currentValue = "SELECT $sort_column FROM news WHERE news_id = $current_news_id LIMIT 1"

$operator = ($sort_way == 'ASC')?' > ':' < ';

$nextNewsId = "SELECT news_id FROM news WHERE $sort_column $operator $currentValue ORDER BY $sort_column $sort_way LIMIT 1"

no?

Query next/previous record

3 Answers3