Database Class¶

Vesthelm Engine has an abstract database layer that allows developers to easily implement database drivers such as MySQL driver (MySQL Class).

Calling the Database Class
Calling the Database Methods
SELECTing Data
INSERTing Data
UPDATEing Data
DELETEing Data
Specifying Columns
FROM Clause
WHERE Clause
GROUP BY Clause
LIMIT Clause
UNION Clause
DISTINCT Query Modifier
Executing Queries
Getting SQL String from Object

Calling the Database Class¶

$db = $this->select(array('b.*'))
            ->from(array('b' => 'bulletin_board_bulletins'))
            ->left_join(array('c' => 'bulletin_board_categories'), array('b.category_id' => 'c.id'))
            ->where(array('b.category_id', '>', 10))
            ->or_where(array('b.category_id', '=', 1))
            ->order(array('b.title', 'ASC'))
            ->limit(array(0, 10));

$db = $this->Db;

$db = $this->Database;

$db = db();

Calling the Database Methods¶

$this->update();

$this->Db->update();
$this->Database->update();

SELECTing Data¶

When you perform a SELECT query, a database object will be returned:

$db = $this->select(array('b.*'))
            ->from(array('b' => 'bulletin_board_bulletins'))
            ->left_join(array('c' => 'bulletin_board_categories'), array('b.category_id' => 'c.id'))
            ->where(array('b.category_id', '>', 10))
            ->order(array('b.title', 'ASC'))
            ->limit(array(0, 10));

To get all rows as array:

$all_rows = $db->all();

When many rows of data are returned you will wish to loop through the returned array and use the values returned for the fields. Vesthelm Engine uses MYSQL_ASSOC, which has MySQL using the field names in the array and not numbers. To access this array, then you will want to use the result array with a foreach loop:

if(!empty($all_rows))
{
    foreach($all_rows as $row)
    {
        echo $row['title'] . ' #' . $row['id'] . "\n";
    }
}

To get one row as array:

$row = $db->row();

if(!empty($row)){
    echo $row['title'] . ' #' . $row['id'];
}

INSERTing Data¶

insert() method makes the inserting of data into the database easier by correctly formatting the INSERT string and escaping the values being inserted. The insert() method accept the name of the table for the insert and an array containing the field names as keys with the values containing the data for those fields, and additional array containing the field names as keys with the values or with the MySQL functions:

$result = $this->insert(
    'bulletin_board_bulletins', //table (in extended Model Class table can be empty, and table will be Model $_table variable)

    //data
    array(
        'title' => 'Test',
        'description' => 'Test Description'
    ),

    //additional data
    array(
        'date' => 'NOW()'
    )
);

UPDATEing Data¶

update() method makes the updating of data in the database easier by correctly formatting the UPDATE string and escaping the values being inserted. Method update() accept the name of the table for the update, an array containing the field names as keys with the values containing the updated data for those fields, the WHERE clause for choosing which rows in the table to update, and additional array containing the field names as keys with the values or with the MySQL functions:

$result = $this->update(
    'bulletin_board_bulletins', //table (in extended Model Class table can be empty, and table will be Model $_table variable)

    //data
    array(
        'title' => 'Test',
        'description' => 'Test Description'
    ),

    //WHERE (id = $id AND status = active OR title = test)
    array(
        array('id', '=', $id), //AND status = active
        array('status', '=', 'active', 'OR'), //OR title = test
        array('title', '=', 'test')
    ),

    //additional data
    array(
        'date' => 'NOW()'
    )
);

DELETEing Data¶

delete() method for deleting data. It accepts a table name and a string or array containing the WHERE clause(s):

$result = $this->delete(
    'bulletin_board_bulletins', //table (in extended Model Class table can be empty, and table will be Model $_table variable)

    //WHERE (id = $id AND status = active OR title = test)
    array(
        array('id', '=', $id), //AND status = active
        array('status', '=', 'active', 'OR'), //OR title = test
        array('title', '=', 'test')
    )
);

Specifying Columns¶

In the first argument of the select() method, you can specify the columns to select from the respective table.

You can list the columns in a simple array of strings, or as an associative mapping of column alias to column name. If you only have one column to query, and you don't need to specify a column alias, you can list it as a plain string instead of an array.

You can specify the column name as `correlation_name`.`column_name`. Vesthelm_Database quotes each part individually. If you don't specify a correlation name for a column, it uses the correlation name for the table named in the current from() method.

Specifying columns:

//SELECT `b`.`id`, `b`.`title` FROM `bulletin_board_bulletins` AS `b`

$db = $this->select(array('b.id', 'b.title'))
            ->from(array('b' => 'bulletin_board_bulletins'));

Specifying columns containing expressions:

//SELECT `b`.`id`, LOWER(b.title) FROM `bulletin_board_bulletins` AS `b`

$db = $this->select(array('b.id', 'LOWER(b.title)'))
            ->from(array('b' => 'bulletin_board_bulletins'));

//SELECT `b`.`id`, (b.price + 10) AS `total` FROM `bulletin_board_bulletins` AS `b`

$db = $this->select(array('b.id', 
                        'total' => '(b.price + 10)')
                    )
            ->from(array('b' => 'bulletin_board_bulletins'));

//SELECT `b`.`id`, (b.price + 10) AS `total` FROM `bulletin_board_bulletins` AS `b`

$db = $this->select(array('b.id', 
                        'total' => $this->expr('b.price + 10'))
                    )
            ->from(array('b' => 'bulletin_board_bulletins'));

Quoting columns in an expression:

