Find the "next" highest id in MySQL

Question

I am developing a web application where there are some very similar pages that I would like users to browse. The data for these "pages" is stored in a database with a unique identifier as the primary key.

I would like to have a “NEXT” button on each page that queries the database and finds out what the next highest identifier is and displays data from that identifier. My problem is that there are several conditions:

• Sometimes pages can be deleted or deleted, which means there are spaces in the identifiers, so I cannot just do -1.
• I only need to return pages where the column is "active" == 1

Does anyone have any tips or suggestions? Thank!

Answer 1

something like that?

SELECT id FROM table WHERE id > '$id' AND active = '1' ORDER BY id ASC LIMIT 1

The PREV button will require the following:

SELECT id FROM table WHERE id < '$id' AND active = '1' ORDER BY id DESC LIMIT 1

Answer 2

  select t.* 
    from table t
   where t.id = 
         (select min(s.id)
            from table s
           where s.active = 1
             and s.id > target);

Answer 3

   SELECT id 
    FROM pages
   WHERE id > $current_id AND active = 1
ORDER BY id ASC
   LIMIT 1

Answer 4

I do not quite understand your scenario, I think, but anyway:

SELECT id
FROM table
WHERE id > $lastid AND active = 1
ORDER BY id ASC
LIMIT 1

?

Answer 5

SELECT id
FROM table
WHERE id < $lastid AND active = 1
ORDER BY id DESC
LIMIT 1

This should give you exactly what you are looking for, the active page with the highest id below the current page.

Answer 6

Use Codeignier active record class ciar

Then put $this->db->select_max();in your application to get the maximum row id possible.