query help! starting at a certain row...

[acidglitter]

i'm working on this gallery, and when you're browsing it would look like this (the numbers are the row id's for each picture in the mysql table)

 

9 8 7 6 5 4 3 2 1

 

then if you clicked on picture 5, it would show the picture, then on the side i want it to show thumbnails of the pictures near it.. like

 

7 6 4 3

 

what kind of mysql query would be able to do this?

 

it would be nice if i could use just one query that like skips two rows ahead from the current picture, then shows the 4 newest rows (excluding the current picture) from that point descending or something.

[Maq]

Should work, not tested...  Not sure how to do it with 1 query.

 

$inc = 2;
//should be: $num_clicked = $_GET['position'];
$num_clicked = 5;
$prev = $num_clicked - $inc;
$next = $num_clicked + $inc;
$next_query = "SELECT * FROM table LIMIT {$num_clicked}, {$inc}";
$prev_query = "SELECT * FROM table LIMIT {$prev}, {$inc}";

[corbin]

You could just do (piggy-backing on Maq's):

 

SELECT * FROM table WHERE id (BETWEEN {$prev} AND {$next}) AND id != {$num_clicked};

[acidglitter]

thanks for your reply! thats actually pretty close to what i'm doing already..

 

SELECT id FROM table WHERE id > '$imgid' ORDER BY id ASC LIMIT 2
SELECT id FROM table WHERE id < '$imgid' ORDER BY id DESC LIMIT 2

 

 

the problem with mine is that it goes

 

6 7 4 3

 

when i want it to be

 

7 6 4 3

 

and i'm so tired i can't think of how to fix it..

[acidglitter]

You could just do (piggy-backing on Maq's):

 

SELECT * FROM table WHERE id (BETWEEN {$prev} AND {$next}) AND id != {$num_clicked};

 

is there a way i could do that where instead of $prev it uses something like WHERE id > '$imgid'?

 

because eventually some rows will probably get deleted so using php to get the row id could select rows that don't exist anymore..

[corbin]

SELECT * FROM table WHERE id > {$prev} AND id != {$num_clicked} LIMIT 4;

[acidglitter]

ah thanks! i changed it a little to

 

$start=$imgid+3;

SELECT id FROM table WHERE id < '$start' AND id != '$imgid' ORDER BY id DESC LIMIT 4

 

and its working perfectly now. thanks for the help!

[acidglitter]

i want to have a first and next link as well.. does anyone know how i could get the row id's with a mysql query?

 

like before i don't want to just put $imgid+1 for like previous because eventually some rows will probably get deleted..

[Maq]

You should really check out the pagination tutorial but...

 

//for next pass
$start += 1;
//for prev pass
$start -= 1;

 

like before i don't want to just put $imgid+1 for like previous because eventually some rows will probably get deleted..

 

I think you meant $imgid-1 for previous.  Anyway, why would they eventually get deleted?  You should be checking if the rows exist so you don't get any errors but, for example, if you reach row 0 you should not show the "prev" link.  Same with the last row, you shouldn't show "next". 

 

Am I understanding you correctly?