CODEWITHSUNDEEP
phpsqlmysqlpagination
st4ck0v3rfl0w
st4ck0v3rfl0w

Reputation: 6755

PHP SQL Pagination

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)

Upvotes: 0

Views: 788

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 1

Wrikken
Wrikken

Reputation: 70460

LIMIT 20, 0

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

Did you mean:

LIMIT 0, 20

Upvotes: 5

cthulhu
cthulhu

Reputation: 3169

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?

Upvotes: 0

hollsk
hollsk

Reputation: 3137

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

Upvotes: 2

Related Questions