PHP code example of wilkques / database

1. Go to this page and download the library: Download wilkques/database library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

wilkques / database example snippets



     loadPHP();


    $connection = new \Wilkques\Database\Connections\PDO\MySql('<host>', '<username>', '<password>', '<database>', '<port>', '<character>');

    // or

    $connection = \Wilkques\Database\Connections\PDO\MySql::connect('<host>', '<username>', '<password>', '<database>', '<port>', '<character>');

    // or

    $connection = (new \Wilkques\Database\Connections\Connectors\PDO\Connections)->connection([
        'driver'    => '<DB driver>',   // mysql
        'host'      => '<host>',        // default localhost
        'username'  => '<username>',
        'password'  => '<password>',
        'database'  => '<database>',
        'port'      => '<port>',        // default 3360
        'charset'   => '<character>',   // default utf8mb4
    ]);

    // or

    $connection = \Wilkques\Database\Connections\Connectors\PDO\Connections::connect([
        'driver'    => '<DB driver>',   // mysql
        'host'      => '<host>',        // default localhost
        'username'  => '<username>',
        'password'  => '<password>',
        'database'  => '<database>',
        'port'      => '<port>',        // default 3360
        'charset'   => '<character>',   // default utf8mb4
    ]);


    $db = new \Wilkques\Database\Queries\Builder(
        $connection,
        new \Wilkques\Database\Queries\Grammar\MySql,
        new \Wilkques\Database\Queries\Processors\Processor,
    );

    // or 
    
    $db = \Wilkques\Database\Queries\Builder::make(
        $connection,
        new \Wilkques\Database\Queries\Grammar\MySql,
        new \Wilkques\Database\Queries\Processors\Processor,
    );


    $connection1 = \Wilkques\Database\Connections\Connectors\PDO\Connections::connect([
        'driver'    => '<DB driver>',   // mysql
        'host'      => '<host>',        // default localhost
        'username'  => '<username>',
        'password'  => '<password>',
        'database'  => '<database>',
        'port'      => '<port>',        // default 3360
        'charset'   => '<character>',   // default utf8mb4
    ]);

    $db->table('<table name>')->where(function ($query) use ($connection1) {
        $query->setConnection(
            $connection1
        )->table('<table name1>');

        // do something ...
    });

    // or

    $db1 = \Wilkques\Database\Queries\Builder::make(
        $connection1,
        new \Wilkques\Database\Queries\Grammar\MySql,
        new \Wilkques\Database\Queries\Processors\Processor,
    );

    $db->table('<table name>')->where($db1->table('<table name1>'));



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



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



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



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



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



    $db->from('<table name1>')->joinSub(
        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>` 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\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>



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



    $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\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 ...)



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



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

    // or

    $dbTable = (
        new \Wilkques\Database\Queries\Builder(
            $connection,
            new \Wilkques\Database\Queries\Grammar\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>');
    });



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



    $db->whereExists(
        function ($query) {
            $query->table('<table name>');
            // do something
    });

    // or

    $dbTable = (
        new \Wilkques\Database\Queries\Builder(
            $connection,
            new \Wilkques\Database\Queries\Grammar\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->whereExists($dbTable);

    // same

    $db->where($dbTable);



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



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



    $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\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->having(`<columnName1>`, $dbTable);

    // or 

    $db->having(`<columnName1>`, "<operator>", $dbTable);



    $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\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->orHaving(`<columnName1>`, $dbTable);

    // or 

    $db->orHaving(`<columnName1>`, "<operator>", $dbTable);



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

    // or

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



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



    $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\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->groupBy($dbTable, 'DESC'); // default ASC

    // or

    $db->groupBy([
        [
            $dbTable, 
            'DESC'
        ],
        ['<columnName2>', 'ASC'],
    ]);



    $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\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->groupByDesc($dbTable, '<columnName1>'); // default ASC

    // or

    $db->groupByDesc([
        $dbTable,
        '<columnName1>'
    ]);



    $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\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->groupByAsc($dbTable, '<columnName1>'); // default ASC

    // or

    $db->groupByAsc([
        $dbTable,
        '<columnName1>'
    ]);



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



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



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



    $db->union(function ($query) {
        $query->table('<table name>');
        // do something
    });

    // or

    $dbTable = (
        new \Wilkques\Database\Queries\Builder(
            $connection,
            new \Wilkques\Database\Queries\Grammar\MySql,
            new \Wilkques\Database\Queries\Processors\Processor,
        )
    )->table('<table name1>');

    $db->union($dbTable);



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



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



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



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



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

    // or

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



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

    // or

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



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

    // or

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



    $db->insertSub([
        '<ColumnName1>' => 'ColumnValue1>',
        '<ColumnName2>' => 'ColumnValue2>',
        ...
    ], function ($query) {
        $query->table('<table name>')->select(
            '<columnName1>',
            '<ColumnName2>',
            ...
        )->where('<columnName3>', '<value3>')->where('<columnName4>', '<value4>');
    });

    // output: insert <table> (...) SELECT ...



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

    // or

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

    $db->delete();



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



    $db->where('<columnName1>', "=", '<columnValue1>')
        ->reStore('<deleteColumnName1>'); // default deleted_at

    // or

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

    $db->reStore('<deleteColumnName1>'); // default deleted_at



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



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

    // for example

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



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



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



    $db->enableQueryLog();



    $db->getQueryLog();



    $db->getParseQueryLog();



    $db->getLastParseQuery();


    
    $db->lockForUpdate();


    
    $db->sharedLock();



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



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



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

    // or

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


    
    $db->beginTransaction();


    
    $db->commit();


    
    $db->rollback();



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



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



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



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



    $db->newConnection();

    // or

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



    $db->reConnection();

    // or

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



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