PHP SQL Pagination

Question

I can't seem to get the desired result from this query:

  SELECT * 
    FROM `messages` 
   WHERE `msgType` = '0' 
     AND `status` = '0' 
ORDER BY `dateSent` DESC LIMIT 20, 0

Basically, I'm trying to show 20 results per page, but this query returns nothing. (For the record, all instances in the database have msgType and status as 0)

EDIT: Removing the LIMIT part gives me the results but not divided and paginated like I want

EDIT v2 LIMIT should be followed by OFFSET, # OF RECORDS (I am dumb)

What flavor of SQL are you using?

Jeff Rupert
– Jeff Rupert

2010-07-23 19:05:14 +00:00
Commented Jul 23, 2010 at 19:05 — Jeff Rupert
– Jeff Rupert, Commented Jul 23, 2010 at 19:05

Wrikken · Accepted Answer · 2010-07-23 19:10:13Z

5

LIMIT 20, 0

Means: start at row 21, return 0 rows, so your answer is correct.

Did you mean:

LIMIT 0, 20

answered Jul 23, 2010 at 19:10

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

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

4 Comments

OMG Ponies Over a year ago

The offset is zero based, but that is the issue here

st4ck0v3rfl0w Over a year ago

There are a total of 24 records. When I do LIMIT 1,20 it gives me records 23, 22, 21, 20....4

Wrikken Over a year ago

You are totally correct, a some collision in my brain for some reason, I'll edit accordingly..

OMG Ponies Over a year ago

@st4ck0v3rfl0w: You're not the first to get parameters backwards - I prefer questions like these to the denormalized data models...

hollsk · Accepted Answer · 2010-07-23 19:03:55Z

2

Try removing the single quotes from around your 0's?

answered Jul 23, 2010 at 19:03

hollsk

3,13725 silver badges34 bronze badges

1 Comment

OMG Ponies Over a year ago

Most databases do implicit conversion between INT and string/VARCHAR

OMG Ponies · Accepted Answer · 2010-07-23 19:10:46Z

1

It took Cthulhu's answer to jog my memory - the issue is the LIMIT clause.

In MySQL, when LIMIT takes two parameters - the first is the offset, meaning which row it starts from, where the first row is zero. So:

LIMIT 20, 0

...will start on the 21st row, and return... zero rows from that point.

You need to reverse the values to get anything back:

LIMIT 0, 20

...to get the first 20 rows.

answered Jul 23, 2010 at 19:10

community wiki

OMG Ponies

1 Comment

st4ck0v3rfl0w Over a year ago

I am dumb. It's official. Thank you.

cthulhu · Accepted Answer · 2010-07-23 19:07:54Z

0

What results do you get when removing the limit? Simplify, even if the query is already simple to begin with. Break the problem up into parts.

How many rows are in the DB matching the conditions?
What is the datatype of msgType and status?
What happens when you remove the limit?

answered Jul 23, 2010 at 19:07

cthulhu

3,1682 gold badges25 silver badges33 bronze badges

1 Comment

st4ck0v3rfl0w Over a year ago

All rows match the conditions. Datatype of msgType and status is varchar. When I remove the limit, it provides the results in time sequence starting from the latest

Collectives™ on Stack Overflow

PHP SQL Pagination

4 Answers 4

4 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related