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 — Mosty Mostacho
– Mosty Mostacho, Commented Oct 4, 2013 at 3:13

Jason Sperske · Accepted Answer · 2013-10-04 02:46:52Z

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 4, 2013 at 2:46

Jason Sperske

30.5k8 gold badges77 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jason Sperske Over a year ago

Sorry, I had them backwards in my original answer

MomasVII Over a year ago

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.

Jason Sperske Over a year ago

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

Shushant · Accepted Answer · 2013-10-04 02:59:29Z

0

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

answered Oct 4, 2013 at 2:59

Shushant

1,6551 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL LIMIT returning more values than LIMIT

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related