_DB Query

Examples

Complete function list

Select row in table
@param string $distinct COLUMNS/DISTINCT
@param string $table table
@param array $whereArr Where data
@param string $order id ASC, date DESC
@param integer $limit Max result limit
@return instance
static _select($distinct = "*", $table, $whereArr = false, $order = false, $limit = false, $prefix = false)
Update row in table
@param array $setArr update arr("name" => "lorem ipsum")
@param string $table table
@param array $whereArr where data
@return instance
static _update($setArr, $table, $whereArr = false, $limit = false, $enclose = true)
Insert row in table
@param array $setArr insert arr("name" => "lorem ipsum")
@param string $table table
@return instance
static _insert($setArr, $table, $enclose = true)
Get set array, it is the insert/update data/array
@return array
getSet()
Update on duplicate key or insert
@param array|null $setArr change the set array on edit (new array or use @->getSet() and modify).
@return self
onDuplicateKey(?array $setArr = NULL)
Drop row in table
@param string $table tabel to drop
@param array $whereArr where data
@param string $distinct distinct
@return instance
static _delete($table, $whereArr = false, $multiTable = false, $limit = false)
Change position
@param array $arr [id => position]
@param string $table Database table
@param string $col The ID column name
static _sortable($arr, $table, $col)
Union
@param newSelf $select new intance \query\DB
@return self
union($select)
Join table to mysql result
@param string $table the table you want to join
@param string $join b.uid = a.id
@param string $type INNER,LEFT,RIGHT
@return instance
join($table, $join, $type = "INNER")
Set mysql result where data
@param bool $math true = AND, false = OR
@param arr/str $whereData array,string or sprintf
@param boolean $argF sprintf value
@param boolean $setKey Set a key to the where array so you later can modify it
@return instance
where($math, $whereData, $argF = false, $setKey = false, $prep = true)
Set mysql result where data
UNPROTECTED: you need to manually mysqli::_prep the data or use outher methods that auto preps like DB::_implode(array)
@param bool $math true = AND, false = OR
@param arr/str $whereData array,string or sprintf
@param boolean $argF sprintf value
@param boolean $setKey Set a key to the where array so you later can modify it
@return instance
unprepWhere($math, $whereData, $argF = false, $setKey = false, $prep = false)
Order mysql results
@param string $order column ASC, column2 DESC
@return instance
order($order)
Group mysql results
@param string $group column you want to group
@return instance
group($group)
Group mysql results
@param string $group column you want to group
@return instance
having($having)
Get current SQL string
@return string
sql()
Remove where attr at int index or defined index
@param int/str $index where key
remove_where($index)
Reloads to default where
reload_where()
Execute query
@param &$mysqli get mysqli instance
@return mysqli result object
execute(&$mysqli = NULL)
Execute SELECT and fetch as array with nested objects
@param function $callback callaback, make changes in query and if return then change key
@param string $addClass Add stdClass to fetch
@return array
fetch($callback = false, $addClass = "stdClass")
Count rows from instance that has @calRows true in method limit(@limit, @calRows = false)
@return [type] [description]
count_rows()
Profile mysql speed
static _beginProfile()
Close profile and print results
static _endProfile($html = true)
Start Transaction
Call this before multiple DB queries, if one of those queries fails then use rollback() method to go back to DB original state or if are success then commit() method to save to DB
@return Transaction instance
static _transaction()
Commit see _beginTransaction()
@return [type] [description]
commit()
Rollback see _beginTransaction()
@return [type] [description]
rollback()
Create View
@param string $viewTbName Name of view table (Prefix will auto include)
@param string $distinct Choose columns you want to add in view
@param string $table Choose table you want to add in view
@param array $whereArr Where data
@param string $order id ASC, date DESC
@param integer $limit Max result limit
@return instance
static _create_view($viewTbName, $distinct = "*", $table, $whereArr = false, $order = false, $limit = false)
Create View if do not exist or Replace view if exists
@param string $viewTbName Name of view table (Prefix will auto include)
@param string $distinct Choose columns you want to add in view
@param string $table Choose table you want to add in view
@param array $whereArr Where data
@param string $order id ASC, date DESC
@param integer $limit Max result limit
@return instance
static _replace_view($viewTbName, $distinct = "*", $table, $whereArr = false, $order = false, $limit = false)
Drop view
@param string $viewTbName View table name
@return instance
static _drop_view($viewTbName)
Get all columns in table
@param string $table [description]
@return [type] [description]
static _return_columns($table = "pages")
[add_column - Add new column to table]
@param String $column Column name to add
@param String $table Table name to add column in
@param String $columnType Type of column to add
static _add_column($table, $column, $columnType = "BIGINT(20)", $defaultValue = 0)
[add_column - Add new column to table]
@param String $column Column name to add
@param String $table Table name to add column in
@param String $columnType Type of column to add
static _alter_table($table, $value, &$mysqli = false)
version_handling
@param int $id The edit page id
@param string $table The DB table
@return BOOL true/false
static _version_handling($id, $table = "pages")
[array_to_query_str - auto escape]
@param Array $select
@return String MySQL string
private array_to_query_str($select)
Add pagin
@param integer $objectPerPage
@param integer $currentPage
@param integer $type The type of pagination load, regular or scroll feed load
@return pagin inst
pagin($objectPerPage = 4, $currentPage = 1, $type = 1)