Database Shortcuts
Insert
<?php
// Usually return value is the number of affected rows.
$this->db->insert([
// Backticks are automatically added.
// Be careful, table name isn't escaped!
'table' => 'users',
// Don't set 'data' => $this->proxy->post()! Don't trust post! Users can inject e.g. "is_admin" => 1, if you send whole post to database.
// Note that empty data is not allowed. You must specify a column. If you want to insert a blank row, send 'id' => null (primary key).
'data' => [
// Typecast is mandatory for all values. The framework must know how to deal with these vaues and proper escape them.
// Backticks are automatically added.
'name' => (string) $this->proxy->post('name', 'unknown'),
// This will be set to null into database, if 'force_null' is set to true (default), in case of an empty value.
'nickname' => (string) $this->proxy->post('nickname'),
'weight' => (int) $this->proxy->post('weight', 10),
// Note that boolean false values are converted to null whereas true it is converted to 1.
'is_owner' => (bool) $this->proxy->post('is_owner'),
'price' => (float) $this->proxy->post('price'),
// You can use e.g. null as second array value for specific fields.
// Even if 'force_null' is set to false, the database field will be set to null, in case of an empty value.
'size' => [(int) $this->proxy->post('size'), null],
],
// Default is true. If 'force_null' is true and 'trim_strings' is true, values like " " are set to null into database.
'trim_strings' => true,
// Default is true. Force null empty values, e.g. "", "0", 0, null, false - are set to null into database.
'force_null' => true,
]);
Update
<?php
// Usually return value is the number of affected rows.
$this->db->update([
// Backticks are automatically added.
// Be careful, table name isn't escaped!
'table' => 'users',
// Don't set 'data' => $this->proxy->post()! Don't trust post! Users can inject e.g. "is_admin" => 1, if you send whole post to database.
// Note that empty data is not allowed. You must specify a column.
'data' => [
// Typecast is mandatory for all values. The framework must know how to deal with these vaues and proper escape them.
// Backticks are automatically added.
'name' => (string) $this->proxy->post('name', 'unknown'),
// This will be set to null into database, if 'force_null' is set to true (default), in case of an empty value.
'nickname' => (string) $this->proxy->post('nickname'),
'weight' => (int) $this->proxy->post('weight', 10),
// Note that boolean false values are converted to null whereas true it is converted to 1.
'is_owner' => (bool) $this->proxy->post('is_owner'),
'price' => (float) $this->proxy->post('price'),
// You can use e.g. null as second array value for specific fields.
// Even if 'force_null' is set to false, the database field will be set to null, in case of an empty value.
'size' => [(int) $this->proxy->post('size'), null],
],
// Default is true. If 'force_null' is true and 'trim_strings' is true, values like " " are set to null into database.
'trim_strings' => true,
// Default is true. Force null empty values, e.g. "", "0", 0, null, false - are set to null into database.
'force_null' => true,
// "where" isn't mandatory. But you will update all table rows if isn't present!
// Note that where[0] (field name) isn't escaped.
// In this case, Nicotine will assume that are you referring to primary key, namelly `id`:
'where' => (int) $this->proxy->get('id'), // means where `id` = 123
'where' => ['id', (int) "123"], // Same as above.
'where' => ['name', (string) $this->proxy->post('name')], // Produces: where `name` = 'John' (automatically escaped).
'where' => ['price', (float) $this->proxy->post('price')], // Produces: where `price` = 12.34
// In this example, 10 is default, in case of empty value for size:
'where' => ['size', (int) $this->proxy->post('size', '10')], // Produces: where `size` = 10
'where' => ['is_admin', (bool) $this->proxy->post('is_admin', false)], // Produces: where `is_admin` is null, on empty post 'is_admin' value.
'where' => ['has_category', (bool) $this->proxy->post('has_category')], // Produces: where `has_category` is not null, if 'has_category' post value is not empty.
]);
Delete
<?php
// Usually return value is the number of affected rows.
$this->db->delete([
// Backticks are automatically added.
// Be careful, table name isn't escaped!
'table' => 'users',
// "where" isn't mandatory. But you will delete all table rows if isn't present!
// Note that where[0] (field name) isn't escaped.
// In this case, Nicotine will assume that are you referring to primary key, namelly `id`:
'where' => (int) $this->proxy->get('id'), // means where `id` = 123
'where' => ['id', (int) "123"], // Same as above.
'where' => ['name', (string) $this->proxy->post('name')], // Produces: where `name` = 'John' (automatically escaped).
'where' => ['price', (float) $this->proxy->post('price')], // Produces: where `price` = 12.34
// In this example, 10 is default, in case of empty value for size:
'where' => ['size', (int) $this->proxy->post('size', '10')], // Produces: where `size` = 10
'where' => ['is_admin', (bool) $this->proxy->post('is_admin', false)], // Produces: where `is_admin` is null, on empty post 'is_admin' value.
'where' => ['has_category', (bool) $this->proxy->post('has_category')], // Produces: where `has_category` is not null, if 'has_category' post value is not empty.
]);