Forum Page Nubmers
-
I have this forum, as some of you know that I'm coding. Luckly, this forum was not designed by previous owner and only has the WTF's that I put in it (which are hopefully few or none(not likely)). I'm at a loss as to how to quickly get the topics or replies and then display them at 50 per page. Currently I have make it count num of rows, then set up page selection options, but thats not the problem. I want to know how to quickly get, say page 3's topics, which would be the last 100-150 topics updated. How can I make a query to get that? I get the first page by SELECT * FROM forumTopics ORDER BY lastUpdate ASC LIMIT 50, the closest thing I can think of is LIMIT pageNum*50 and then looping through all the rows until I get to the last 50 returned. I know that can't be the fastest way to do this. Does anyone have any ideas?
-
use the second parameter of the LIMIT clause
LIMIT start, length
for example, SELECT (...) LIMIT 150, 50 gives 50 results starting from the 150th
-
Okay, thank you, I was unaware that LIMIT had more than one param, I only learned SQL from PHP and MySQL for Dummies and from my Java II class when I used JDBC. Looks like I need a SQL book.
-
A book can't be a bad idea, but if you just want to lookup syntax and possibilities, the mysql documentation should be enough.
-
And it remains a question whether a generic SQL book contains all those syntax differences of the various SQL flavours.
MSSQL has TOP, and not LIMIT, for example.
-
As far as MSSQL goes, from v2005, it has the ROW_NUMBER function.
SELECT a.col1
FROM (SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) AS RowId FROM dbo.tablename) AS a
WHERE RowId > pageStart AND RowId < pageEndWould be nice to have LIMIT, though.
-
Normally dealings with MSSQL are done through ADO, and ADO is nice enough to handle paging.
I used to do some dev work in ASP 2.0 (notice: no .net), done in VBScript. When I finally got a job working in PHP I didn't look back except for ADO, which made dealing with databasing in general so much nicer...
Then again I've never connected to a MSSQL database via anything other than an MS language...