DBManagerFactory

Overview

Provides an overview of the DBManagerFactory class that will allow you to generate the appropriate manager for the database you are using.

Instantiating a DB Object

To use the DB object you should use:

$GLOBALS['db']

If you should need to manually instantiate the DB object, you can use the DBManagerFactory class’s getInstance() method. This method will create a reference to the DB object for the instance.

$db = DBManagerFactory::getInstance();

Querying The Database

Retrieving Results

To query the database looking for a result set, you will use the query() and fetchByAssoc() methods. The query() method will retrieve the results while the fetchByAssoc() method will allow for you to iterate through the results. An Example is below:

$sql = "SELECT id FROM accounts WHERE deleted = 0";

$result = $GLOBALS['db']->query($sql);

while($row = $GLOBALS['db']->fetchByAssoc($result) )
{
    //Use $row['id'] to grab the id fields value
    $id = $row['id'];
}

Retrieving a Single Result

To retrieve a single result from the database, such as a specific record field, you can use the getOne() method.

$sql = "SELECT name FROM accounts WHERE id = '{$id}'";

$name = $GLOBALS['db']->getOne($sql);

Limiting Results

To limit the results of a query, you can add a limit to the sql string yourself or use the limitQuery() method. An example is below:

$sql = "SELECT id FROM accounts WHERE deleted = 0";
$offset = 0;
$limit = 1;

$result = $GLOBALS['db']->limitQuery($sql, $offset, $limit);

while($row = $GLOBALS['db']->fetchByAssoc($result) )
{
    //Use $row['id'] to grab the id fields value
    $id = $row['id'];
}​

Generating SQL Queries

To have Sugar automatically generate SQL queries, you can use some methods from the bean class.

Select Queries

To create a select query you can use the create_new_list_query() method. An example is below:

$bean = BeanFactory::newBean($module);

$order_by = '';
$where = '';
$fields = array(
    'id',
    'name',
);

$sql = $bean->create_new_list_query($order_by, $where, $fields);

Count Queries

You can also run the generated SQL through the create_list_count_query() method to generate a count query. An example is below:

$bean = BeanFactory::newBean('Accounts');
$sql = "SELECT * FROM accounts WHERE deleted = 0";
$count_sql = $bean->create_list_count_query($sql);