SQL LIMIT returning more values than LIMIT

Question

Ok I am a bit confused as to whats happening with my page. I have page numbers at the bottom and a table full of items say 23 assorted items.

Each page should display say 5 items at a time. I have the pages displaying which sends a get with pagenumber = whatever.

So basically I have this:

$highLimit = $pageNo*5;
$lowLimit = $highLimit-5;
$sql = mysql_query("SELECT * FROM items 
                   ORDER BY id DESC 
                   LIMIT $lowLimit, $highLimit");

So here's what happens: The first page works fine and displays the correct 5. Then the second page display the next 10 and the third page displays the last 13. Then page 4 displays the last 8 of page 3 and page 5 displays the last 3 of page 4.

When I output num_rows for $sql it says 5, 10, 13, 8, 3. I don't see how It can get more rows then the limit I put in.

The limit goes 0 5, 5 10, 10 15 etc and seems to work.

So is the $sql keeping the original data in it from previous pages or something?

I really don't know what the problem is hopefully someone can help.

ty

just to clarify this is the current pages and ID of data on that page

PAGE 1:

23, 22, 21, 20, 19

PAGE 2:

18, 17, 16, 15, 14
13, 12, 11, 10, 9

PAGE 3:

13, 12, 11, 10, 9
8,  7,  6,  5,  4
3, 2, 1

PAGE 4:

8,  7,  6,  5,  4
3, 2, 1

PAGE 5:

3, 2, 1

possible duplicate of [confusion in mysql limit when used with offset](http://stackoverflow.com/questions/10119291/confusion-in-mysql-limit-when-used-with-offset) — Mosty Mostacho, Oct 04 '13 at 03:13

score 1 · Accepted Answer · answered Oct 04 '13 at 02:46

1

The syntax of LIMIT is optionally the starting offset then the number if rows you want (documentation). So your second value should always be 5

answered Oct 04 '13 at 02:46

Jason Sperske

29,816
8
73
124

Sorry, I had them backwards in my original answer – Jason Sperske Oct 04 '13 at 02:48
Yeah thanks works perfect now. I was under the impression from the brief scan of LIMIT that I was selecting the ranges of data to be got. – MomasVII Oct 04 '13 at 03:06
MySQL syntax is worlds better than MS SQL syntax for this (they might have fixed this by now but I remember porting a ORM to MSSQL from MySQL and there were so many inverse queries to get page n>1 – Jason Sperske Oct 04 '13 at 03:18

score 0 · Answer 2 · answered Oct 04 '13 at 02:59

Well try this way

$page = isset($_GET["page"]) ? $_GET["page"] : 1;

$max = 5;
$page--;
$lmt = $page * $max;
$page++;
//count number of records from table
$count = 20;

$pages = ceil($count/$max);
$stmt =  $db->prepare("SELECT * FROM table_name ORDER BY id DESC LIMIT ".$lmt.", 3");

SQL LIMIT returning more values than LIMIT

2 Answers2