Previous and Next via SQL query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bensin
Forum Newbie
Posts: 1
Joined: Tue Aug 27, 2002 5:27 pm

Previous and Next via SQL query

Post by bensin »

I'm creating a gallery of pictures. The picture id are not in any order.
What i would like to do is to do a query on the db and that brings back 3 id's, pic1 and pic2. your GID will be set in the URL ex. http://test.com?gid=10
I need to display the one that matches the gid the one before and the one after.
This is the code that i have been using but's isn't working.

Code: Select all

$sql = "SELECT id,pic1,pic2 FROM gallery ORDER by id";
$result = mysql_query($sql,$db);

while ($r = mysql_fetch_array($result)) {
extract($r, EXTR_PREFIX_ALL, "IN");



if($IN_id == ($gid-1)) {
$presm = $IN_pic2;
$preid = $IN_id-1; }


if($IN_id == $gid)
$bigpic = $IN_pic1;

if($IN_id == ($gid+1)) {
$postsm = $IN_pic2;
$postid = $IN_id+1; }

 }
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What does 'isn't working' mean?

Mac
User avatar
9902468
Forum Commoner
Posts: 89
Joined: Thu Jun 06, 2002 6:39 am
Location: Europe

Post by 9902468 »

Few things:
Gid = the picture id that you want to display?
ok search db like this

Code: Select all

$sql = "SELECT id, picture FROM gallery WHERE id = ".$gid." or id = ".($gid + 1)." or id = ".($gid - 1)."
This returns three pictures , one before and one after gid, also this doesn't create error if there is no picture with that id. (My code is not good since it assumes that there are no "holes" in id number chain...)

One possibility is to get all id's from gallery, and find the closest next and previous id's using array functions and (sql)sorting. And then get the three pictures.

-9902468
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Probably better to use an IN within the where clause

$gidPlus = $gid + 1;
$gidMinus = $gid - 1;

$sql = "select * from table where id IN ($gid,$gidPlus,$gidMinus)";
User avatar
gotDNS
Forum Contributor
Posts: 217
Joined: Tue May 07, 2002 5:53 pm
Location: West Chester, PA

Post by gotDNS »

*ponders the 2 previous replies...*...*supposes that may be simpler than the way I'm doing it....* *gr.*
Post Reply