# Database

Wolff\Core\DB

You can run queries using the Database class of Wolff. It's basically an abstraction layer builded on top of PDO, meaning that it simplifies the process of running queries and is also safe and reliable.

Keep in mind that the PHP PDO extension must be installed and enabled for the database to work.

Configuration

The database constructor looks like this.

__construct([array $data[, array $options ]])

It takes two parameters, an array with the database credentials, and an array which will be used as the options for the PDO instance that the utility uses internally.

If no data array is passed, it will use the credentials defined in the db key of the system/config.php file array.

$db = new Wolff\Core\DB();

$db = new Wolff\Core\DB([
    'dsn'      => 'mysql:host=localhost;dbname=testdb'
    'username' => 'root',
    'password' => '12345',
]);

Both examples are right.

Running queries

query(string $sql[, ...$args]): \Wolff\Core\Query

The query method returns a Wolff\Core\Query object.

$db->query('SELECT * FROM table');

You can prepare a query passing multiple parameters after the first one.

$db->query('SELECT * FROM user WHERE id = ?', $id);

$db->query('SELECT * FROM user WHERE name = ? and email = ?', $name, $email);

Query

The Wolff\Core\Query object returned by the query method has the following method.

Get

get(): array

Returns the query result as an associative array.

$db->query('SELECT * FROM table')->get();

Get Json

getJson(): string

Returns the result as a JSON.

$db->query('SELECT * FROM table')->getJson();

Limit

limit(int $start[, int $end]): array

Returns the query result as an associative array sliced.

$db->query('SELECT * FROM table')->limit(0, 5);

In that example only 5 rows will be returned.

First

first([string $column]): array

Returns the first element of the query result.

$db->query('SELECT * FROM table')->first();

You can pass a column name to the method. That will return only the specified column value of the first element.

$db->query('SELECT * FROM table')->first('column');

Count

count(): int

Returns the number of query rows.

$db->query('SELECT * FROM table')->count();

Pick

pick(...$columns): array

Returns the query result only with the specified columns.

$db->query('SELECT * FROM users')->pick('name');

That would return something like this:

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

$db->query('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

dump(): void

Var dump the query result.

$db->query('SELECT * FROM table')->dump();

Var dump and die

dumpd(): void

Var dump the query result and die.

$db->query('SELECT * FROM table')->dumpd();

Print result

printr(): void

Prints the query result in a nice looking way.

$db->query('SELECT * FROM table')->printr();

Print result and die

printrd(): void

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

$db->query('SELECT * FROM table')->printrd();

General methods

Get Pdo

getPdo(): ?\PDO

Returns the PDO object.

$db->getPdo();

Get last id

getLastId(): ?string

Returns the last inserted ID in the database.

$db->getLastId();

Get last statement

getLastStmt(): \PDOStatement

Returns the last PDO statement executed.

$db->getLastStmt();

Get last query

getLastSql(): mixed

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 with runLastSql.

$db->runLastSql();

Table exists

tableExists(string $table): bool

Returns true if the specified table exists, false otherwise.

$db->tableExists('users');

WARNING: This method must NOT be used with user input since it does not escapes the given arguments.

Column exists

columnExists(string $table, string $column): bool

Returns true if the specified table and 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.

WARNING: This method must NOT be used with user input since it does not escapes the given arguments.

Move rows

moveRows(string $src_table, string $dest_table[, string $conditions[, $args]]): bool

Moves rows from the source table to the destination table.

This method returns true if the transaction has been made successfully, false otherwise.

$db->moveRows('customers', 'new_customers', 'WHERE status = 1');

In case of errors, the changes are completely rolled back.

WARNING: This method must NOT be used with user input since it does not escapes the given arguments.

Fast methods

The DB class has some fast methods you can use.

Insert

insert(string $table, array $data): mixed

Inserts the given data into the specified table.

The first parameter must be the table name where the data will be inserted, the second parameter must be an associative array with data.

Take in mind that the array keys will be directly mapped to the column names.

$db->insert('product', [
    'name'     => 'phone',
    'model'    => 'PHN001',
    'quantity' => 5,
]);

That will be the same as INSERT INTO 'product' (name, model, quantity) VALUES ('phone', 'PHN001', '5').

Select

select(string $table[, string $conditions[, ...$args]]): array

Runs a select query in the specified table.

This method returns the result as an associative array, and accepts dot notation.

$db->select('users');
$db->select('users', 'id = ?', 1);
$db->select('users.name');

Equivalent to:

SELECT * FROM users
SELECT * FROM users WHERE id = 1.
SELECT name FROM users.

Count

count(string $table[, string $conditions[, ...$args]]): int

Returns the number of rows in the specified table.

$db->count('users');
$db->count('users', 'id = ?', 1);

Equivalent to:

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

Delete

delete(string $table[, string $conditions[, ...$args]]): bool

Deletes the rows in the specified table.

This method returns true in case of success, false otherwise.

$db->delete('users');
$db->delete('users', 'id = ?', 1);

Equivalent to:

DELETE * FROM users
DELETE * FROM users WHERE id = 1.