//SELECT `b`.`id`, (`b`.`price` + 10) AS `total` FROM `bulletin_board_bulletins` AS `b`
    
$db = $this->select(array('b.id', 
                        'total' => "($this->qval('b.price') + 10)")
                    )
            ->from(array('b' => 'bulletin_board_bulletins'));

FROM Clause¶

from() method specifies the table for query:

from() method:

//SELECT * FROM `bulletin_board_bulletins`

$db = $this->select('*')
            ->from('bulletin_board_bulletins');

You can also specify the correlation name/table alias for a table. Instead of a string, use an associative array mapping the correlation name to the table name. If your query joins more than one table, Vesthelm_Database generates unique correlation names based on the table names, for any tables for which you don't specify the correlation name.

Specifying a table correlation name:

//SELECT b.* FROM `bulletin_board_bulletins` AS `b`

$db = $this->select(array('b.*'))
            ->from(array('b' => 'bulletin_board_bulletins'));

WHERE Clause¶

Criteria for restricting rows of the result set using the where() method:

Multiple bool ops and ops example:

//SELECT * FROM `table` WHERE `field` = 'value' AND `field2` => 'value' OR `field3` <= 'value' OR NOT `field4` LIKE 'value' AND `field5` BETWEEN 'value' AND `field6` <> 'value'

$where = array(
    array('field', '=', 'value', 'AND'), //array('field', 'value', 'AND') or array('field', 'value') or array('field' => 'value') (with ops `=` only)
    array('field2', '>=', 'value', 'OR'),
    array('field3', '<=', 'value', 'OR NOT'),
    array('field4', 'LIKE', 'value', 'AND'),
    array('field5', 'BETWEEN', 'value', 'AND'),
    array('field6', '<>', 'value')
);

Multiple bool ops example (ops `=`):

//SELECT * FROM `table` WHERE `field` = 'value' AND `field2` = 'value' AND `field3` = 'value' OR `field4` = 'value'

$where = array(
    'field' => 'value',
    1 => 'AND', //'AND' (with ops `=` only)
    'field2' => 'value',
    3 => 'AND',
    'field3' => 'value',
    5 => 'OR',
    'field4' => 'value'
);

One unique bool ops example (ops `=`):

//SELECT * FROM `table` WHERE `field` = 'value' AND `field2` = 'value' OR `field3` = 'value'

$where = array(
    'field' => 'value', 'AND',
    'field2' => 'value', 'OR',
    'field3' => 'value'
);

$where = array(
    'field' => 'value',
    'AND' => '',
    'field2' => 'value',
    'OR' => '',
    'field3' => 'value'
);

GROUP BY Clause¶

In Vesthelm_Database, you can use the limit() method to specify the count of rows and the number of rows to skip. The first argument to this method is the desired count of rows (or can be an array("count of rows", "number of rows to skip")). The second argument is the number of rows to skip.

//SELECT `b`.* FROM `bulletin_board_bulletins` AS `b` JOIN `bulletin_board_categories` AS `c` ON `b`.`category_id` = `c`.`id` GROUP BY `b`.`category_id`

$db = $this->select(array('b.*'))
            ->from(array('b' => 'bulletin_board_bulletins'))
            ->join(array('c' => 'bulletin_board_categories'), array('b.category_id' => 'c.id'))
            ->group('b.category_id');

LIMIT Clause¶

//SELECT * FROM `bulletin_board_bulletins` LIMIT 0, 10

$db = $this->select(array('b.*'))
            ->from(array('b' => 'bulletin_board_bulletins'))
            ->limit(0, 10);

$db = $this->select(array('b.*'))
            ->from(array('b' => 'bulletin_board_bulletins'))
            ->limit(array(0, 10));

UNION Clause¶

You can build union queries with Vesthelm_Database by passing an array of Vesthelm_Database or SQL Query strings into the union() method. As second parameter you can pass the "UNION" or "UNION ALL" strings to specify which type of union you want to perform (default "UNION").

$sql1 = $this->select();
$sql2 = "SELECT ...";
 
$db = $this->select()
    ->union(array($sql1, $sql2))
    ->order('id');

DISTINCT Query Modifier¶

The distinct() method enables you to add the DISTINCT keyword to your SQL query. In the first argument of the distinct() method, you can specify bool (true to enable or false to disable using DISTINCT).

//SELECT DISTINCT `b`.`title` FROM `bulletin_board_bulletins`

$db = $this->select('b.title')
            ->distinct()
            ->from(array('b' => 'bulletin_board_bulletins'));

Disables using DISTINCT:

$db = $this->select('b.title')
            ->distinct(false) //to enable: true or nothing
            ->from(array('b' => 'bulletin_board_bulletins'));

Executing Queries¶

This section describes how to execute the query.

Object query:

//SELECT * FROM `bulletin_board_bulletins`

$db = $this->select('*')
            ->from('bulletin_board_bulletins');

$result = $db->query();// or $this->query();

$rows = $db->fetch($result);// or $this->fetch();

Plain query:

$sql = 'SELECT * FROM `bulletin_board_bulletins`';
                
$result = $this->query($sql);
    
$rows = $this->fetch($result);

Getting SQL String from Object¶

If you need access to a string representation of the SQL query corresponding to the Vesthelm_Database object, use the get_query() method:

$db = $this->select('*')
            ->from('bulletin_board_bulletins');

$sql = $db->get_query(); // or $this->get_query()

echo "{$sql}\n";

//The output is the string: SELECT * FROM `bulletin_board_bulletins`