PHP-MySql: Select previous row and next row from current ID

Published by Miaz Akemapa

Book minded!!!

Reader Comments

  1. 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 >>

    Reply

  3. 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

    Reply

  4. 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.

    Reply

  7. 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.

    Reply

  11. @einchi: the same, select the next/previous date and proceed.

    thanks a lot for this solution, it’s brilliant.

    Reply

  16. ΠšΠ°ΠΉΡ‚ΡΠ΅Ρ€Ρ„ΠΈΠ½Π³, школа кайтсСрфинга, ΠΎΠ±ΡƒΡ‡Π΅Π½ΠΈΠ΅ кайтсСрфингу,ΠΊΠ°ΠΉΡ‚ΠΈΠ½Π³, ΠΊΠ°ΠΉΡ‚ΠΈΠ½Π³ ΠΎΠ±ΡƒΡ‡Π΅Π½ΠΈΠ΅, ΠΊΠ°ΠΉΡ‚ школа, ΠΊΠ°ΠΉΡ‚ школа Π²ΡŒΠ΅Ρ‚Π½Π°ΠΌ.

    Reply

  17. 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?

    Reply

  20. 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)

    Reply

  27. 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!

    Reply

  31. 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 πŸ™‚

    Reply

  32. 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.

    Reply

  36. 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

    Reply

      1. 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.

        Reply

  39. 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.

    Reply

  42. 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”/>

    Reply

  43. // 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.””;
    }

    Reply

  46. 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))

    Reply

  48. 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)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *