[Q] Does the VB database keep track of "Most Recent Threads" not "posts"

[downloadsuk]

Hi,

I'm doing some modding of my forum home page - as I'm modding the forum to be a Q&A site, I want to be able to have the forum home page list the most recent "questions" (i.e. the most recent topics / threads that have been opened) AND NOT the most recent posts.

Short of doing a query on the whole database, is there a list I can pull back of most recent threads or would I have to mysql (this is pseudo code here)

SELECT ( first 20 of) threads FROM thread.table WHERE thread.start <= today

?

I mean, if I did it this way, surely I'd have to bring back every single thread, sort it by date, and then do some post processing in PHP to pick out the first 20 from the list. If my forum gets big, this is a hefty query to be doing. And if posts dates stretch across multiple months, I don't really want to have to do a qualifying cut-off like (less than 30 days).

Can anyone help me out here? Maybe with the appropriate SQL query or even some ideas about the way the vb database stores the thread meta information?

[Lynne]

What exactly are you trying to post on your home page? Just a link to the 20 most recent threads? You can just use javascript for that.

See these threads for methods to pull threads via javascript:
[HowTo] Display your latest threads on an external page using an RSS2 feed (http://www.vbulletin.com/forum/showthread.php?t=159044)
[HowTo] Display your latest threads on an external page using an XML feed (http://www.vbulletin.com/forum/showthread.php?t=158934)
[HowTo] Display your latest threads on an external page using an RSS feed (http://www.vbulletin.com/forum/showthread.php?t=158916)

[vbresults]

MySQL 5.0 Reference Manual: SELECT syntax. (http://dev.mysql.com/doc/refman/5.0/en/select.html)

SELECT * FROM thread ORDER BY dateline DESC LIMIT 0, 20

[downloadsuk]

MySQL 5.0 Reference Manual: SELECT syntax. (http://dev.mysql.com/doc/refman/5.0/en/select.html)

SELECT * FROM thread ORDER BY dateline DESC LIMIT 0, 20


Thanks, you got the jist of what I'm after. a bientot good sir.

--------------- Added 1301191696 at 1301191696 ---------------

just to confirm, the table value thread.dateline is the start date for the thread right?

Why choose "dateline" as a table value? I mean, why confuse people and not just have "created_date" or something? Who the hell says "dateline"?

[vbresults]

The schema has been verbose for a while now; not saying it's good (it is not), but because vB is a giant bowl of spaghetti code and third-party add-ons use the column name "dateline", it would not be very easy to change it.

Also, vBulletin is quite old and making column names descriptive/friendly is a more modern thing.