CODEWITHSUNDEEP
phpmysqllimitoffset
Shane Larson
Shane Larson

Reputation: 597

MySQL Limit Rows Bug

SELECT * FROM `news` 
WHERE `deleted` = '0' 
ORDER BY `time` DESC 
LIMIT $START, $END

When LIMIT = 0,10 it loads good.
When LIMIT is 10,20 it loads good.
When LIMIT is 20,30 it loads the last 4 rows already shown in 20,30 again...

Upvotes: 3

Views: 4016

Answers (3)

Frank Fu
Frank Fu

Reputation: 1

Quoted from http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html:

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

Return the first N rows from the queue. (If M was specified, skip the first M rows and return the next N rows.)

  • LIMIT 0, 10 -- first 10 rows, rows 1-10
  • LIMIT 10, 10 -- rows 11-20
  • LIMIT 10, 20 -- rows 11-30, not 11-20
  • LIMIT 20, 30 -- rows 21-50, not 21-30

Upvotes: 0

K6t
K6t

Reputation: 1845

First fetch Full data from DB using SELECT SQL_CALC_FOUND_ROWS FROM [table name]; it return total count;

use Three variable;

$start $end; $total_records;

use ceil($total_records/$start); U ll get roundValue; use it limit

Upvotes: -2

Rich Adams
Rich Adams

Reputation: 26574

The way you're using LIMIT is incorrect. It's not,

LIMIT <start> <end>

but rather, 

LIMIT <offset> <number_of_rows>

From the MySQL documentation,

The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

So for example,

... LIMIT 0, 10 -- first 10 rows
... LIMIT 10, 10 -- rows 10-20
... LIMIT 10, 20 -- rows 10-30, not 10-20
... LIMIT 20, 30 -- rows 20-50, not 20-30

If you don't have enough rows in the resultset to fill the limit, it will just return up to the last row. For example, if you only retrieve 15 rows, ...LIMIT 10, 10 would return rows 10-15.

Upvotes: 11

Related Questions