wilkques/database

Maintainers

Details

github.com/wilkques/php-database

Source

Issues

Installs: 18

Dependents: 0

Suggesters: 0

Security: 0

Stars: 0

Watchers: 1

Forks: 0

Open Issues: 0

pkg:composer/wilkques/database

v1.0.0 2025-05-28 07:24 UTC

Requires

Requires (Dev)

Suggests

MIT eab78404042224d7b7185bc49a1ea8cbe39938e4

This package is auto-updated.

Last update: 2025-09-28 08:08:01 UTC

README

Latest Stable Version License

Notice

  1. MySQL Only
  2. Database operate

ENV

  1. php >= 5.4
  2. mysql >= 5.6
  3. PDO extension

How to use

  1. Via PHP require
    Download Database
    Download EzLoader and See how to use

    require_once "path/to/your/folder/wilkques/Ezloader/src/helpers.php";
require_once "path/to/your/folder/wilkques/Database/src/helpers.php";

loadPHP();

  2. Via Composer composer require wilkques/database

    require "vendor/autoload.php";

  3. 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
]);

Methods

table or from

  1. table or from or fromSub table same from

    $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

  1. select or selectSub

    $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>`

  2. 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

  1. 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>

  2. 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>

  3. 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>

  4. 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>

  5. leftJoin

    same join

  6. leftJoinSub

    same joinSub

  7. leftJoinWhere

    same join

  8. leftJoinSubWhere

    same joinSub

  9. rightJoin

    same join

  10. rightJoinSub

    same joinSub

  11. rightJoinWhere

    same join

  12. rightJoinSubWhere

    same joinSub

  13. crossJoin

    same join

  14. crossJoinSub

    same joinSub

  15. crossJoinWhere

    same join

  16. crossJoinSubWhere

    same joinSub

where

  1. 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 ...)

  2. orWhere

    same where

  3. whereNull

    $db->whereNull('<columnName1>');

  4. orWhereNull

    same whereNull

  5. whereNotNull

    same whereNull

  6. orWhereNotNull

    same whereNotNull

  7. 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>');
});

  8. orWhereIn

    same whereIn

  9. whereNotIn

    same whereIn

  10. orWhereNotIn

    same whereIn

  11. whereBetween

    $db->whereBetween('<columnName1>', ['<columnValue1>', '<columnValue2>']);

  12. orWhereBetween

    same whereBetween

  13. whereNotBetween

    same whereBetween

  14. orWhereNotBetween

    same whereBetween

  15. 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);

  16. whereNotExists

    same whereExists

  17. orWhereExists

    same whereExists

  18. orWhereNotExists

    same whereExists

  19. whereLike

    $db->whereLike('<columnName1>', '<columnValue2>');

  20. orWhereLike

    $db->orWhereLike('<columnName1>', '<columnValue2>');

having

  1. 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);

  2. 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 or offset

  1. limit

    $db->limit(1); // set query LIMIT

// or

$db->limit(10, 1); // set query LIMIT

  2. offset

    $db->offset(1); // set query OFFSET

group by

  1. 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'],
]);

  2. 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>'
]);

  3. 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>'
]);

order by

  1. 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'],
]);

  2. 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>'
]);

  3. 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

  1. 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);

  2. unionAll sam union

Get Data

  1. get

    $db->get(); // get all data

  2. first

    $db->first(); // get first data

  3. find

    $db->find('<id>'); // get find data

Update

  1. 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
    }
]);

  2. increment

    $db->increment('<columnName>');

// or

$db->increment('<columnName>', '<numeric>', [
    '<update column 1>' => 'update value 1',
    '<update column 2>' => 'update value 2',
    ...
]);

  3. decrement

    $db->decrement('<columnName>');

// or

$db->decrement('<columnName>', '<numeric>', [
    '<update column 1>' => 'update value 1',
    '<update column 2>' => 'update value 2',
    ...
]);

Insert

  1. insert

    $db->insert([
        '<ColumnName1>' => 'ColumnValue1>',
        '<ColumnName2>' => 'ColumnValue2>',
        ...
    ]);

// or

$db->insert([
    [
        '<ColumnName1>' => 'ColumnValue1>',
        '<ColumnName2>' => 'ColumnValue2>',
        ...
    ],
    [
        '<ColumnName3>' => 'ColumnValue3>',
        '<ColumnName4>' => 'ColumnValue4>',
        ...
    ]
]);

  2. 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

  1. delete

    $db->where('<columnName1>', "=", '<columnValue1>')
    ->delete([
        '<deleteColumnName1>' => '<deleteColumnValue1>'
    ]);

// or

$db->where('<columnName1>', "=", '<columnValue1>')->first();

$db->delete();

  2. 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"

  3. 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

  1. 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>"),
]);

SQL Execute

  1. query set SQL string

    $db->query("<SQL String>")->fetch();

// for example

$db->query("SELECT * FROM `<your table name>`")->fetch();

  2. prepare execute SQL string

    $db->prepare("<SQL String>")->execute(['<value1>', '<value2>' ...])->fetch();

  3. bindParams execute SQL string

    $stat = $db->prepare("<SQL String>");

$stat->bindParams(['<value1>', '<value2>' ...])->execute();

$stat->fetch();

  4. execute execute SQL string

SQL Execute result

  1. fetchNumeric get result key to numeric

  2. fetchAssociative get result key value

  3. fetchFirstColumn get result first column

  4. fetchAllNumeric get all result key to numeric

  5. fetchAllAssociative get all result key value

  6. fetchAllFirstColumn get all result first column

  7. rowCount get result

  8. free PDO method closeCursor PHP PDOStatement::closeCursor

  9. fetch PDOStatement::fetch

  10. fetchAll PDOStatement::fetchAll

Query Log

  1. enableQueryLog enable query logs

    $db->enableQueryLog();

  2. getQueryLog get all query string and bind data

    $db->getQueryLog();

  3. getParseQueryLog or parseQueryLog get paser query logs

    $db->getParseQueryLog();

  4. getLastParseQuery or lastParseQuery get paser query

    $db->getLastParseQuery();

Lock

  1. lockForUpdate

    $db->lockForUpdate();

  2. sharedLock

    $db->sharedLock();

Page

  1. currentPage

    $db->currentPage(1); // now page

  2. prePage

    $db->prePage(15); // pre page

  3. getForPage

    $db->getForPage(); // get page data

// or

$db->getForPage('<prePage>', '<currentPage>'); // get page data

Transaction

  1. beginTransaction

    $db->beginTransaction();

  2. commit

    $db->commit();

  3. rollback

    $db->rollback();

Connect

  1. host

    $db->host('<DB host>');

  2. username

    $db->username('<DB username>');

  3. password

    $db->password('<DB password>');

  4. database

    $db->database('<DB name>');

  5. newConnection

    $db->newConnection();

// or

$db->newConnection("<sql server dns string>");

  6. reConnection

    $db->reConnection();

// or

$db->reConnection("<sql server dns string>");

  7. selectDatabase

    $db->selectDatabase('<database>');