paginating results from two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

paginating results from two tables

Post 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?
Post Reply