I am trying to create a function in PHP that returns "next_id" and "prev_id", from an sql table.

However, the id is not simply 1,2,3,etc which means if one record id is 50, the previous id might be 22 while the next id might be 81.

I tried, in this way...

public function prevNext($id)
    {
        "SELECT
            IFNULL(SELECT min(id)
                from table
                where id > t.id ) as next_id
            , IFNULL(SELECT max(id)
                from table
                where id < t.id) as prev_id
        from table as t
        where id = $id";
    }

CodePudding user response：

Hi to select the next id you just need to take the minimal id among those who are greater than the current and vice-versa for the previous. Here are the sql requests :

1. For the next_id : SELECT MIN(id) FROM table WHERE id>current_id
2. For the previous : SELECT MAX(id) FROM table WHERE id<current_id

CodePudding user response：

finally reached on a solution to solve my problem

public function prevNext($id)
    {
        "SELECT
            IFNULL(( SELECT min(id)
                from table
                where id > t.id ), (SELECT min(id)
                from table)) as next_id
            , IFNULL(( SELECT max(id)
                from table
                where id < t.id ), (SELECT max(id)
                from table)) as prev_id
        from table as t
        where id = $id";
     }

CodePudding user response：

If LAG and LEAD are supported in your version of MySQL, you could try something like this:

SELECT id, LAG(id) OVER(ORDER BY id) AS PrevID, 
    LEAD(id) OVER(ORDER BY id) AS NextID
FROM table as t
WHERE id = $id