waModel

Database management

Contents...

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 = array('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.

  • 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(array(
    '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.

Пример

$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 two methods of using placeholders: by means of ? characters or in the form type:name (such placeholders are called 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 match 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 \ 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, then placeholder values must be specified as additional parameters when calling method exec. The order of values must match that of placeholders used in the SQL query.
    2. If named placeholders are used, then parmeter $params must contain an associative array of elements corresponding to the following coniditions:
      • 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();
$model->exec('UPDATE table_name SET name = ? WHERE id = ?', $name, $id); //the contents of variables $name and $id will be inserted instead of ? characters exactly in the same order in which these variables are specified as parameters of the exec method

Example

$model = new waModel();
$data = array(
    'name' => 'John',
    'id' => 25,
);
$model->exec('UPDATE table_name SET name = s:name WHERE id = i:id', $data); //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:)

Example

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

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:

idnameage
1John25
2Mary26
3Bill25
4Michael19
5Jane26

Example

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

Result

Array
(
  0 => Array
  (
    id => '1'
    name => 'John'
    age => '25'
  )
  1 => Array
  (
    id => '2'
    name => 'Mary'
    age => '26'
  )
  2 => Array
  (
    id => '3'
    name => 'Bill'
    age => '25'
  )
  3 => Array
  (
    id => '4'
    name => 'Michael'
    age => '19'
  )
  4 => Array
  (
    id => '5'
    name => 'Jane'
    age => '26'
  )
)

Example

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

Result

Array
(
  1 => Array
  (
    name => 'John'
    age => '25'
  )
  2 => Array
  (
    name => 'Mary'
    age => '26'
  )
  3 => Array
  (
    name => 'Bill'
    age => '25'
  )
  4 => Array
  (
    name => 'Michael'
    age => '19'
  )
  5 => Array
  (
    name => 'Jane'
    age => '26'
  )
)

Example

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

Result

Array
(
  25 => Array
  (
    0 => Array
    (
      id => '1'
      name => 'John'
    )
    1 => Array
    (
      id => '3'
      name => 'Bill'
    )
  )
  26 => Array
  (
    0 => Array
    (
      id => '2'
      name => 'Mary'
    )
    1 => Array
    (
      id => '5'
      name => 'Jane'
    )
  )
  19 => Array
  (
    0 => Array
    (
      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 = array(
    'age' => 25,
    'name' => 'John',
);
$model->getByField($data, true); //return all records with the specified field values

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

Array
(
  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 = array(
    'id' => 6,
    'age' => 25,
    'name' => 'John',
);
$model->insert($data, 1);

public function multipleInsert ($data)

Inserts several records with specified values.

Параметры (отдельно для каждой записи)

  • $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 insrted 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.

Example

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

Example

$model = new myappModel();
$data = array(
    'name' => 'bag',
    'color' => array(
        '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

public function ping()

Restores connection to the database management server in case of its failure.

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 = array(
    array(
        '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', array('name' => 'Johnny')); //in field 'name' value 'John' will be replaced with 'Johnny'

Example

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

Example

$model = new myappModel();
$fields = array(
    'name' => 'John',
    'age' => 25,
);
$data = array(
    'mood' => 'bright',
);
$model->updateByField($fields, $data); //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'

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 reponse 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 = array(
    'name' => 'John',
    'age' => 25,
);
$model->updateById(6, $data);