waModel

Correct calls of this class's methods is possible only for its subclasses (an app's or plugin's model classes), in which a valid value for class property $table containing the name of an existing table in framework's database is defined as shown below:

protected $table = 'table_name';

This limitation does not apply to the use of methods exec and query.

By default, database records are uniquely identified by the values of field named id. If a subclass of waModel should use a field with a different name for identifying database records, its name should be specified as the value of subclass property $id:

protected $id = 'product_id';

If more than one field is required for identifying database records, the names of all such fields should be specified as an array:

protected $id = ['product_id', 'order_id'];

Methods

countAll
Returns the number of records stored in the table.
countByField
Returns the number of records with the value of the specified field matching the specified value.
deleteByField
Deletes records with the value of the specified field matching the specified value.
deleteById
Deletes the record with the value of the identifier field matching the specified value.
escape
Prepares data for safe saving to the database.
exec
Executes a SQL query without returning its result.
fieldExists
Verifies whether the specified field exists in the table.
getAll
Returns table contents as an array.
getByField
Returns the contents of records with the value of the specified field matching the specified value.
getById
Returns the contents of record with the value of the identifier field matching the specified value.
getEmptyRow
Returns an array corresponding to all table fields and containing their default values.
getTableId
Returns the name of the identifier field used by the model class.
getTableName
Returns the table name defined in the model class.
insert
Inserts a new record with specified values.
isEmpty
Checks absence of entries in the table.
multipleInsert
Inserts several records with specified values.
ping
Restores connection to the database management server in case of its failure.
query
Executes a SQL query and returns its result.
replace
Replaces a table record using SQL operator REPLACE.
updateByField
Updates the contents of records containing the specified field values.
updateById
Updates the contents of a table record with the identifier field value matching the specified value.

public function countAll()

Returns the number of records stored in the table.

Example

$model = new myappModel();
$model->countAll();

Result

156 //in the table whose name is specified in some model subclass named 'myappModel' there are 156 records

public function countByField ($field, $value = null)

Returns the number of records with the value of the specified field matching the specified value.

Parameters

$field
Name of field to be checked.
$value
Value to be checked in the specified field of all table records.

Example

$model = new myappModel();
$model->countByField('name', 'John');

Result

12 //the table contains 12 records in which field 'name' contains value 'John'

public function deleteByField ($field, $value = null)

Deletes records with the value of the specified field matching the specified value.

Parameters

$field
Name of field to be checked, or an associative array of field names and their values to be checked for the entries to be deleted.
$value
Value to be checked in the specified field of all table records. This value is not taken into account if an array is specified for $field parameter.

Example

$model = new myappModel();
$model->deleteByField('name', 'John');

Example

$model = new myappModel();
$model->deleteByField([
    'name', 'John',
    'age',  '24',
]);

public function deleteById ($value)

Deletes the record with the value of the identifier field matching the specified value.

Parameters

$value
Value (or array of values) to be checked in the identifier field of all table records which need to be deleted.

Example

$model = new myappModel();
$model->deleteById(123); //method deletes the record in which the value of the identifier field (by default, it is 'id') is equal to 123

public function escape ($data, $type = null)

Prepares data for safe saving to the database.

Parameters

$data
Single value or an array of values.
$type
Type to which provided data must be converted. Allowed type values:
- 'int': integer values
- 'like': preparing string data for use with keyword LIKE: occurrences of \\ are replaced with \\\\, PHP function addslashes() is applied, underscores _ and percent characters % are escaped.
- null: default type option: PHP function addslashes() is applied.

Example

$model = new waModel();
$model->escape($data);

public function exec ($sql, $params = null)

Executes a SQL query without returning its result.

Parameters

$sql
The text of a SQL query, which may (optionally) contain placeholders. Placeholders are used for safe adding of various data into the query text; this helps avoid writing unsafe queries to the database. Placeholders are special marks in the text of a SQL query which are replaced by actual data specified in the $params parameter.

There are 3 methods of using placeholders: a) by means of ? characters, b) in the form type:number, or c) in the form type:name (named placeholders; e.g., i:id or s:name). The type must be specified in accordance with the type of data which must be added to the query, and name must be one of the keys of the array specified in the $params parameter.

