Reputation:
I'm not sure exactly how this is called but I'll try to describe as good as I can what I want to acheive.
So, first of all, there is a variable, called $id
, which is actually $_GET['id']
. Assuming the user is entering the following page by requesting: /page.php?id=6
. Now what I need to do is to provide the information about the next 3 pages from database. Here is the database:
TABLE `pages`
id | page_name
______________________
1 | AAAAA
2 | BBBBB
3 | CCCCC
4 | DDDDD
5 | EEEEE
6 | FFFFF
7 | GGGGG
8 | HHHHH
9 | IIIII
So, while requesting the page with id 6
, the following script returns the next 3 pages (7,8,9):
$res = mysql_query("SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` DESC LIMIT 3");
while($arr = mysql_fetch_assoc($res))
{
print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
}
And here is the output:
Page ID: 7; Page Name: GGGGG
Page ID: 8; Page Name: HHHHH
Page ID: 9; Page Name: IIIII
And it works fine until the $id
is greater then 6. When it is (/page.php?id={7/8/9}
), the output doesn't show 3 pages any more, but 2 pages, 1 page and respectively no output when $id
is 9
.
So my question is: Is there a way to go back and start from the beginning when there are not enough results (less than 3) to display?
/page.php?id=8
, the output should contain pages with id 9
, 1
and 2
. /page.php?id=9
, the output should contain pages with id 1
, 2
, 3
. /page.php?id=3
, the output should contain pages with id 4
, 5
, 6
and so on.Upvotes: 1
Views: 461
Reputation: 4769
I would solve this way (one possible issue is that the resultset could contain at most 6 records instead of 3):
$res = mysql_query("(SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3) UNION DISTINCT (SELECT * FROM `pages` WHERE id>0 ORDER BY id ASC LIMIT 3)");
$counter = 0;
while($arr = mysql_fetch_assoc($res) && $counter<3)
{
$counter++;
print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
}
Upvotes: 0
Reputation: 4045
(SELECT *, 0 AS custom_order FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3)
UNION ALL
(SELECT *, 1 AS custom_order FROM `pages` ORDER BY `id` ASC LIMIT 3)
ORDER BY custom_order, id ASC
LIMIT 3
This way you always get 3 pages. If not enough next pages, you will get up to 3 from the beginning.
Upvotes: 3
Reputation: 7991
You could modify the query to be something like:
select * from
(select *, id-$inval($id) as order_by
from pages were id > $inval($id) order by id asc limit 3
union
select *, id as order_by
from pages order by id asc limit 3 ) as pages
order by order_by asc
Upvotes: 0