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

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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.