Below is the list of acceptable data types:
- i: integer or array of integers; if an array is specified, its items will be converted to integers and concatenated into a string with the comma used as a separator.
- b: Boolean value, which will be converted into 1 or 0.
- l: string value, in which characters % and _ will be escaped by means of the \ character.
- f: decimal value, in which commas will be replaced by dots.
- s: string value or array of strings; its items will be included in quotation marks and concatenated into a string with the comma used as a separator.
If the data type is not specified, the by default, a value is treated as a string.
$params
One or more optional parameters used for adding actual data into the SQL query instead of placeholders (if available). There are 2 methods of specifying placeholder values depending on the type of placeholders used in the query:
1. If placeholders are specified as ? characters or in the form type:number, then placeholder values must be specified as additional parameters when calling the exec() method. The order of values must match that of placeholders used in the SQL query. The numeric values of placeholders in the form type:number must start with 0 (e.g., i:0, s:1, etc.).
2. If named placeholders in the form type:name are used, then parameter $params must contain an associative array of elements corresponding to the following conditions:
  - The key of an array item must match one of placeholder names specified in the SQL query.
  - The value of an array item will be inserted into the SQL query text instead of all placeholders with the name matching the key of the current array item.
  The order of array items has no importance.

Example

$model = new waModel();
$model->exec('INSERT INTO table_name (name) VALUES (?)', $customer_name); //the contents of $customer_name variable will be inserted instead of ?

Example

$model = new waModel();

// the values of variables `$name` and `$id` will be inserted instead of ? characters
// in the same order in which they are passed as parameters
$model->exec('UPDATE table_name SET name = ? WHERE id = ?', $name, $id);

// this example is similar to the one above
// with the exception that type conversion is performed on variable values
// before they are added to the query
$model->exec('UPDATE table_name SET name = s:0 WHERE id = i:1', $name, $id);

Example

$model = new waModel();
$data = [
    'name' => 'John',
    'id' => 25,
];

// the values of $data array items with keys 'name' and 'id' will be inserted into the SQL query instead of placeholders with appropriate names;
// their values will be converted into the data types specified for them, i.e. 'name'—string (s:), 'id'—integer (i:)
$model->exec('UPDATE table_name SET name = s:name WHERE id = i:id', $data);

Example

$model = new waModel();
$data = [
    'name' => 'John',
    'id' => 25,
];

// for placeholder with name 'name', no data type is specified (:name);
// therefore, its value extracted from the $data array will be treated as a string
$model->exec('UPDATE table_name SET name = :name WHERE id = i:id', $data);

public function fieldExists ($field)

Verifies whether the specified field exists in the table.

Parameters

$field
Name of table field.

Example

$model = new myappModel();
$model->fieldExists('extra_description');

public function getAll ($key = null, $normalize = false)

Returns table contents as an array each of its items being a subarray corresponding to one of table records.

Parameters

$key
Name of table field, by whose value table records will be grouped. The keys of the returned array will be the values of the specified field.
$normalize
Value grouping mode applied when a non-empty value is specified for $key parameter; acceptable values for this parameter are these:
- 0 or false: if the table contains more than one record with equal values of the specified field, then only the first of such records is included in the returned array
- 1 or true: similar to previous mode; additionally, items with keys matching the specified field name are removed from each subarray
- 2: if the table contains more than one record with equal values of the specified field, then all such records are included in the returned array so that records with equal values of the specified field are grouped as items of subarrays whose keys match the values of the specified field; additionally, items with keys matching the specified field name are removed from each subarray (as described for mode 1/true)

Examples for this method are based on the following sample data:

id	name	age
1	John	25
2	Mary	26
3	Bill	25
4	Michael	19
5	Jane	26

Example

$model = new myappModel();
$model->getAll();

Result

[
    [
        id => '1'
        name => 'John'
        age => '25'
    ],
    [
        id => '2'
        name => 'Mary'
        age => '26'
    ],
    [
        id => '3'
        name => 'Bill'
        age => '25'
    ],
    [
        id => '4'
        name => 'Michael'
        age => '19'
    ],
    [
        id => '5'
        name => 'Jane'
        age => '26'
    ],
]

Example

$model = new myappModel();
$model->getAll('id', 1); //element 'id' is removed from each subarray

Result

[
    [
        name => 'John'
        age => '25'
    ],
    [
        name => 'Mary'
        age => '26'
    ],
    [
        name => 'Bill'
        age => '25'
    ],
    [
        name => 'Michael'
        age => '19'
    ],
    [
        name => 'Jane'
        age => '26'
    ],
]

Example

$model = new myappModel();
$model->getAll('age', 2); //values are grouped by the value of field 'age'

Result

[
    25 => [
        0 => [
            id => '1'
            name => 'John'
        ],
        1 => [
            id => '3'
            name => 'Bill'
        ],
    ],
    26 => [
        0 => [
            id => '2'
            name => 'Mary'
        ],
        1 => [
            id => '5'
            name => 'Jane'
        ],
    ],
    19 => [
        0 => [
            id => '4'
            name => 'Michael'
        ],
    ],
],

