next entry link problem

cbesh2

I'm trying to create a couple of links that displays the next/previous entry.

I understand that if I am do do this in simplest form, I would do something like the following (to create a NEXT link):

SELECT *
FROM Products
WHERE prodID > '$ID'
LIMIT 1

$ID is the current prodID that is being displayed. However, my query is a little more complicated. I need to order my results by type. Simply adding "ORDER BY type" will not work since the WHERE clauses gives us a references point on where to start looking for the results. If I keep the WHERE clause, it will sort the results by prodID which is throwing off the order.

Any ideas on how to fix this?

sagee

sorry, dont see the problem,
can you post your query & describe why the ORDER isnt good enough

cbesh2

Well, before tweaking my query, I was getting an unsual order -- often skipping some entries that should be displaying.

Now, after doing some changes I am getting an error: "Column 'prodID' in where clause is ambiguous". Because of my error I am now unable to view the results. I'm not sure what I am doing wrong.

Here is the code that I am using:

SELECT * 
	FROM Products
	INNER JOIN Product_Theme_Series_Relationship ON Products.prodID=Product_Theme_Series_Relationship.prodID
	INNER JOIN Theme ON Product_Theme_Series_Relationship.themeID=Theme.themeID
	INNER JOIN Series ON Product_Theme_Series_Relationship.seriesID=Series.seriesID
	INNER JOIN Type ON Products.typeID=Type.typeID
	WHERE Products.prodID > '$ID' AND
	Products.current = 'yes'
	ORDER BY Series.series ASC, Theme.theme ASC, Products.description ASC
	LIMIT 1

sagee

sorry, cant see the problem.
the ambiguous error means there is something duplicate with the same name.

what happens when you remove "Products.prodID > '$ID'" & just leave the current term?

i see more without the db structure.
if you like you can post the tables code so i could take a look.
Sorry.

cbesh2

Thanks for your help!

I fixed the ambiguous error.

When removing the Products.prodID > '$ID' clause, the next link displays the last entry. The previous link displays the first entry. Nothing in between.

Here it the current query I'm using for the NEXT link:

SELECT * 
	FROM Product_Theme_Series_Relationship
	INNER JOIN Products ON Product_Theme_Series_Relationship.prodID=Products.prodID
	INNER JOIN Theme ON Product_Theme_Series_Relationship.themeID=Theme.themeID
	INNER JOIN Series ON Product_Theme_Series_Relationship.seriesID=Series.seriesID
	INNER JOIN Type ON Products.typeID=Type.typeID
	WHERE Products.prodID > '$ID' AND
	Products.current = 'yes' AND
	Type.type = '$ptype'
	ORDER BY Type.order ASC, Products.description ASC, Series.series ASC, Theme.theme ASC
	LIMIT 1

The PREV link is the same except I am using WHERE Products.prodID < '$ID' and DESC instead of ASC.

Here are the descriptions for my tables:

Products - prodID, description (name of the product), current (whether to display the product or not, using this instead of deleting entry)
Theme - themeID, theme (name of theme)
Series - seriesID, series (name of theme series)
Product_Theme_Series_Relationship - relationshipID, prodID, themeID, seriesID (this table is used to relate the Products, Theme and Series tables together)
Type - typeID, type (type of product)