[thelist] Gallery3 - Using SQL queries

[Anthony Baratta]

On 3/20/2010 12:55 PM, Todd Richards wrote:
>
> Thanks for your response.  What I am looking at is how to do a "previous"
> and "next" link on the page with the image.  I'm getting the image ok but
> I'm not really super sure how to say to the database "from the images in
> this album, get the next image in the table" (or previous image).

If you are ordering my Record ID, you might be able to get away with this:

First grab the target image info:

select
       Image.Id
     , Image.Parent_Id
     , Image.Relative_path_cache
from <table> as Image
join <table> as Album on
     Image.Parent_Id = Album.Id
where
     Image.Name = @Name
     AND
     Album.Name = @Album


Store Image.Id in @SelectedID
Store Image.Parent_Id  in @SelectedParentID

Select
	Max(Id) as Previous
 From <table>
Where Id < @SelectedID
And Parent_Id = @SelectedParentID

Select
	Min(Id) as Next
 From <table>
Where Id < @SelectedID
And Parent_Id = @SelectedParentID

If any of the above come back null, you'll need one of the following:

[If Min = Null]
Select
	Max(Id) as Previous
 From <table>
And Parent_Id = @SelectedParentID

[If Max = Null]
Select
	Min(Id) as Next
 From <table>
And Parent_Id = @SelectedParentID




--
Anthony Baratta

"What it lies in our power to do, it lies in our power not to do." 
---Aristotle