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'
")

Solution 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.

Solution 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.

Solution 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);

Comments

[Rufinus]

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

[chaos]

Re edit, you should get your error feedback set up to the point that it will tell you what's wrong with your SQL. You'll find you have a syntax error because your LIMIT clause is before your ORDER BY clause.

[mpen]

Is that valid? I've never seen that.

[engma]

sorry to ask but i kept wondering how does this work? it works for me but i can't get it, does it mean to fetch staring from 21 with maximum of 10 rows ?

[engma]

i've been doing some searches for a while now, and the starting number depends on the engine, when i tested in with innodb it started with 0 not 1, so you should check your engine preferences.

[chaos]

@Developer106: Actually, I can't replicate the index starting with 1 in any circumstance, so I don't know how the previous version of this answer even happened.

[Zachary Craig]

Welcome to stackoverflow! This is a pretty old, high traffic question, does your answer add something that one of the other, already established answers doesn't? Generally speaking it's better if you can edit the existing, accepted answers to reflect any necessary changes rather than an entire new answer, as most users won't scroll so far down to the bottom, (where your answer will appear) answer will be.