# Database

You can run queries using the DB connection. First you need to use Core\DB in your class, then you can use the following methods.

The run method returns a Query object.

DB::run('SELECT * FROM table');

You can prepare a query passing an array or single variable as the second parameter.

DB::run('SELECT * FROM users WHERE id = ?', $id);

Query methods

get

Get the query result as an associative array with the get method.

DB::run('SELECT * FROM table')->get();

toJson

Get the result as a JSON instead of an array.

DB::run('SELECT * FROM table')->toJson();

limit

Get the query result as an associative array sliced.

DB::run('SELECT * FROM table')->limit(0, 5);
//Only the 5 first rows will be returned

first

Get the first element of the query result with the first method.

DB::run('SELECT * FROM table')->first();

You can pass a column name as parameter to the first method.

That will return only the specified column value of the first element.

DB::run('SELECT * FROM table')->first('column');

count

Count the query rows.

DB::run('SELECT * FROM table')->count();

pick

Get the query result only with the specified columns.

DB::run('SELECT * FROM users')->pick('name');

That would return something like this:

Array
(
    [0] => Margaret Brown
    [1] => Thomas Andrews
    [2] => Bruce Ismay
)

DB::run('SELECT * FROM users')->pick('name', 'age');

That would return something like this:

Array
(
    [0] => Array
        (
            [name] => Margaret Brown
            [age] => 40
        )

    [1] => Array
        (
            [name] => Thomas Andrews
            [age] => 45
        )

)

dump

Var dump the query result.

DB::run('SELECT * FROM table')->dump();

dumpd

Var dump the query result and die.

DB::run('SELECT * FROM table')->dumpd();

printr

Print the query result in a nice looking way.

DB::run('SELECT * FROM table')->printr();

printrd

Print the query result in a nice looking way and die.

DB::run('SELECT * FROM table')->printrd();

General methods

getLastId

Returns the last inserted ID in the database.

DB::getLastId();

getLastStmt

Returns the last PDO statement executed.

DB::getLastStmt();

getLastSql

Returns the last query executed.

DB::getLastSql();

And you can get its arguments with getLastArgs.

DB::getLastArgs();

Finally you can re run the last query:

DB::runLastSql();

tableExists

Returns true if the specified table exists, false otherwise.

DB::tableExists('users');

columnExists

Returns true if the specified column exists, false otherwise.

DB::columnExists('users', 'user_id');

The first parameter is the table where the column is, the second is the column name.

getSchema

Returns the complete database schema

DB::getSchema();

Example result:

Array
(
    //Table category
    [category] => Array
        (
            [0] => Array
                (
                    [Field] => category_id
                    [Type] => int(11)
                    [Null] => NO
                    [Key] => PRI
                    [Default] =>
                    [Extra] => auto_increment
                )

            [1] => Array
                (
                    [Field] => name
                    [Type] => varchar(155)
                    [Null] => NO
                    [Key] =>
                    [Default] =>
                    [Extra] =>
                )
        )

    //Table portfolio
    [portfolio] => Array
        (
            [0] => Array
                (
                    [Field] => portfolio_id
                    [Type] => int(11)
                    [Null] => NO
                    [Key] => PRI
                    [Default] =>
                    [Extra] => auto_increment
                )

            [1] => Array
                (
                    [Field] => title
                    [Type] => varchar(150)
                    [Null] => NO
                    [Key] =>
                    [Default] =>
                    [Extra] =>
                )

            [2] => Array
                (
                    [Field] => description
                    [Type] => varchar(255)
                    [Null] => NO
                    [Key] =>
                    [Default] =>
                    [Extra] =>
                )

            [3] => Array
                (
                    [Field] => category_id
                    [Type] => int(11)
                    [Null] => NO
                    [Key] => MUL
                    [Default] =>
                    [Extra] =>
                )
        )
)

getTableSchema

Returns the schema of the specified table.

DB::getTableSchema('user');

Fast methods

The DB class has some fast methods you can use

Select All

Will return the result of a SELECT * FROM query.

DB::selectAll('users');

Equivalent to: SELECT * FROM users.

You can do the same with conditions:

DB::selectAll('users', 'id = ?', [1]);

Equivalent to: SELECT * FROM users WHERE id = 1.

The first parameter is the table name, the second is the where condition, the third is the argument array.

Count All

Will return the result of a SELECT COUNT(*) FROM query.

DB::countAll('users');

Equivalent to: SELECT COUNT(*) FROM users.

You can do the same with conditions:

DB::countAll('users', 'id = ?', [1]);

Equivalent to: SELECT COUNT(*) FROM users WHERE id = 1.

The first parameter is the table name, the second is the where condition, the third is the argument array.

Delete All

Will return the result of a DELETE FROM query.

DB::deleteAll('users');

Equivalent to: DELETE FROM users.

You can do the same with conditions:

DB::deleteAll('users', 'id = ?', [1]);

Equivalent to: DELETE FROM users WHERE id = 1.

The first parameter is the table name, the second is the where condition, the third is the argument array.