Query next/previous record [duplicate]

This question already has answers here: Closed 11 years ago.

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.

---

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

---

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;

---

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?