Find the "next" highest id in MySQL

I am developing a web application where there are a number of very开发者_运维技巧 similar pages I'd like users to browse through. The data for these "pages" is stored in a database with a unique ID as the primary key.

I'd like to have a "NEXT" button on each page that queries the database and finds out what the next highest ID is, and displays the data from that id. My problem is that there are a couple conditions:

• Sometimes pages may be deleted or removed, meaning that there are gaps in the IDs, so I can't just do -1.
• I need to only return pages where the column 'active' == 1

Does anyone have any tips or suggestions? Thanks!

something like this?

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

a PREV button would then need something like this:

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

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

I don't quite understand your scenario I think, but anyway:

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

?

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

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

That should give you exactly what you are looking for, the active page with the highest id lower than the current page.

Use Codeignier's active record class CIAR

Then put $this->db->select_max(); in your application for getting the maximum possible id of the rows.