# Xcrud_db

xCRUD MySQL database query utility class.

# get_instance()

In all external files you can use Xcrud_db database class instance by calling this static method.

Parameter	Type	Default	Description
`$params`	`array`	`false`	A string array of the MySQL user, password, database name, database host and database encoding in that order. This defaults to values defined in Xcrud_config.

$db = Xcrud_db::get_instance();

# query()

Executes the sql query and returns a count of affected rows.

Parameter	Type	Default	Description
`$query`	`string`	`''`	Valid MySQL query.

$db = Xcrud_db::get_instance();
$db->query("SELECT * FROM table example");

# insert_id()

Returns the last insert id;

$db = Xcrud_db::get_instance();
$db->query("INSERT INTO example VALUES (...)");
$db->insert_id();

# result()

Returns a query's result as an number indexed array of associative arrays.

$db = Xcrud_db::get_instance();
$db->query("SELECT id, name FROM table example");
$db->result(); 
// returns
/*
Array(
    [0] => Array(
        'id' => 1,
        'name' => 'A. Name'
    ),
    [1] => Array(
        'id' => 1,
        'name' => 'B. Name'
    ),

    ...

)
*/

# row()

Returns the first result row as associative array.

$db = Xcrud_db::get_instance();
$db->query("SELECT id, name FROM table example LIMIT 1");
$db->row(); 
// returns
/*
Array(
    'id' => 1,
    'name' => 'A. Name'
)
*/

# escape()

Sanitizes literal values for use in sql queries and wraps the escaped value in single quotes.

Parameter	Type	Default	Description
`$val`	`string`		Value to sanitize.
`$not_qu`	`string`	`false`	If set to `true`, the escaped value is NOT wrapped single quotes.
`$type`	`string`	`false`	Data type of $val. Accepted values are: `'bool'`, `'int'` or `'float'`. Assumes `'string'` otherwise. All data types except for `'string'` ignore the parameter `$not_qu`. a) `'bool'` returns 1 for true and 0 for false. b) `'int'` returns an integer value. c) `'float'` returns the value wrapped in single quotes.
`$null`	`string`	`false`	Set `'NULL'` depending on `$type` set to: a) `'bool'` returns 1 for boolean `true` and integers > 0 , `'NULL'` otherwise. b) `'int'` returns an integer value or `'NULL'` if no numeric characters found. c) `'float'` returns `'NULL'` only if an empty string is given as a value else returns the value wrapped in single quotes.
`$bit`	`string`	`false`	Set to `true` to escape as bits depending on `$type`: a) `'bool'` returns `b'1'` for values resolving to `true`. b) `'int'` escapes the integer as a bit field value.

$db = Xcrud_db::get_instance();
$db->escape('nya\lenda'); // 'nya\\lenda'
$db->escape('nya\lenda', true); // nya\\lenda

$db->escape(true, false, 'bool'); // 1
$db->escape('true', false, 'bool'); // 0 - $val needs to be of $type specified

$db->escape(3.14, false, 'float'); // '3.14'
$db->escape(3.14, true, 'float'); // '3.14'
$db->escape(3, false, 'float'); // '3'

// $type='bool' & $null=true
$db->escape(true, false, 'bool', true); // 1
$db->escape('true', false, 'bool', true); // 'NULL'
$db->escape(0, false, 'bool', true); // 'NULL'

// $type='int' & $null=true
$db->escape(401, false, 'int', true); // 401
$db->escape(0, false, 'int', true); // 0
$db->escape('str', false, 'int', true); // 'NULL'
$db->escape('st4r', false, 'int', true); // 4
$db->escape('str5', false, 'int', true); // 5

// $type='float' & $null=true
$db->escape(40.1, false, 'float', true); // '40.1'
$db->escape(0, false, 'float', true); // '0'
$db->escape('', false, 'float', true); // 'NULL'

// $type='bool' & $bit=true
$db->escape(b'1', false, 'bool', false, true); // b'1'
$db->escape(true, false, 'bool', false, true); // b'1'
$db->escape('true', false, 'bool', false, true); // b'0'
$db->escape(0, false, 'bool', false, true); // b'0'

// $type='int' & $bit=true
$db->escape(b'1', false, 'int', false, true); // b'1'
$db->escape(0, false, 'int', false, true); // b'0'
$db->escape(11, false, 'int', false, true); // b'11'

# escape_like()

SQL LIKE pattern matching escape utility.

Parameter	Type	Default	Description
`$val`	`string`	`''`	The value to escape and concatenate between the 1st and 2nd `$pattern` wildcard characters.
`$pattern`	`array`	`array('%', '%')`	Specify the wildcard characters for the MySQL LIKE clause. The first wildcard is prepended to `$val` and the second appended after.

$db = Xcrud_db::get_instance();
$db->escape_like('blue'); // '%blue%'
$db->escape_like('blue', array('%', '_'); // '%blue_'
$db->escape_like('blue', array('', '%'); // 'blue%'
$db->escape_like('blue', array('', ''); // 'blue'

← Xcrud_config Xcrud_postdata →