SqlBuilder Tutorial?

MySchizoBuddy

Can anyone write a nice tutorial of how to use the SqlBuilder class.
With a couple of important examples for a newbie.
Is it just for getting result from the database. or u use it to create new tables and new fields as well. Looking at Attachments extension. Jazzman doesn't use SqlBuilder to create a new table.

Mr Do

neither do i in the Dojo Files extension. you can just use the php command : mysql_query

MySchizoBuddy

ur using SqulBuilder sparingly
$SB->AddWhere('t', 'CategoryID', '', $CurrentBlock, '<>', 'and', '', 0, 0);
u obviously understand what SqlBuilder is for.

Mr Do

thats actually not my code, its from the Category Hider Add On - I just stole those lines from there, because its feature is essential mine, i feel.

what it does is modify the sql call that will be called to accumulate the discussions for the front page by blocking out the category.

MySchizoBuddy

u mean Category Filter extension

Mr Do

nope. I mean Category Hider by Wallphone

MySchizoBuddy

Wallphone profile doesn't mention a Category Hider extension

Mr Do

never the less, I have this addon installed on my forum. let me show u it in its entirety. and btw, are you sure your not getting stuck on the wrong point here?
<?php /* Extension Name: Category Hider Extension Url: http://www.lussumo.com/addons Description: Hides certian categories from the discussion grid Version: 1.0 Author: WallPhone Author Url: http://wallphone.com/ */ // CONFIGURATION: Edit the two array() statements in the code to reflect the category IDs that should be blocked. // The code blocks categories 5 and 6, but if you wanted to instead block categories 1, 3, and 6, then use // array('1', '3', '6') in place of the other two array functions. if ( ('index.php' == $Context->SelfUrl) && !in_array(ForceIncomingString('CategoryID', ''), array('10')) ) { function Category_HideFromDiscussions(&$DiscussionManager) { $SB = &$DiscussionManager->DelegateParameters['SqlBuilder']; foreach ( array('10') as $CurrentBlock ) { $SB->AddWhere('t', 'CategoryID', '', $CurrentBlock, '<>', 'and', '', 0, 0); } } $Context->AddToDelegate('DiscussionManager', 'PostGetDiscussionBuilder', 'Category_HideFromDiscussions'); $Context->AddToDelegate('DiscussionManager', 'PreGetDiscussionCount', 'Category_HideFromDiscussions'); } ?>

Jazzman

The SqlBuilder makes it easy to change the prefix/table/column-names without having to rewrite your queries. Also it was intended to make it independent which database system you use (MySQL, MS SQL, etc). Therefor I suggest you always use the SqlBuilder class.

It's quite simple:

First, create an instance of the SqlBuilder object:
$s = $Context->ObjectFactory->NewContextObject($Context, 'SqlBuilder');
Next, select the main table you want to use and give it an alias:
$s->SetMainTable('Category', 'c');
Then you want to select a few fields (you can select mutiple fields by using an array):
$s->AddSelect(array('CategoryID', 'Name'), 'c');
Maybe you want to add a where-clause:
$s->AddWhere('c', 'CategoryID', '', '1', '=');

You can also do joins, orderby's and limit. Just take a look a the SqlBuilder class what functions are available and which parameters you can use.