How can I select rows in MySQL starting at a given row number?

Question

Say I have 50 rows in a MySQL table. I want to select the first ten (LIMIT 10), but then I want to be able to select the next 10 on a different page.

So how do I start my selection, after row 10?

Updated query:

mysql_query("
    SELECT * FROM `picdb`
    WHERE `username` = '$username'
    ORDER BY `picid` DESC
    LIMIT '$start','$count'
")

Answer 1

I recommend working by obtaining the first page using:

LIMIT 0, 10

then for the second page

LIMIT 10, 10

then

LIMIT 20, 10

for the third page, and so on.

Answer 2

LIMIT 10

LIMIT 10 OFFSET 10

From the MySQL 5.1 docs on SELECT syntax:

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

Answer 3

This question is old but i just want to add a code that is not hardcoded, the answer chaos gave means you'll have to hardcode your scripts(Select statement). you can achieve the same results by getting the file name and then select data from the database based on the current page, without hardcoding your select statement. first get the current page

$page = basename($_SERVER['SCRIPT_FILENAME']);
$page_counter = rtrim($page, ".php");
//setting your limit
$start = 0;
$limit = 10;
//if current page is not index.php then $start = ($limit * page_counter); 
// e.g if current page is 1.php then $start = ($limit * 1) = 10
//if current page is 2.php then $start = ($limit * 2) = 20
if ($page !== 'index.php') {

 $start = ($limit * $page_counter);
}
//getting row count
$ROW_COUNT = $db->query('SELECT * from tableName')->rowCount();

//getting number of rows left in the table
$rows_left = ("SELECT * FROM tableName limit ?,?");
$rows_left = $db->prepare($rows_left);
$rows_left->execute(array($start,$ROW_COUNT));
$rows = $rows_left->fetchAll(PDO::FETCH_ASSOC);

$number_rows = 0;
foreach ($rows as $r) {
 $number_rows = $number_rows + 1;
 }
 //if number of rows left in the table is less than 10 then $limit = the number of rows left
 if ($number_rows < 10) {
 $limit = $number_rows;
 }

 //getting all rows
            $getRows = "SELECT * FROM tableName limit ?,?";
            $getRows = $db->prepare($getRows);
            $getRows->execute(array($start , $limit));
            $getRows = $getRows->fetchAll(PDO::FETCH_ASSOC);

Answer 4

select * from 'table_name' 
ORDER BY 'column_id 'DESC 
LIMIT 0,10;

select * from 'table_name' 
ORDER BY 'column_id' DESC 
LIMIT 10,10;

select * from 'table_name' 
ORDER BY 'column_id' DESC
LIMIT 20,10;

and continue till the numbers you want.