Forum Page Nubmers

malfist

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?

aikii

use the second parameter of the LIMIT clause

LIMIT start, length

for example, SELECT (...) LIMIT 150, 50 gives 50 results starting from the 150th

malfist

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.

aikii

A book can't be a bad idea, but if you just want to lookup syntax and possibilities, the mysql documentation should be enough.

dhromed

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.

RayS

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 < pageEnd

Would be nice to have LIMIT, though.

whereiseljefe

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...