public function getByField ($field, $value = null, $all = false, $limit = false)

Returns the contents of records with the value of the specified field matching the specified value. Method allows 2 modes of passing parameters: for one value and for multiple values.

Parameters (for one value)

$field
Name of field to be checked.
$value
Field value.
$all
Flag requiring to return the contents of all found records. Instead of Boolean true you can specify string name of field whose values in the result array must be used as the array keys. By default (false), only the first found record is returned.
$limit
Number of records to be returned if all matching records are requested. By default (false) this limitation is disabled.

Parameters (for multiple values)

$field
Associative array with field names as item keys and their values as item values.
$all
Flag requiring to return the contents of all found records. Instead of Boolean true you can specify string name of field whose values in the result array must be used as the array keys. By default (false), only the first found record is returned.
$limit
Number of records to be returned if all matching records are requested. By default (false) this limitation is disabled.

Example

$model = new myappModel();
$model->getByField('age', 25, true, 10); //return the first 10 records in which the value of field 'age' is equal to 25

Example

$model = new myappModel();
$data = [
    'age' => 25,
    'name' => 'John',
];

// return all records with the specified field values
$model->getByField($data, true);

public function getById ($value)

Returns the contents of record with the value of the identifier field matching the specified value.

Parameters

$value
Name of the identifier field .

Example

$model = new myappModel();
$model->getById(4);

public function getEmptyRow()

Returns an array corresponding to all table fields and containing their default values.

Example

$model = new myappModel();
$model->getEmptyRow();

Result

