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.

Mysql query to SQLBuilder

ur help will result in some cool extensions being build. I know u want cool extensions right :P

I need to optimize 3 calls to the database with just one call. I know i have to use the JOIN statement for this. here are the 3 calls
$sql = $this->Context->ObjectFactory->NewContextObject($this->Context, 'SqlBuilder'); $sql->SetMainTable('Discussion','d'); $sql->AddSelect(array('Name','AuthUserID','Closed','DateCreated','CountComments' ), 'd'); $sql->AddWhere('d','DiscussionID','',$DiscussionID,'='); $result = $this->Context->Database->Select($sql, $this->Name, 'DiscussionStuff', 'An error occurred while listing the discussion information.'); $Discussion = $this->Context->Database->GetRow($result); $sql->Clear(); $sql->SetMainTable('User','u'); $sql->AddSelect('Name', 'u'); $sql->AddWhere('u','UserID','',$Discussion['AuthUserID'],'='); $result = $this->Context->Database->Select($sql, $this->Name, 'GetAuthorName', 'An error occurred while listing the discussion information.'); $rows = $this->Context->Database->GetRow($result); $Discussion['AuthUsername'] = $rows[0]; $sql->Clear(); $sql->SetMainTable('Comment','c'); $sql->AddSelect('Body', 'c'); $sql->AddWhere('c','DiscussionID','',$DiscussionID,'='); $result = $this->Context->Database->Select($sql, $this->Name, 'GetCommentBody', 'An error occurred while listing the discussion information.'); $rows = $this->Context->Database->GetRow($result);

Comments

  • lechlech Chicagoland
    It might be best to also explain why you're fetching all those rows for what purpose so there could be a little bit of context to what is meant to be built by those queries.
  • its for a permalink page So I need 1) Name of Discussion, I need to query the Discussion table for that 2) User ID and Username. Hence i need to query the User table to get the username 3) First Comment of the Discussion, so I need to query the Comments table as well Now the AuthUserID in the Discussions table is same as UserID in the User table. So they can be joined. DiscussionID is same in the Discussion table and Comment table, so they can joined as well
This discussion has been closed.