Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

SQL Query...

edited May 2006 in Vanilla 1.0 Help
Anyone fancy writing me an SQL Qeury to pull the 10 most recently updated discussions out of the database? and also the 10 most recent discussions in a single category. Pretty please???


  • SELECT DiscussionID FROM LUM_Discussion ORDER BY DateLastActive LIMIT 10;

    SELECT DiscussionID FROM LUM_Discussion WHERE CategoryID='$CategoryID' ORDER BY DateLastActive LIMIT 10;

    of course, $CategoryID is the numeric ID for the category you're looking for. (You can find those in LUM_Category)

    and if you want more than just the DiscussionID, you'll want to change that too.
  • MarkMark Vanilla Staff
    dknowles forgot to block hidden discussions and hidden categories.

    The easiest way to make sure that all of the flags are properly checked is to use the existing code in the DiscussionManager object. You can do it like this:
    $dm = $Context->ObjectFactory->NewContextObject($Context, "DiscussionManager");
    $DataSet = $dm->GetDiscussionList($RowsPerPage, 1);
    $d = $Context->ObjectFactory->NewObject($Context, "Discussion");
    while ($Row = $this->Context->Database->GetRow($DataSet)) {
    	echo($d->Name." by ".$d->AuthUsername."<br />");
  • hmmm I have no idea what that all means, I suck at OO programmming I'm basically looking to write a php file that is run by a cron job every 20 minutes so I can have a list of the latest topics on the front page of my friends new site.
  • just include that code into your friends site and it will run realtime when people visit the page?
  • do as mini says
  • N.B, i assume if you want to include any more information, just change: echo($d->Name." by ".$d->AuthUsername."<br />"); to: echo($d->Name." by ".$d->AuthUsername.", ".$d->Fieldname."<br />"); Where fieldname is the name of the field you want the information for.
  • pfft. hidden stuff. bah.

    i've been doing queries for the stats extension all day. kinda forgot about those. i should add them to the stats, too.
  • I can't seem to see what field in the LUM_Discussion table shows that the discussion is hidden? I'm looking through the cleanup extension, but still don't get it. It must have something to do with that "ObjectFactory" I see mentioned in there.
  • 'Active' is the column you're looking for.
  • Oh jeeze, I'm an idiot. Thanks!
This discussion has been closed.