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);
Get the query result as an associative array with the get
method.
DB::run('SELECT * FROM table')->get();
Get the result as a JSON instead of an array.
DB::run('SELECT * FROM table')->toJson();
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
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 the query rows.
DB::run('SELECT * FROM table')->count();
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
)
)
Var dump the query result.
DB::run('SELECT * FROM table')->dump();
Var dump the query result and die.
DB::run('SELECT * FROM table')->dumpd();
Print the query result in a nice looking way.
DB::run('SELECT * FROM table')->printr();
Print the query result in a nice looking way and die.
DB::run('SELECT * FROM table')->printrd();
Returns the last inserted ID in the database.
DB::getLastId();
Returns the last PDO statement executed.
DB::getLastStmt();
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();
Returns true if the specified table exists, false otherwise.
DB::tableExists('users');
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.
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] =>
)
)
)
Returns the schema of the specified table.
DB::getTableSchema('user');
The DB class has some fast methods you can use
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.
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.
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.