previous, next

[non_zero]

i have a script that selects a certain art piece uploaded by a certain user, and finds the art uploaded before and after the current one. this is so i can make "next" and "previous" links.

i'm doing this by putting all the art pieces in an array, and finding the items before and after the array item that holds the current art piece.

//create array
$ids = array();

//get art pieces from database
$art = mysql_query("SELECT * FROM art WHERE author_id=$author_id ORDER BY art_id");

//add each row to array
while ($a = mysql_fetch_array($art)) {
    $aid = $a["art_id"];
    array_push($ids, $aid);
}

//go through array
for ($i = 0; $i < count($ids); ++$i) {
    //if current row is selected art piece
   //$plink equals array item before it, and $nlink is array item after.
    if ($ids[$i] == $art_id) {
        $plink = $ids[$i - 1];
        $nlink = $ids[$i + 1];
    }
}

this works fine, but it's awfully complicated to do such a simple thing.

is there an SQL command that will do what i want to do, or something else to make this process simpler?

[altexis]

maybe the LIMIT sql command is what you're looking for 🙂

[Mystis]

Yes it is, in order to use it use the syntax LIMIT first, number

For example, if you would like to show records starting at #17, and show the next 10, you would use

$art = mysql_query("SELECT * FROM art WHERE author_id=$author_id ORDER BY art_id LIMIT 17, 10");

Or if you would like to show the first 20 records:

$art = mysql_query("SELECT * FROM art WHERE author_id=$author_id ORDER BY art_id LIMIT 0, 20");

In order to produce the paging effect (prev/next) then you would just have to use dynamic numbers for those values, passed through the url string. Since you have quite a few posts and it looks like you know your way around PHP then I'll try and let you figure it out, if you need any more help let me know 🙂