MySQL
Only- Database operate
- php >= 5.4
- mysql >= 5.6
- PDO extension
-
Via PHP require
Download Database
Download EzLoader and See how to userequire_once "path/to/your/folder/wilkques/Ezloader/src/helpers.php"; require_once "path/to/your/folder/wilkques/Database/src/helpers.php"; loadPHP();
-
Via Composer
composer require wilkques/database
require "vendor/autoload.php";
-
start
$connection = \Wilkques\Database\Database::connect('<host>', '<username>', '<password>', '<database>', '<port>', '<character>'); // or $connection = \Wilkques\Database\Database::connect([ 'driver' => '<DB driver>', // mysql 'host' => '<host>', // default localhost 'username' => '<username>', 'password' => '<password>', 'database' => '<database>', 'port' => '<port>', // default 3360 'charset' => '<character>', // default utf8mb4 ]);
-
table
orfrom
orfromSub
table
samefrom
$db->table('<table name>'); // or $db->table('<table name>', '<as name>'); // or $db->table( function ($query) { $query->table('<table name>'); // do something }, '<as name>' ); // output: select ... from (select ... from <table name>) AS `<as name>` // same $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->fromSub( $dbTable, '<as name>' ); // output: select ... from (select ... from <table name>) AS `<as name>` // same $db->fromSub( function ($query) { $query->table('<table name>'); // do something }, '<as name>' ); // output: select ... from (select ... from <table name>) AS `<as name>` // or $db->table([ function ($query) { $query->table('<table name1>'); }, function ($query) { $query->table('<table name2>'); }, ]); // output: select ... from (select ... from <table name1>), (select ... from <table name2>) // or $db->table([ '<as name1>' => function ($query) { $query->table('<table name1>'); }, '<as name2>' => function ($query) { $query->table('<table name2>'); }, ]); // output: select ... from (select ... from <table name1>) AS `<as name1>`, (select ... from <table name2>) AS `<as name2>`
-
select
orselectSub
$db->select( '<columnName1>', '<columnName2>', '<columnName3>', function ($query) { $query->table('<table name>'); // do something } ); // output: select <columnName1>, <columnName2>, <columnName3>, (select ...) // or $db->select([ '<as name1>' => '<columnName1>', '<as name2>' => '<columnName1>', ]); // output: select <columnName1> AS `<as name1>`, <columnName2> AS `<as name2>` // or $db->select([ '<columnName1>', '<columnName2>', '<columnName3>', function ($query) { $query->table('<table name>'); // do something }, '<as name>' => function ($query) { $query->table('<table name>'); // do something }, ]); // output: select <columnName1>, <columnName2>, <columnName3>, (select ...), (select ...) AS `<as name>` // or $db->select("`<columnName1>`, `<columnName2>`, `<columnName3>`"); // or $db->selectSub( function ($query) { $query->table('<table name>'); // do something }, '<as name>' ); // output: select (select ...) AS `<as name>`
-
selectSub
$db->selectSub( function ($query) { $query->table('<table name>'); // do something } ); // output: select (select ...) // or $db->selectSub( function ($query) { $query->table('<table name>'); // do something }, '<as name>' ); // output: select (select ...) AS `<as name>`
-
join
$db->from('<table name1>')->join( '<table name2>', '<table name1>.<column1>', '<table name2>.<column1>' ); // output: select ... join <table name> ON <table name1>.<column1> = <table name2>.<column1> // or $db->from('<table name1>')->join( '<table name2>', function ($join) { $join->on('<table name1>.<column1>', '<table name2>.<column1>') ->orOn('<table name1>.<column2>', '<table name2>.<column2>'); // do something } ); // output: select ... join <table name> ON <table name1>.<column1> = <table name2>.<column1> OR <table name1>.<column2> = <table name2>.<column2>
-
joinWhere
$db->from('<table name1>')->joinWhere( '<table name2>', '<table name1>.<column1>', '<table name2>.<column1>' ); // output: select ... join <table name> WHERE <table name1>.<column1> = <table name2>.<column1> // or $db->from('<table name1>')->joinWhere( '<table name2>', function ($join) { $join->on('<table name1>.<column1>', '<table name2>.<column1>') ->orOn('<table name1>.<column2>', '<table name2>.<column2>'); // do something } ); // output: select ... join <table name> WHERE <table name1>.<column1> = <table name2>.<column1> OR <table name1>.<column2> = <table name2>.<column2>
-
joinSub
$db->from('<table name1>')->joinSub( function ($query) { $query->table('<table name2>'); // do something }, '<as name2>', function (\Wilkques\Database\Queries\JoinClause $join) { $join->on('<table name1>.<column1>', '<as name2>.<column1>') ->orOn('<table name1>.<column2>', '<as name2>.<column2>'); } ); // output: select ... join (select ...) as `<as name2>` ON <table name1>.<column1> = <as name2>.<column1> OR <table name1>.<column2> = <as name2>.<column2> // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->from('<table name1>')->joinSub( $dbTable, '<as name2>', function (\Wilkques\Database\Queries\JoinClause $join) { $join->on('<table name1>.<column1>', '<as name2>.<column1>') ->orOn('<table name1>.<column2>', '<as name2>.<column2>'); } ); // output: select ... join (select ...) as `<as name2>` ON <table name1>.<column1> = <as name2>.<column1> OR <table name1>.<column2> = <as name2>.<column2>
-
joinSubWhere
$db->from('<table name1>')->joinSubWhere( function ($builder) { $builder->table('<table name2>'); // do something }, '<as name2>', function (\Wilkques\Database\Queries\JoinClause $join) { $join->on('<table name1>.<column1>', '<as name2>.<column1>') ->orOn('<table name1>.<column2>', '<as name2>.<column2>'); } ); // output: select ... join (select ...) as `<as name2>` WHERE <table name1>.<column1> = <as name2>.<column1> OR <table name1>.<column2> = <as name2>.<column2> // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->from('<table name1>')->joinSubWhere( $dbTable, '<as name2>', function (\Wilkques\Database\Queries\JoinClause $join) { $join->on('<table name1>.<column1>', '<as name2>.<column1>') ->orOn('<table name1>.<column2>', '<as name2>.<column2>'); } ); // output: select ... join (select ...) as `<as name2>` WHERE <table name1>.<column1> = <as name2>.<column1> OR <table name1>.<column2> = <as name2>.<column2>
-
leftJoin
same
join
-
leftJoinSub
same
joinSub
-
leftJoinWhere
same
join
-
leftJoinSubWhere
same
joinSub
-
rightJoin
same
join
-
rightJoinSub
same
joinSub
-
rightJoinWhere
same
join
-
rightJoinSubWhere
same
joinSub
-
crossJoin
same
join
-
crossJoinSub
same
joinSub
-
crossJoinWhere
same
join
-
crossJoinSubWhere
same
joinSub
-
where
$db->where([ ['<columnName1>'], ['<columnName2>'], ['<columnName3>'], ]); // output: select ... where (<columnName1> IS NULL AND <columnName2> IS NULL AND <columnName3> IS NULL) // or $db->where('<columnName1>'); // output: select ... where (<columnName1> IS NULL) // or $db->where([ ['<columnName1>', '<value1>'], ['<columnName2>', '<value2>'], ['<columnName3>', '<value3>'], ]); // or $db->where([ ['<columnName1>', '<operator1>', '<value1>'], ['<columnName2>', '<operator2>', '<value2>'], ['<columnName3>', '<operator3>', '<value3>'], ]); // or $db->where('<columnName1>', "<operator>", '<columnValue1>'); // or $db->where('<columnName1>', '<value1>') ->where('<columnName2>', '<value2>') ->where('<columnName3>', '<value3>'); // or $db->where('<columnName1>', "<operator>", '<value1>') ->where('<columnName2>', "<operator>", '<value2>') ->where('<columnName3>', "<operator>", '<value3>'); // or $db->where(function ($query) { $query->where('<columnName1>', '<value1>')->where('<columnName2>', '<value2>'); }); // output: select ... where (<columnName1> = <value1> AND <columnName2> = <value2>) // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->where($dbTable); // same $db->whereExists($dbTable); // output: select ... where EXISTS (select ...) // or $db->where('<columnName>', $dbTable); // output: select ... where '<columnName>' = (select ...) // or $db->where('<columnName>', "<operator>", $dbTable); // output: select ... where '<columnName>' <operator> (select ...) // or $db->where('<columnName>', "<operator>", function ($query) { $query->table('<table name>')->where('<columnName1>', '<value1>')->where('<columnName2>', '<value2>'); }); // output: select ... where '<columnName>' <operator> (select ...)
-
orWhere
same
where
-
whereNull
$db->whereNull('<columnName1>');
-
orWhereNull
same
whereNull
-
whereNotNull
same
whereNull
-
orWhereNotNull
same
whereNotNull
-
whereIn
$db->whereIn('<columnName1>', ['<columnValue1>', '<columnValue2>']); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->whereIn('<columnName1>', $dbTable); // or $db->whereIn('<columnName1>', function ($query) { $query->select('<columnName2>')->table('<table name1>'); });
-
orWhereIn
same
whereIn
-
whereNotIn
same
whereIn
-
orWhereNotIn
same
whereIn
-
whereBetween
$db->whereBetween('<columnName1>', ['<columnValue1>', '<columnValue2>']);
-
orWhereBetween
same
whereBetween
-
whereNotBetween
same
whereBetween
-
orWhereNotBetween
same
whereBetween
-
whereExists
$db->whereExists( function ($query) { $query->table('<table name>'); // do something }); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->whereExists($dbTable); // same $db->where($dbTable);
-
whereNotExists
same
whereExists
-
orWhereExists
same
whereExists
-
orWhereNotExists
same
whereExists
-
whereLike
$db->whereLike('<columnName1>', '<columnValue2>');
-
orWhereLike
$db->orWhereLike('<columnName1>', '<columnValue2>');
-
having
$db->having(`<columnName1>`, `<columnValue1>`); // or $db->having(`<columnName1>`, "<operator>", `<columnValue1>`); // or $db->having( `<columnName1>`, function ($query) { $query->table('<table name>'); // do something } ); // or $db->having( `<columnName1>`, "<operator>", function ($query) { $query->table('<table name>'); // do something } ); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->having(`<columnName1>`, $dbTable); // or $db->having(`<columnName1>`, "<operator>", $dbTable);
-
orHaving
$db->orHaving(`<columnName1>`, `<columnValue1>`); // or $db->orHaving(`<columnName1>`, "<operator>", `<columnValue1>`); // or $db->orHaving( `<columnName1>`, function ($query) { $query->table('<table name>'); // do something } ); // or $db->orHaving( `<columnName1>`, "<operator>", function ($query) { $query->table('<table name>'); // do something } ); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->orHaving(`<columnName1>`, $dbTable); // or $db->orHaving(`<columnName1>`, "<operator>", $dbTable);
-
limit
$db->limit(1); // set query LIMIT // or $db->limit(10, 1); // set query LIMIT
-
offset
$db->offset(1); // set query OFFSET
-
groupBy
$db->groupBy('<columnName1>', 'DESC'); // default ASC // or $db->groupBy([ ['<columnName1>', 'DESC'], ['<columnName2>', 'ASC'], ]); // or $db->groupBy([ [ function ($query) { $query->table('<table name>'); // do something }, 'DESC' ], ['<columnName2>', 'ASC'], ]); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->groupBy($dbTable, 'DESC'); // default ASC // or $db->groupBy([ [ $dbTable, 'DESC' ], ['<columnName2>', 'ASC'], ]);
-
groupByDesc
$db->groupByDesc('<columnName1>'); // or $db->groupByDesc('<columnName1>', '<columnName2>'); // or $db->groupByDesc( function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ); // or $db->groupByDesc(['<columnName1>', '<columnName2>']); // or $db->groupByDesc([ function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ]); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->groupByDesc($dbTable, '<columnName1>'); // default ASC // or $db->groupByDesc([ $dbTable, '<columnName1>' ]);
-
groupByAsc
$db->groupByAsc('<columnName1>'); // or $db->groupByAsc('<columnName1>', '<columnName2>'); // or $db->groupByAsc( function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ); // or $db->groupByAsc(['<columnName1>', '<columnName2>']); // or $db->groupByAsc([ function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ]); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->groupByAsc($dbTable, '<columnName1>'); // default ASC // or $db->groupByAsc([ $dbTable, '<columnName1>' ]);
-
orderBy
$db->orderBy('<columnName1>', "DESC"); // default ASC // or $db->orderBy([ ['<columnName1>', 'DESC'], ['<columnName2>', 'ASC'], ]); // or $db->orderBy([ [ function ($query) { $query->table('<table name>'); // do something }, 'DESC' ], ['<columnName2>', 'ASC'], ]);
-
orderByDesc
$db->orderByDesc('<columnName1>'); // or $db->orderByDesc('<columnName1>', '<columnName2>'); // or $db->orderByDesc( function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ); // or $db->orderByDesc(['<columnName1>', '<columnName2>']); // or $db->orderByDesc([ function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ]);
-
orderByAsc
$db->orderByAsc('<columnName1>'); // or $db->orderByAsc('<columnName1>', '<columnName2>'); // or $db->orderByAsc( function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ); // or $db->orderByAsc(['<columnName1>', '<columnName2>']); // or $db->orderByAsc([ function ($query) { $query->table('<table name>'); // do something }, '<columnName2>' ]);
-
union
$db->union(function ($query) { $query->table('<table name>'); // do something }); // or $dbTable = ( new \Wilkques\Database\Queries\Builder( $connection, new \Wilkques\Database\Queries\Grammar\Drivers\MySql, new \Wilkques\Database\Queries\Processors\Processor, ) )->table('<table name1>'); $db->union($dbTable);
-
unionAll
samunion
-
get
$db->get(); // get all data
-
first
$db->first(); // get first data
-
find
$db->find('<id>'); // get find data
-
update
$db->where('<columnName1>', "=", '<columnValue1>') ->update([ '<updateColumnName1>' => '<updateColumnValue1>' ]); // or $db->where('<columnName1>', "=", '<columnValue1>')->first(); $db->update([ '<updateColumnName1>' => '<updateColumnValue1>' ]); // or $db->where('<columnName1>', "=", '<columnValue1>')->first(); $db->update([ '<updateColumnName1>' => function ($query) { $query->table('<table name>')->select('<column name>'); // do something } ]);
-
increment
$db->increment('<columnName>'); // or $db->increment('<columnName>', '<numeric>', [ '<update column 1>' => 'update value 1', '<update column 2>' => 'update value 2', ... ]);
-
decrement
$db->decrement('<columnName>'); // or $db->decrement('<columnName>', '<numeric>', [ '<update column 1>' => 'update value 1', '<update column 2>' => 'update value 2', ... ]);
-
insert
$db->insert([ '<ColumnName1>' => 'ColumnValue1>', '<ColumnName2>' => 'ColumnValue2>', ... ]); // or $db->insert([ [ '<ColumnName1>' => 'ColumnValue1>', '<ColumnName2>' => 'ColumnValue2>', ... ], [ '<ColumnName3>' => 'ColumnValue3>', '<ColumnName4>' => 'ColumnValue4>', ... ] ]);
-
insertSub
$db->insertSub([ '<ColumnName1>' '<ColumnName2>' ... ], function ($query) { $query->from('<Sub table name>')->select( '<Sub ColumnName1>', '<Sub ColumnName2>', ... )->where('<Sub columnName3>', '<Sub value1>')->where('<Sub columnName4>', '<Sub value2>'); }); // output: Insert <table> (<ColumnName1>, <ColumnName2>) SELECT <Sub ColumnName1>, <Sub ColumnName2> FROM <Sub table name> // WHERE <Sub columnName3> = <Sub value1> AND <Sub columnName4> = <Sub value2>
-
delete
$db->where('<columnName1>', "=", '<columnValue1>') ->delete([ '<deleteColumnName1>' => '<deleteColumnValue1>' ]); // or $db->where('<columnName1>', "=", '<columnValue1>')->first(); $db->delete();
-
softDelete
$db->where('<columnName1>', "=", '<columnValue1>') ->softDelete('<deleteColumnName1>', '<date time format>'); // default deleted_at, "Y-m-d H:i:s" // or $db->where('<columnName1>', "=", '<columnValue1>')->first(); $db->softDelete('<deleteColumnName1>', '<date time format>'); // default deleted_at, "Y-m-d H:i:s"
-
reStore
recovery (delete
cannot recovery data)$db->where('<columnName1>', "=", '<columnValue1>') ->reStore('<deleteColumnName1>'); // default deleted_at // or $db->where('<columnName1>', "=", '<columnValue1>')->first(); $db->reStore('<deleteColumnName1>'); // default deleted_at
raw
// select $db->select($db->raw("<sql string in select column>")); // example $db->select($db->raw("COUNT(*)")); // update $db->update([ $db->raw("<sql string in select column>"), ]);
-
query
set SQL string$db->query("<SQL String>")->fetch(); // for example $db->query("SELECT * FROM `<your table name>`")->fetch();
-
prepare
execute SQL string$db->prepare("<SQL String>")->execute(['<value1>', '<value2>' ...])->fetch();
-
bindParams
execute SQL string$stat = $db->prepare("<SQL String>"); $stat->bindParams(['<value1>', '<value2>' ...])->execute(); $stat->fetch();
-
execute
execute SQL string
-
fetchNumeric
get result key to numeric -
fetchAssociative
get result key value -
fetchFirstColumn
get result first column -
fetchAllNumeric
get all result key to numeric -
fetchAllAssociative
get all result key value -
fetchAllFirstColumn
get all result first column -
rowCount
get result -
free
PDO methodcloseCursor
PHP PDOStatement::closeCursor -
fetch
PDOStatement::fetch -
fetchAll
PDOStatement::fetchAll
-
enableQueryLog
enable query logs$db->enableQueryLog();
-
getQueryLog
get all query string and bind data$db->getQueryLog();
-
getParseQueryLog
orparseQueryLog
get paser query logs$db->getParseQueryLog();
-
getLastParseQuery
orlastParseQuery
get paser query$db->getLastParseQuery();
-
lockForUpdate
$db->lockForUpdate();
-
sharedLock
$db->sharedLock();
-
currentPage
$db->currentPage(1); // now page
-
prePage
$db->prePage(15); // pre page
-
getForPage
$db->getForPage(); // get page data // or $db->getForPage('<prePage>', '<currentPage>'); // get page data
-
beginTransaction
$db->beginTransaction();
-
commit
$db->commit();
-
rollback
$db->rollback();
-
host
$db->host('<DB host>');
-
username
$db->username('<DB username>');
-
password
$db->password('<DB password>');
-
database
$db->database('<DB name>');
-
newConnection
$db->newConnection(); // or $db->newConnection("<sql server dns string>");
-
reConnection
$db->reConnection(); // or $db->reConnection("<sql server dns string>");
-
selectDatabase
$db->selectDatabase('<database>');