Pagination query using SQL

Question

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?

Instead of doing it like this, why not order the products by name/id/stock/price and use LIMIT and OFFSET — ScottMcGready
– ScottMcGready, Commented Sep 1, 2015 at 0:55
i did not know how to use offset until i read your comment and search for this — white-comet
– white-comet, Commented Sep 1, 2015 at 1:03
@ScottMcGready your a life saver thanks bro i hope you the answer instead of comment. i will accept at the answer below because it it gave also the right answer this comment is to appreciate your help :) — white-comet
– white-comet, Commented Sep 1, 2015 at 1:09

satroy · Accepted Answer · 2015-09-01 01:04:19Z

1

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

answered Sep 1, 2015 at 1:04

satroy

1169 bronze badges

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