PHP Pagination

[diabolo]

SELECT  *  FROM  `textbook` WHERE  `class`=:class ORDER  BY `textbook`.`ch`

+ ---- + ----- + -- + ------ + ------ +
| .id. | class | ch | sTitle | fTitle |
+ ---- + ----- + -- + ------ + ------ +
| 6... | CK2.. | 1. | ...... | ...... |
| 7... | CK2.. | 3. | ...... | ...... |
| 8... | CK2.. | 6. | ...... | ...... |
| 115. | CK2.. | 9. | ...... | ...... |
| 116. | CK2.. | 11 | ...... | ...... |
| 9... | CK2.. | 13 | ...... | ...... |
| 10.. | CK2.. | 15 | ...... | ...... |
| 11.. | CK2.. | 17 | ...... | ...... |
| 12.. | CK2.. | 20 | ...... | ...... |
| 13.. | CK2.. | 22 | ...... | ...... |
+ ---- + ----- + -- + ------ + ------ +

Let's say i bring up the chapter for 11.

SELECT  *  FROM  `textbook` WHERE  `class`=:class AND `ch`=:ch"

+ ---- + ----- + -- + ------ + ------ +
| .id. | class | ch | sTitle | fTitle |
+ ---- + ----- + -- + ------ + ------ +
| 116. | CK2.. | 11 | ...... | ...... |
+ ---- + ----- + -- + ------ + ------ +

I also want to have previous and next buttons. But the design flaw in the database is that I can't just do a `ch`+1 or a `ch`-1 because they are not all in sequencial order.
I figured that I can make another column `order`, but I do not want to add unneccessary columns to the database.
Another way I was thinking would to have all the values of `ch` in an array and try to sort it somehow.

I could be overlooking a very simple solution.

Note:
~I am using PDO.

 

[dlukin]

Are you saying if a request comes in to display ch 11, you want to retrieve the information for ch 9 and for ch 13 (using your above data)?

 

[diabolo]

Are you saying if a request comes in to display ch 11, you want to retrieve the information for ch 9 and for ch 13 (using your above data)?

yes sir.

 

[misson]

Use the LIMIT clause.

SELECT <columns> 
  FROM `textbook` 
  WHERE `class`=:class AND `ch` > :ch 
  ORDER BY `ch` ASC
  LIMIT 1

Index column ch for best performance. An index on `textbook` (`class`, `ch`) will work.

 

[diabolo]

so I would use this query to find the previous chapters?

SELECT <columns> 
  FROM `textbook` 
  WHERE `class`=:class AND `ch` < :ch 
  ORDER BY `ch` DESC
  LIMIT 1

also, what kind of check could I run to see if there is no more previous/next chapters?

 

[descalzo]

That query should work.

If result set is empty, then no previous/next chapter (depending on the query).

 

[diabolo]

thanks guys that did the trick. now im gonna go back to my absence. ill be back soon.