You can easily select the rows from the database by giving it a starting row number and a limit to how many. You can use the LIMIT command in your select syntax to do this.
This will limit the results to the first 10 rows, starting on row 0 (the first row displayed since arrays start on 0 and not 1)
SELECT * FROM `my_table` ORDER BY id ASC LIMIT 0, 10
This will limit the results to 25, starting on row 99 (100th entry)
SELECT * FROM `my_table` ORDER BY id ASC LIMIT 99,25
Basically the first number in the command is the starting row and the second is how many maximum results to return. If there is only 4 possible results but you limit it to 5, it will still only return the 4 results.
As for the limiting the number of rows per query, use a variable to pass through the command (I'd check with ctype_digit() to make sure its a whole integer and avoid sqli while defaulting to whatever on failure). If the user isn't defining the number of rows, then you can just place a number there.