PHP-MySql: Select previous row and next row from current ID
| Posted in Programming | Posted on 15-07-2008
55
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?
id +1 is realy easy way if you have autoincrement and no deleted post, your solution is great
Yes I was also wondering how you would go about this, if you were to search the next entry on name so a string value?
Thanks for the help
Saudara Vandai..
newbie nih…,
saya ketemu ini blog, juga karena lagi bingung
untuk link prev – next panggil baris data.
seperti yg bak wan bilang,
soalnya, “manggil” gak liwat numerik id,
tapi liwat string text dari file jpg.
(itu juga hasil utak-atik kopas kode yg udh ada).
pingin nerapin ide bakwan…masih bingung…
he-he-he…:)
bangga juga, anak2 indo banyak yg pinter2.
makasi atas postingnya.
salam.
you’re god dude ……………
Thank you so much
thank you!!
Thank you!
how itsworks if it is a random id
in some case id can be a higher value and some tomes it will come lower than the previous
no problem, just change the ORDER BY with any field..
Unfortunately that’s not true. If you order by time, for instance, the id may be less than the given id while the time could be greater. I believe a subquery would be necessary.
i want to get some records which has been added after a particular id
there is no auto increment id for the table
Nice tutorial. Simply elegant and effective.
hi this is pretty neat. unlike on what i did to my project where i query all id and put these on an array variable then access it one by one. this will save me more time and resources. thanks ill try this right away.
I realy like your solution! this helps me alot.
btw, i like your way of explaining the scripts. keep it up!
Thanks! used it at my website. Easy to use!
Following is a complete code for displaying Current Record With Previous, Next Record button
0) {
$info = mysql_fetch_assoc($result);
} else {
die(‘Not found’);
}
// Prev id
$sql2 = “SELECT * FROM user_login WHERE id 0) {
$prevrow = mysql_fetch_assoc($result2);
// echo ”. print_r($prevrow,true) . ”;
$previd=$prevrow['id'];
// echo “Prev = “.$previd.”";
}
// Next id
$sql1 = “SELECT * FROM user_login WHERE id > {$curid} LIMIT 1″;
$result1 = mysql_query($sql1);
if (mysql_num_rows($result1)>0) {
$nextrow = mysql_fetch_assoc($result1);
// echo ”. print_r($nextrow,true) . ”;
$nextid=$nextrow['id'];
// echo “Next = “.$nextid.”";
}
} else {
// No form has been submitted so use the lowest id and grab its info
$sql = “SELECT id,username,password FROM user_login WHERE id > 0″;
$result = mysql_query($sql);
if (mysql_num_rows($result)>0) {
$info = mysql_fetch_assoc($result);
$sql1 = “SELECT * FROM user_login WHERE id > {$info['id']} LIMIT 1″;
$result1 = mysql_query($sql1);
if (mysql_num_rows($result1)>0) {
$nextrow = mysql_fetch_assoc($result1);
$nextid=$nextrow['id'];
}
}
}
if (isset($info)) {
$content = ”.print_r($info,true).”;
} else {
‘;
$content = ‘Nothing in the db
}
?>
Next prev
Info
<input type="submit" name="id[]” value=”prev”/>
<input type="submit" name="id[]” value=”next”/>
// Prev id
$sql2 = “SELECT * FROM user_login WHERE id 0) {
$prevrow = mysql_fetch_assoc($result2);
// echo ”. print_r($prevrow,true) . ”;
$previd=$prevrow['id'];
// echo “Prev = “.$previd.”";
}
// Next id
$sql1 = “SELECT * FROM user_login WHERE id > {$curid} LIMIT 1″;
$result1 = mysql_query($sql1);
if (mysql_num_rows($result1)>0) {
$nextrow = mysql_fetch_assoc($result1);
// echo ”. print_r($nextrow,true) . ”;
$nextid=$nextrow['id'];
// echo “Next = “.$nextid.”";
}
BRRILLIANT!!! you sir saved me hours.
thnx
Here’s what I came up with for ordering by a separate field (time):
SELECT id
FROM posts
WHERE time >= (SELECT MAX(time)
FROM posts
WHERE time
(SELECT time
FROM posts
WHERE id = $id))
Some thing happened to the less-than symbol:
the second WHERE should be:
WHERE time <
That chopped off the rest of the query. Here’s the rest:
ORDER BY time ASC
LIMIT 3
With one query that returns three values (or two if its the last row): last, current and next. It only fails if the value of id is the first row. If you get an empty return value, just execute the following to get the id of the second row:
(see next post)
SELECT id
FROM posts
WHERE time = (SELECT MIN(time)
FROM posts
WHERE time >
(SELECT time
FROM posts
WHERE id = $id))
After a couple of hours with a BACK NEXT problem to show results on a particular query you gave me the cleanest solution ever.
Tons of thanks