Page 1 of 1

paginating results from two tables

Posted: Tue Jan 11, 2005 9:56 am
by primate
Hi,

I'm trying to paginate the results of two queries from two different tables and getting very stuck (again :) ). The database is MS SQL. The script is a basic forum script for my intranet.

I have the following query to get round MS SQL's lack of a LIMIT clause which I am using succesfully to paginate in another script:

Code: Select all

$query = "SELECT Datetime,
		CONVERT(CHAR(19),Datetime) as Datetime, PostID, Username, Title, Replies FROM (
 SELECT TOP $limit * FROM (
    SELECT TOP $limitvalue * FROM Posts WHERE (ForumID = {$_GETї'ForumID']}) ORDER BY PostID
 ) AS newtbl ORDER BY PostID DESC
) AS newtbl2 ORDER BY PostID ASC";
Where $limit is the number of results per page.

Replies to the Posts returned from this query are stored in the Replies table. If 'Replies' is set to 1 (from the above query) I query the Replies table to get the date of the most recent reply and then list the threads in order of the thread with the most recent post/reply. This works fine til I started trying to paginate the results.

This is where I get stuck because the above query orders the results based on the PostID, so I can only order the results by reply date for the particular page of posts returned by the first query.

Does anyone have any ideas how to get around this, other than completely starting again?