DB Queries

Intro

Seagull uses PEAR::DB as its abstraction layer which gives the developer a considerable amount of flexibility to migrate SQL across DB vendors. It also makes querying the database quite a bit easier/cleaner than using just the PHP native SQL functions.

Overview

A few of the main things to keep in mind with Seagull's use of PEAR::DB are:

  • sequences must always be used, forget about MySQL's auto-increment feature, this will make it much more difficult to move your SQL to another DB, say Postgres, in the future
  • Seagull overrides the standard PEAR::DB way of getting the next id from a given sequence. The API is the same, however we put all sequences in a single table to avoid huge numbers of tables in the DB (Choose MySQL_SGL at installation). You can however revert back to the standard PEAR approach to MySQL sequences, this is left up to the user
  • PEAR::DB gives three choices for the format of returned results: DB_FETCHMODE_ORDERED, DB_FETCHMODE_ASSOC, DB_FETCHMODE_OBJECT - Seagull uses the object mode by default however you can override this at any time
  • Seagull allows you to query multiple databases from a single 'page', just pass a unique $dsn to each SGL_DB::singleton() instance.
  • all queries (unless multiple DBs are used) are run against a single database resource, this is enforced by using a Singleton connection resource

Returning Appropriate Data Structures

Array of Objects

the following query:

    $dbh = & SGL_DB::singleton();
    $query = '
        SELECT
            b.block_id, b.name, b.title, b.title_class, 
            b.body_class, b.is_onleft
        FROM    block b, block_assignment ba
        WHERE   is_enabled = 1
        AND     b.block_id = ba.block_id
        AND     ( ba.section_id = 0 OR ba.section_id = ' . 
                $this->_currentSectionId . ' )
        ORDER BY blk_order
    ';
    $aResult = $dbh->getAll($query);

returns the following data structure

 Array
(
    [0] => stdClass Object
        (
            [block_id] => 10
            [name] => SampleRightBlock1
            [title] => Sample Right Block
            [title_class] => 
            [body_class] => 
            [is_onleft] => 0
        )
    [1] => stdClass Object
        (
            [block_id] => 1
            [name] => SiteNews
            [title] => Site News
            [title_class] => 
            [body_class] => 
            [is_onleft] => 1
        )
    [...] 

Single Value

the following query:

    $dbh = & SGL_DB::singleton();
    $query = "  SELECT label 
                FROM " . $conf['table']['category'] . "
                WHERE category_id = '$id'";  
    $result = $dbh->getOne($query);

returns the following data structure

myLabel

Single Row

the following query:

    $dbh = & SGL_DB::singleton();
    $query = "
        SELECT  usr_id, user_group_id
        FROM " . $conf['table']['user'] . "
        WHERE   username = " . $dbh->quote($username) . "
        AND     passwd = '" . md5($password) . "'
        AND     is_acct_active = 1
        AND     user_group_id <> " . SGL_UNASSIGNED;
    $aResult = $dbh->getRow($query, DB_FETCHMODE_ASSOC);

returns the following data structure

Array
(
    [usr_id] => 2
    [user_group_id] => 2
)

Array of Arrays

the following query:

    $dbh = & SGL_DB::singleton();
    $query = "  SELECT category_id, label 
                FROM " . $conf['table']['category'] . "
                WHERE parent = $id";
    $result = $dbh->query($query);
    $count = 0;
    $aChildren = array();
    while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $aChildren[$count]['category_id'] = $row['category_id'];   
        $aChildren[$count]['label'] = $row['label'];
        $count++;
    }

returns the following data structure

Array
(
    [0] => Array
        (
            [category_id] => 1
            [label] => Sample Category
        )
    [1] => Array
        (
            [category_id] => 4
            [label] => General
        )
    [2] => Array
        (
            [category_id] => 115
            [label] => Javascript tutorial
        )
    [3] => Array
        (
            [category_id] => 118
            [label] => Planets
        )
    [4] => Array
        (
            [category_id] => 300
            [label] => Continents
        )
)

Hash

the following query:

    $dbh = & SGL_DB::singleton();
    $query = "
         SELECT  i.item_id,
                 ia.addition
         FROM    item i, item_addition ia, item_type it, item_type_mapping itm
         WHERE   ia.item_type_mapping_id = itm.item_type_mapping_id
         AND     it.item_type_id  = itm.item_type_id
         AND     i.item_id = ia.item_id
         AND     i.item_type_id = it.item_type_id
         AND     itm.field_name = 'title'
         AND it.item_type_id  = '5'         /* Static Html Article */
         AND i.status  > " . SGL_STATUS_DELETED . "
         ORDER BY i.last_updated DESC
    ";
    $res = $dbh->getAssoc($query);

returns the following data structure

Array
(
    [1] => Content Reshuffle
    [3] => Little
    [6] => Mary
    [11] => Had a Lamb
)

Prepared Queries

Prepared queries can save you a lot of time, for example:

    $dbh = & SGL_DB::singleton();
    $sth = $dbh->prepare("  UPDATE " . $conf['table']['user'] . "
                            SET user_group_id = $gid
                            WHERE usr_id = ?");
    foreach ($aUsers as $uid => $username) {
        //  if attempt to remove admin (uid = 1), silently ignore
        if ($uid == 1) {
            continue;
        }
        $dbh->execute($sth, $uid);
    }

Object Relational Mapping

with DB_DataObject

Many PEAR developers prefer to use the DataObject library which abstracts away the SQL into an object interface, there are many tutorials on the subject, but a short example would be:

    $oUser = & new DataObjects_Usr();
    //  get limit and totalNumRows
    $totalNumRows = $oUser->count();
    $limit = $_SESSION['prefs']['resPerPage'];
    $oUser->orderBy($input->sortBy . ' ' . $input->sortOrder);
    $oUser->limit($input->from, $limit);
    $oUser->orderBy('date_created DESC');
    //  execute query
    $numRows = $oUser->find();
    $aUsers = array();
    if ($numRows > 0) {
        while ($oUser->fetch()) {
            $oUser->getLinks('link_%s');
            $aUsers[] = clone($oUser);
        }
        return $aUsers;
    }

Checking for DB errors

If an error occurs, PEAR::DB will return an error Object. You can easily check for it like:

// $aRes is the result of the query
if (!PEAR::isError($aRes)) {
    return $aRes;
} elseif (PEAR::isError($aRes, DB_ERROR_NOSUCHTABLE)) {
    SGL::raiseError('You have a Seagull database with no tables ...',
        SGL_ERROR_NODATA, PEAR_ERROR_DIE);
} else {
    SGL::raiseError('Unknown DB error occurred, pls file bug',
        SGL_ERROR_NODATA, PEAR_ERROR_DIE);
}