CODEWITHSUNDEEP
phpmysqlajaxpagination
sergey_c
sergey_c

Reputation: 761

Simple PHP MySQL pagination (show more button, with parameters)

I'm trying to create a simple pagination for my website.
I have a table's rows with 3 value
id (AI)
name
price 

SELECT * FROM `mytable` ORDER BY `id`LIMIT 10

I use very simple MySQL request to show it, and JS/AJAX button to show more.
When it ordered by ID, it's easy to show more - I know the last ID, and I'm adding this info to the next request 

SELECT * FROM `mytable` WHERE `id` > $lastid ORDER BY `id` LIMIT 10

But HOW can I create same pagination, if I'll order it by price?
It can be 100 items with same price, so I can't write

WHERE `price` > $lastprice

because it will show only 10% of all items with same price
Also, I can't write

WHERE `price` > $lastprice AND `id` > $lastid

because item with biggest id can be with less price. Any ideas to make it work?
Thanks!
p.s. Actually it's not a real pagination, it's only one button "SHOW MORE" that takes next 10 items from server by AJAX.

Upvotes: 0

Views: 1465

Answers (2)

nicolaenichifor
nicolaenichifor

Reputation: 111

You don't actually need the WHERE clause in this case. The query should be like this

SELECT * FROM `mytable` ORDER BY `price` DESC LIMIT <offset>, <results per page>

Where offset is the number of results to be skipped and results per page is the number of results to be returned (in your case 10). In order to calculate offset value you need the page number instead of last id for example. If you know that then (I assume is php on the backend):

$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * <resutls per page>;

This way you will get an offset of 0 on the first page, 10 on the second and so on.

Upvotes: 1

peppeocchi
peppeocchi

Reputation: 824

You can do that by javascript, when calling the ajax function, pass it the number of rows already printed, then in mysql change the query to 

..... LIMIT "value passed", 10

Upvotes: 1

Related Questions