Logic help on jumping around in a DB

[mcpeekj]

I have a photography site pulling photo info from a db. All the photo references are in 1 table, with each record having a category. So when you're viewing a photo, there are next and previous buttons. I can't figure out a statement to make it check for the next occurrence of that category. I had it setup to just take the picture ID + 1, but they won't always be incremental in the same category. And then after that's figured out, I need to figure out how to show/hide the next and previous buttons based on whether there are or aren't more records in that category. Here's what I've got so far. Obviously, the problem lies with the previous/next logic. Any help would be appreciated

SQL = "SELECT * FROM (tblMaster INNER JOIN tblLocation ON tblMaster.LocationLink=tblLocation.LocationID) INNER JOIN tblCategories ON tblMaster.CategoryLink=tblCategories.CategoryID WHERE tblMaster.ID=" & id & "AND tblMaster.CategoryLink=" & cat
rs.Open SQL, connStr, adOpenStatic, adLockReadOnly
	response.write ("<img src=../images/" & rs("CategoryFull") & "Name.gif><br><br>")
Response.Write "<table border=0 cellpadding=2 cellspacing=0" & vbcrlf
      Response.Write "<tr><td>"

	response.write "<font size=4 color=cccccc face=arial><b>Title:   </b>"
	response.write rs("Name")
	response.write "</font></b></td></tr><tr><td colspan=2>"
      Response.Write  "<img src=../images/" & rs("CategoryFull") & "/" & rs("FileName") & ".jpg class=image>"
         Response.Write "</td></tr><tr><td width=50% align=left>"
         Response.Write "<font color=cccccc face=arial><b>Date taken: </b>"
         Response.Write rs("Date")

         Response.Write "</td><td align=right>"
         Response.Write "<font color=cccccc face=arial><b>Location: </b>"
         Response.Write rs("LocationFull")
response.write "</td></tr><tr><td>"

IF rs("ID")-1 = 0 then
	response.write ""
	else
	response.write "<a href=viewPhoto.asp?cat=" & cat & "&id=" & rs("id")-1 & ">"
	response.write "<img src=../images/back.jpg alt=Back border=0></a>"
	end if
	response.write "</td><td align=right>"

IF rs.EOF then
	response.write ""	

	else
	response.write "<a href=viewPhoto.asp?cat=" & cat & "&id=" & rs("id")+1 & ">"
	response.write "<img src=../images/next.jpg alt=Next border=0></a>"
	end if

       Response.Write "</td></tr></table>"
      rs.Close
      Set rs = Nothing

 

[Sheco]

So the next ID has the following attributes:
1) Category is the same as current ID
2) ID is greater than current ID
3) The minimum ID amoung the group of IDs that meet the 1st and 2nd attribute.

Is that correct?

 

[mcpeekj]

Correct on all 3 counts.

After thinking more about this, I need to filter my result set on the category, then just show the photo with the matching ID, but then I get lost in the logic. How can I pull records based on category AND only display the correct photo?

Then on top of that, how do I move to the next record in the correct category? But I'm sure you're about to school me in that

 

[Sheco]

You can use a subquery to get the ID of the photo that matches those 3 conditions... You join on the subquery to pull the full matching record.

Assuming all of the fields that you need to prepare the photo page can be found in tblMaster, and assuming that you already have the ID and category of the current photo. You can find the next on in the sequence like this:

SELECT 
  Ma.* 
FROM 
  tblMaster Ma 
    INNER JOIN (SELECT 
                  Min(M.ID) as 'ID'
                FROM 
                  tblMaster as M
                WHERE 
                  M.CategoryLink = @cat  AND
                  M.ID > @id
               ) Sub
       ON Ma.ID = Sub.ID

The previous one is just the opposite:

SELECT 
  Ma.* 
FROM 
  tblMaster Ma 
    INNER JOIN (SELECT 
                  Max(M.ID) as 'ID'
                FROM 
                  tblMaster as M
                WHERE 
                  M.CategoryLink = @cat  AND
                  M.ID < @id
               ) Sub
       ON Ma.ID = Sub.ID

In either case if you come up with Null then there is no next/previous record because the current one is the first or last within the category.

Note: You may need to fiddle with the syntax of this SQL to match your particular database version.