PHP-MySql: Select previous row and next row from current ID
| Posted in Programming | Posted on 15-07-2008
30
During the dark age, the simplest way to get previous row from current ID was using this query:
-
SELECT field1
-
FROM tablename
-
WHERE id = ($currentId - 1)
and to get next row:
-
SELECT field1
-
FROM tablename
-
WHERE id = ($currentId + 1)
But there’s a problem. Like common data, we need to delete some rows. For example, the current ID is 14 ($currentid = 14) , and i deleted row id number 13, when i run the query to get the previous row, the result will empty. The sql cannot find ID number 13 ($currentid – 1).
How to fix it?
Again, the first simplest way i got is to loop the ID. When it cannot find the first query, it calculate again by looping it with PHP.
-
<?
-
$ok = false;
-
$prev = $currentid - 1;
-
while($ok == false){
-
$sql = "select * from tablename where id = $prev ";
-
$data = $db->query($sql);
-
if(empty($data)){
-
$prev = $prev-1;
-
}else{
-
$ok = true;
-
}
-
}
-
?>
This is really bad idea. It will consume more resources by continually query the database. What happen when you deleted 10 rows from current ID?
There will be 10 looping with 10 query to database. How about deleted 100 rows?? You must be insane doing this.
So what’s the solution?
We’ll not using the equal ( = ) operator since we must find the exact ID first. We will use the smaller and larger operator ( < > ) and combine it with “limit”.
Query to get the previous row:
-
SELECT field1
-
FROM tablename
-
WHERE id < $currentid
-
ORDER BY id DESC
-
LIMIT 1
And the next row:
-
SELECT field1
-
FROM tablename
-
WHERE ID > $currentid
-
ORDER BY id ASC
-
LIMIT 1
Simply, find the smaller ID from the current ID, sort it by descending, and limit by 1 to ensure we got the exactly previous row. And the same way for the next row. Get the larger ID from the current ID, sort it by ascending, and limit by 1.
No more resource wasting.
sayangnya trik ini tidak bisa diterapkan jika ID bukan bertipe data numerik (integer, float, dll)
solusinya mungkin seperti ini :
kita harus tahu di posisi row keberapakah data yang saat ini ditampilkan, simpan ke variabel misal $currentRow
kode :
$goNext = $currentRow + 1;
$goPrev = $currentRow – 1;
kemudian link untuk navigasinya jadi seperti ini :
Prev
Next
gunakan passing variabel $currentRow dari navigasi untuk query berikut :
SELECT * FROM something LIMIT $currentRow, 1
selengkapnya >>
Thank you so much for this. I’ve been looking for a more efficient way of doing this for some time. Its a simple, elegant solution.
Thank’s a lot. Indeed it is a elegant solution. Only one problem: if I have reached the last row (or the first row) and then I put the next-button (previous button) I get a empty form mask. How can I avoid this?
Two ideas (logically, no technical):
1. If I reached the last row (last dataset) the next button is disabled.
What is the technical way to program this?
2. If I reached the last row pushing the next-button fetches again the last row.
How can I program this?
Thanks a lot for all helpful suggestions
Martin
1. Add this:
$data = (your sql query);
if(empty($data))
echo “<input type=’button’ value=’next’ disabled=’true’ />”;
2. Add more query. or add ‘if-else’ case on your query.
Nice and simple idea
Very nice blog. I appreciate your efforts.
Can you please let me is it possible with one query?
Kunal Mehta
—
http://elevatesoftsolutions.in/post/2009/01/11/how-portal-iframes-open-splendid-crm-page-like-sugar-crm-php.aspx
Thanks a lot for this useful information. I have tried this and it worked perfectly. I highly appreciate this solution. Previously, i used to write like id=current_id + 1. I want next id to move the current record down. But as the next id might have got deleted, i have to click on down button many times to move the current record down, till i reach the next existing id in the table. So i used like “seelct id > current_id” and it instantly moves the current record down and vice-versa, moves up. Thanks a lot.
it’s 100% not working if you have random id.. or delete 1 id both another.
Up’s sorry i didn’t lookout your solution:D
it’s working**
thx
How if not sorting by id.. but by date?? like wordpress??
Do the same but change the date to timestamp strtotime then compare sizes.
@einchi: the same, select the next/previous date and proceed.
thanks a lot for this solution, it’s brilliant.
Gut!
You rock dude !.. Really cool tip
Hello webmaster
I would like to share with you a link to your site
write me here preo…@mail.ru
Perfect!Amazing!Super!Thank you very much
==
http://www.vinyladdiction.tv/sitemap.xml
Кайтсерфинг, школа кайтсерфинга, обучение кайтсерфингу,кайтинг, кайтинг обучение, кайт школа, кайт школа вьетнам.
Couldn’t I get the previous and next items in one query by changing the limit to 3 and using the first and third row of the resultset?
forget about it, of course not.
But you could UNION ALL the two queries if you wished to retrieve both values in one query.
UNION example (including a subquery to get sorting for certain ID.
(SELECT sorting
FROM table
WHERE sorting (SELECT sorting FROM table WHERE itemid = ?)
AND groupid = ?
ORDER BY sorting ASC
LIMIT 1)
wellwell the editor cut half of my query… I’m giving up
How to fix if previous / next data is empty?
will result empty or null data.
see my previous comment.
thank you so much friend!
your SQL code, very usefull for me
Thanks a lot for writing this. I was looking for a good solution to getting prev/next rows and this is perfect!
your welcome..
hope this is usefull..
Hi
Very smart!
Like the others though, I’m trying to verify if the next and previous results in the DB the easy way. I figured I could cheat doing this:
$Query = “select * from `welcome_images` where `id`’$_HTTP[image]‘ order by `id` asc limit 3″
($_HTTP = $_GET + $_POST)
Unfortunately.. it doesn’t work!
I could use 3 queries and test all 3 of them but .. thats the long hard undynamic way!
just to say for some reason my SQL didn’t come out right in the reply above..
How to sort by string instead of ID?