CODEWITHSUNDEEP
phpmysql
white-comet
white-comet

Reputation: 391

Pagination query using SQL

I have a table called 'items'

It has the following columns: id, name, stock, price, and category_id.

My query is 

SELECT * FROM items WHERE category_id = {$category_id} LIMIT 10;

my logic for next page is store all ids in a variable called $oldIds and the next query is:

SELECT * FROM items WHERE category_id = {$category_id} AND id NOT IN ($oldIds)  LIMIT 10

for another page is i store again the ids from 1st page and 2nd page do again the query

SELECT * FROM items WHERE category_id = {$category_id} AND id NOT IN ($oldIds)  LIMIT 10

Do i continue using this style of query or is there a better way of querying?

Upvotes: 0

Views: 38

Answers (1)

satroy
satroy

Reputation: 116

for mysql

First statement should be:

mysql_query("SELECT * FROM {$statement} ORDER BY datetime ASC LIMIT {$limit} OFFSET {offset}

if 10 records per page is what you are looking for and you want to show records on page 2 i.e. records 11-20 your query will look like:

mysql_query("SELECT * FROM {$statement} ORDER BY datetime ASC LIMIT 10 OFFSET 10

Upvotes: 1

Related Questions