Database

Contents...

Database manipulation classes represent the "Model" layer as defined in MVC. Webasyst framework contains abstract class waModel for the implementation of database models. Description classes for entities used in apps must extend class waModel. Model class files must be located in subdirectory lib/models/ inside the main app directory.

A model description must contain only one required field: the app's table name as a value of the $table variable. Example of a model description file:

<?php

class blogModel extends waModel
{
    protected $table = 'blog';
}

A model is initialized using the syntax shown below:

$model = new blogModel();

In the above sample blog is an example of an app identifier (APP_ID). After initialization, a model retrieves the description of the specified database table (using an SQL query of the form DESCRIBE $table) and saves it in cache. At this step the table's primary key as well as table fields and their types are defined.

In the source code of controllers and actions any interaction with the database is carried out using only app model classes:

// creating a model instance to retrieve data from the database
$model = new guestbookModel();


// retrieving guestbook records from the database
$records = $model->order('datetime DESC')->fetchAll();

Class waModel contains several methods for automatic generation of SQL queries which allow the source code of an app to be independent of the specific kind of a DBMS used on the server.

SQL query generation methods

SELECT queries

// Retrieve a record by its primary key value
$model->getById($id);

// Retrieve a record by of one of its fields' values
$model->getByField('name', $name);  // returns the first found record

// If all records matching the search parameters should be returned like in the example above,
// then the third argument should be added: true
$model->getByField('name', $name, true);

// Several fields' values can be specified
// they will be combined in the generated SQL query using AND operator
$model->getByField(['name' => $name, 'contact_id' => $contact_id]);

// If it is necessary to select records by several values of a single field,
// then an array should be specified instead of a variable, in this case the resulting SQL query
// will contain IN operator
$model->getByField('name' , [$name1, $name2]);

// Aggregate queries of the form SELECT COUNT(*) ... WHERE ... are generated by method countByField
// Calling arguments are the same as for getByField
$model->countByField($field, $value);
$model->countByField(['name' => $name, 'contact_id' => $contact_id]);
$model->countByField('name' , [$name1, $name2]);

UPDATE queries

// Update a record by the primary key value
$model->updateById($id, $data); // $data is an associative array of the values to be updated
$model->updateById($id, ['field_1' => $field_1, 'field_2' => $field_2]);

// Method updateByField is similar to getByField,
// it accepts an array of the values to be updated as the third argument
$model->updateByField('contact_id', $contact_id, ['published' => true]);

DELETE queries

// There are the following two DELETE methods similar to those for UPDATE queries:
$model->deleteById($id);
$model->deleteByField($field, $value);

INSERT and REPLACE queries

// Both methods affect only one record

$model->insert($data); // if auto_increment is enabled, the method will return the id of the inserted record
$model->replace($data);

Execution of directly written SQL queries

The framework has two basic methods for executing SQL queries: query and exec. As the first argument they accept an SQL query, and an optional array of values for placeholders as the second argument.

Example:

$model->query("SELECT * FROM " . $this->table . " WHERE id = i:id", ['id' => $id]);

Notes:

  • i:id is a named placeholder where i denotes the integer data type (it means that the value retrieved from an array will be converted to an integer type);
  • id is a key in the array of values;
  • ['id' => $id] is the array of values.

Available data types for placeholders:

  • 's': String
  • 'i': Integer
  • 'b': Boolean
  • 'f': Float/Double