[
    id => ''
    name => ''
    age => ''

public function getTableId()

Returns the name of the identifier field used by the model class.

Example

$model = new myappModel();
$model->getTableId();

Result

id //name of the identifier field which is by default used if a different field name is not defined in the model class

public function getTableName()

Returns the table name defined in the model class.

Example

$model = new myappModel();
$model->getTableName();

public function insert ($data, $type = 0)

Inserts a new record with specified values.

Parameters

$data
Associative array of values with key matching the names of table fields.
$type
Execution mode for SQL query INSERT:
- 0: query is executed without additional conditions (default mode)
- 1: query is executed with condition ON DUPLICATE KEY UPDATE
- 2: query is executed with key word IGNORE

Example

$model = new myappModel();
$data = [
    'id' => 6,
    'age' => 25,
    'name' => 'John',
];
$model->insert($data, 1);

public function isEmpty()

Checks absence of entries in the table.

Example

$no_records_available = (new myappModel())->isEmpty();

public function multipleInsert ($data)

Inserts several records with specified values.

Parameters (for each entry)

$data
Array of values to be inserted into the database, which can be specified in several ways as described below:
1. Array items must consist of associative subarrays containing items of the form 'field' => 'value'. Individual records corresponding to each subarray are inserted into the database table.
2. Array items must have the form 'field' => 'value'. If one of array items contains an array of values, then multiple records are inserted in the table, each corresponding to one of such subarray items. Otherwise calling of this method is equivalent to using method insert.
$mode
SQL query execution mode for the cases when values are being inserted that are equal to the values of unique table fields included in a PRIMARY or UNIQUE index:
- null: no data are inserted.
- waModel::INSERT_EXPLICIT: no data are inserted and a waDbException exception is thrown.
- waModel::INSERT_IGNORE: SQL keyword IGNORE is used.
- Array of table field names: the values of the listed fields are updated from the values with the same keys from the $data parameter.
- Associative array in the “field → value” format: the values of the fields specified as array keys are updated from their values in this array.
- Any other array of strings: each item is used as one of the expressions separated by commas and added to the SQL query after the keywords ON DUPLICATE KEY UPDATE.

Example

$model = new myappModel();
$data = [
    [
        'name' => 'bag',
        'color' => 'red',
    ],
    [
        'name' => 'bag',
        'color' => 'green',
    ],
    [
        'name' => 'bag',
        'color' => 'blue',
    ],
];
$model->multipleInsert($data); //3 records corresponding to 3 array items will be inserted

Example

$model = new myappModel();
$data = [
    'name' => 'bag',
    'color' => [
        'red',
        'green',
        'blue',
    ],
];
$model->multipleInsert($data); //3 records corresponding to 3 values of the 'color' element will be inserted with the value of the 'name' field equal for all such records

Example

// update the `color` field values in the entries with existing `id` + `name` pairs
// by the corresponding values from the array,
// if fields `id` and `name` are included in a PRIMARY or UNIQUE index
(new myappModel())->multipleInsert([
    [
        'id' => 1,
        'name' => 'John',
        'color' => 'white',
    ],
    [
        'id' => 2,
        'name' => 'Mary',
        'color' => 'blue',
    ],
    [
        'id' => 3,
        'name' => 'Bill',
        'color' => 'red',
    ],
], [
    'color'
]);

Example

// update the `color` field values in the entries with existing `id` + `name` pairs
// by the value from the 2nd array
// if fields `id` and `name` are included in a PRIMARY or UNIQUE index
(new myappModel())->multipleInsert([
    [
        'id' => 1,
        'name' => 'John',
        'color' => 'white',
    ],
    [
        'id' => 2,
        'name' => 'Mary',
        'color' => 'blue',
    ],
    [
        'id' => 3,
        'name' => 'Bill',
        'color' => 'red',
    ],
], [
    'color' => 'green',
]);

public function ping()

Restores connection to the database management server in case of its failure. May be useful to eliminate the “MySQL server has gone away” error during long computing cycles.

Example

$model = new myappModel();
$model->ping();

public function query ($sql)

Executes a SQL query and returns its result as an instance of class waDbResultSelect, waDbResultInsert, waDbResultUpdate, waDbResultDelete, or waDbResultReplace. To access the result of a SQL query, call public methods of the class corresponding to the specific query type.

Parameters

$sql
Required parameter: SQL query string. If a SQL query contains placeholders to insert custom data into the query text, then you also need to pass placeholder values as described for method exec.

Example

$model = new waModel();
$model->query('SELECT * FROM table_name')->fetchAll() //method fetchAll is available for accessing the results of a 'SELECT' query because it is described in the source code of class waDbResultSelect;
//this method returns a PHP array of values retrieved by the SQL query

public function replace ($data)

Replaces a table record using SQL operator REPLACE.

Parameters

$data
Data array.

Example

$model = new myappModel();
$data = [
    [
        'item_id' => 18,
        'order_id' => 984,
    ],
];
$model->replace($data);

public function updateByField ($field, $value, $data = null, $options = null, $return_object = false)

Updates the contents of records containing the specified field values. Several methods of passing parameters are acceptable:

Parameters (verifying values in one field)

$field
Name of the table field whose value needs to be updated.
$value
Existing value of the specified field, which needs to be replaced with a new value. Instead of one value, you may specify an array; in this case the specified field is updated for all records where that field's value matches one of array items specified in parameter $value.
$data
Associative array of new values for found records.
$options
Optional key words for SQL query UPDATE: LOW_PRIORITY or IGNORE.
$return_object
Flag requiring to return an instance of class waDbResultUpdate to enable you to call its public methods in order to access the response received from the database server. By default (false) the method returns a simple Boolean value identifying the successful query result, or null if incorrect parameters are passed to the method.

Parameters (verifying values in several fields)

$field
Associative array of table fields by whose values table records must be searched.
$data
Associative array of new values of arbitrarily specified fields for the found records.
$options
Optional key words for SQL query UPDATE: LOW_PRIORITY or IGNORE.

Example

$model = new myappModel();
$model->updateByField('name', 'John', ['name' => 'Johnny')]; //in field 'name' value 'John' will be replaced with 'Johnny'

Example

$model = new myappModel();
$values = [
    'John',
    'Johnny',
];

$data = [
    'mood' => 'bright',
];

// for records where the value of field 'name' is equal to 'John' or 'Johnny',
// the value of field 'mood' will be replaced with 'bright'
$model->updateByField('name', $values, $data);

Example

$model = new myappModel();
$fields = [
    'name' => 'John',
    'age' => 25,
];

$data = [
    'mood' => 'bright',
];

// for records where the value of field 'name' is equal to 'John' and the value of 'age' is equal to 25,
// set the value of field 'mood' to 'bright'
$model->updateByField($fields, $data);

public function updateById ($id, $data, $options = null, $return_object = false)

Updates the contents of a table record with the identifier field value matching the specified value.

Parameters

$id
The value of the identifier field used by the model class which is searched for across all table records in order to replace the values of fields specified in $data parameter in the found record.
$data
Associative array of new values for arbitrarily specified fields of the found record.
$options
Optional key words for SQL query UPDATE: LOW_PRIORITY or IGNORE.
$return_object
Flag requiring to return an instance of class waDbResultUpdate to enable you to call its public methods in order to access the response received from the database server. By default (false) the method returns a simple Boolean value identifying the successful query result, or null if incorrect parameters are passed to the method.

Example

$model = new myappModel();
$data = [
    'name' => 'John',
    'age' => 25,
];
$model->updateById(6, $data);