PHP SQL Pagination Problem

Question

I can't seem to get the desired result.

  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 db 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)

Answer 1

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

Answer 2

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?

Answer 3

LIMIT 20, 0  

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

Did you mean:

LIMIT 0, 20  

Answer 4

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.