If no data type has been specified for a placeholder (e.g., if you use only :id in an SQL query, then the String type is used by default. Use of placeholders makes the creating of SQL queries easier and, which is far more important, eliminates the possibility of writing code prone to SQL injection vulnerabilities.

Using placeholders is not a requirements; however, if they are not used, a developer is responsible for correct use of data in an SQL query. In this case model method escape may be useful, which escapes text strings (if DBMS MySQL is used, it works exactly like PHP function mysql_real_escape_string):

$model->query("SELECT * FROM " . $this->table . " WHERE id = " . (int)$id);
$model->query("SELECT * FROM " . $this->table . " WHERE name LIKE '" . $this->escape($name) . "'";

The only difference between methods query and exec is in the result which they return.

Read more about different types of placeholders in the waModel class description ›

Method query($sql, $params = null)

This method executes an SQL query and returns the result object. The type of the returned object depends on the query type:

  • for SELECT queries: waDbResultSelect
  • for INSERT queries: waDbResultInsert
  • for UPDATE queries: waDbResultUpdate
  • for DELETE queries: waDbResultDelete

SELECT queries

An object of type waDbResultSelect is returned, which can be used in a foreach cycle, for instance. Examples of working with waDbResultSelect:

$result = $model->query("SELECT * FROM " . $this->table . " WHERE text LIKE '%test%'");

// Retrieve the number of items in the result (similar to function mysql_num_rows)
$result->count();

// Access each returned item in the result:
foreach ($result as $row) {
    ...
}

// Retrieve all result items as an array
$data = $result->fetchAll();

// If a field name is specified as the first argument for method fetchAll,
// then an associative array is returned with the specified field's values as array keys
$data = $result->fetchAll('id');

// If the result is expected to return only one value or if only one returned value is necessary,
// the following methods will be useful:

// similar to mysql_fetch_assoc
$result->fetch();

// executes fetch and returns a value by the specified key
// or the first array item's value, if no key is specified

$result->fetchField('id');

// here is how you can retrieve the result of a COUNT query
$n = $model->query("SELECT count(*) FROM " . $this->table . " WHERE ...")->fetchField();

DELETE and UPDATE queries

A result object of type waDbResultDelete or waDbResultUpdate respectively is returned.

$result = $model->query("UPDATE " . $this->table . " SET name = 'no name' WHERE name = ''");

// Retrieve the number of affected table records (mysql_affected_rows function)
$result->affectedRows()

INSERT queries

An object of type waDbResultInsert is returned.

// Retrieve the value of the primary key of the created record
// if auto_increment is enabled (mysql_insert_id function)
$result->lastInsertId()

Method exec($sql, $params = null)

This method executes an SQL query and returns the result received from the DBMS adapter (in case of MySQL the result of the mysql_query function is returned). Using this method is convenient when no result is required or when it is necessary to verify the fact of a query execution.

SELECT queries constructor

SELECT queries can be generated using a simple constructor as shown below:

// Select all records sorted by field datetime:
$records = $model->order('datetime DESC')->fetchAll();

// Retrieve an associative array id => name
$data = $model->select('id, name')->fetchAll('id', true);

// The current version offers methods select, where, order:
$model
    ->select('id, name, datetime')
    ->where('contact_id = ' . (int)$contact_id)
    ->order('datetime DESC')
    ->fetchAll('id');

// The previous example is equal to the following:
$model->query("SELECT id, name, datetime WHERE contact_id = ' . (int) $contact_id . ' ORDER BY datetime DESC')->fetchAll('id');

The constructor’s methods return an instance of class waDbQuery with the public method getQuery(), which is handy for checking the exact contents of the generated SQL query. To use it, simply replace a result-fetching method — fetch(), fetchField(), fetchAssoc(), fetchAll() — with getQuery():

$sql = $model
    ->select('id, name, datetime')
    ->where('contact_id = ' . (int)$contact_id)
    ->order('datetime DESC')
    ->getQuery();

echo $sql;
// SELECT id, name, datetime FROM myapp_table_name WHERE contact_id = 42 ORDER BY datetime DESC

Recommendations on implementation of custom model methods in an app

In order to avoid repeating equal parts of code (using standard model methods) in several PHP files, you can create custom methods for a model to implement frequently executed actions.

Example:

// Method returning all contact records
public function getByContact($contact_id)
{
    return $this->getByField('contact_id', $contact_id, true);
}

All methods used for manipulation with each database table are implemented in the corresponding model class. Existing model methods can later be enhanced (e.g., through adding of caching functions).

We recommend implementing most operations on a certain database table in a model's methods not associated with table fields' names. This will, in case of code refactoring, allow applying changes only to one source file: that of the model class.

Connections to other databases

Class waModel can also be used to establish connections to other databases in addition to the one containing Webasyst data. This may be useful, for example, to import data from third-party software.

To establish a connection to another database, you need to specify connection credentials when creating an instance of a model class in your app. There are several ways to do so as described below in this section.

Creating permanent connection configuration

If you are planning to use connection to a certain database multiple times, add the appropriate credentials to framework configuration file wa-config/db.php as an associative array:

<?php

return [
    'default' => [
        'host' => 'localhost',
        'user' => 'user1',
        'password' => '12345678',
        'database' => 'webasyst',
        'type' => 'mysqli',
    ],
    'extra' => [
        'host' => 'localhost',
        'user' => 'user2,
        'password' => '87654321',
        'database' => 'wordpress',
        'type' => 'mysqli',
    ],
];

The above example shows how a new record named extra is added to the configuration file. To get connected to the database named wordpress (in this example), you need to create a model instance in your app's PHP code as shown below:

$extra_model = new waModel('extra');

Calling the standard methods of such a model will query only the database whose name is contained in the specified database configuration.

Setting connection credentials dynamically

Should your app need to connect to different databases, you may want to specify the connection credentials dynamically, as an associative array. Below is provided an example of this approach:

$extra_connection = [
    'host' => 'localhost',
    'user' => 'user2',
    'password' => '87654321',
    'database' => 'somedb',
    'type' => 'mysqli',
];

$extra_model = new waModel($extra_connection);

Both methods described in this section are applicable both to the basic class waModel and to custom model classes which you may create (e.g., myappSomeModel):

$extra_model = new myappSomeModel('extra');

or

//array $extra_connection must contain valid connection credentials

$extra_model = new myappSomeModel($extra_connection);

When using connections to other databases in your app, always be aware of the specific table structure of those databases when designing custom methods for your models.

To use connections to databases with different table structure, it is advisable to create a separate model class for each type of database. This will make your app code more transparent and will facilitate the debugging.