CODEWITHSUNDEEP
phpmysqlnavigationpaginationfetch
ramesh
ramesh

Reputation: 4082

PHP - MySQL "next" - "previous" link with ordering

I have a table like this

id    |   name      |  image     |    ordering
------+-------------+------------+--------------
1     |   name 1    |  one.jpg   |      5
------+-------------+------------+--------------
2     |   name 2    |  two.jpg   |      2
------+-------------+------------+--------------
3     |   name 3    |  thr.jpg   |      3
------+-------------+------------+--------------
4     |   name 4    |  for.jpg   |      7
------+-------------+------------+--------------
5     |   name 5    |  fiv.jpg   |      1
------+-------------+------------+--------------
6     |   name 6    |  six.jpg   |      9
------+-------------+------------+--------------

My requirement to show first image in a page according to the ordering. The following query works for me

SELECT * FROM images ORDER BY ordering ASC LIMIT 0,1 - row with id 5 will return

Next I have to show 2 links at the bottom "Prev" and "Next" ( Since this is the first page dont need to show "Prev" )

Okey .. By pressing on "Next" I have to show the next page ( ie according to the table its row with id 2 ). In that page need to show "prev" which will leads to first result. "Next" from that page must leads to row with id 3

I have tried 

select * from images where id < $local_id order by id desc limit 1
 select * from images where id > $local_id order by id asc limit 1

But since it having the ordering it wont work...

Can any one please share an idea with me ? Thanks in advance

Upvotes: 1

Views: 4161

Answers (4)

KingRider
KingRider

Reputation: 2257

Not good +1 / -1 is bad, prevent id (page) is not exist, use sql where previous id.

Example ur main page id 3 and id 4 not exist... happen:

Next:

select * from images where id = (select min(id) from images where id > 4)

Previous:

select * from images where id = (select max(id) from images where id < 4)

Upvotes: 1

Kashyap
Kashyap

Reputation: 391

you can change in limit to achieve it, change the limit as per changing of page.

    SELECT * FROM images ORDER BY ordering ASC LIMIT 1,1 - row with id 2 will return
    SELECT * FROM images ORDER BY ordering ASC LIMIT 2,1 - row with id 3 will return

Upvotes: 1

fullybaked
fullybaked

Reputation: 4127

In MySQL LIMIT X, Y is the range you want to get, where X is the starting row (0 being the first row) and Y being the number of rows to return

To achieve what you want you would need to use page numbers for each page you were on, then use them to calculate the X value, Y would always be 1 as you only want 1 image on each page.

Something like this would get you started:

<?php

$page = (isset($_GET['page'])) ? $_GET['page'] : 1;

$startPoint = $page - 1;

$query = "SELECT * FROM images ORDER BY ordering ASC LIMIT $startPoint,1";

$rowCount = 0; // replace this with a count of all your rows/images

Then you links would be like

<a href="index.php?page=1">First</a>
<a href="index.php?page=<?php echo $page - 1?>">Prev</a>
<a href="index.php?page=<?php echo $page + 1?>">Next</a>
<a href="index.php?page=<?php echo $rowCount;?>Last</a>

Upvotes: 3

Hanky Panky
Hanky Panky

Reputation: 46900

Next

select * from images where id = $local_id+1 limit 1

Previous

select * from images where id = $local_id-1 limit 1

Upvotes: 1

Related Questions