Database Connection

There are two ways of creating a database connection:

Instantiate a database connection for a specific driver;
Instantiate a Connection Manager to store multiple database connections.

The Connection Manager implements the same connection strategy. This means that you can define a connection to act like it.

Supported Drivers

MySQL
SQLite

MySQL Connection

MySQL use PDO as a connector.

Let say you have a database named example_db on 127.0.0.1 with username john and password doe. All you have to do is to initialize a connection with a PDO connector.

$connection = new new \Greg\Orm\Connection\MysqlConnection(
    new \Greg\Orm\Connection\Pdo('mysql:dbname=example_db;host=127.0.0.1', 'john', 'doe')
);

SQLite Connection

Sqlite use PDO as a connector.

Let say your database is in /var/db/example_db.sqlite. All you have to do is to initialize a connection with a PDO connector.

$connection = new \Greg\Orm\Connection\SqliteConnection(
    new \Greg\Orm\Connection\Pdo('sqlite:/var/db/example_db.sqlite')
);

Methods

Below you can find a list of supported methods.

transaction - Executes a process in a transaction;
inTransaction - Determines if inside a transaction;
beginTransaction - Initiates a transaction;
commit - Commits a transaction;
rollBack - Rolls back a transaction;
execute - Execute an SQL statement and return the number of affected rows;
lastInsertId - Returns the ID of the last inserted row or sequence value;
quote - Quotes a string for use in a query;
fetch - Fetches the next row from a result set;
fetchAll - Returns an array containing all of the result set rows;
generate - Returns a generator containing all of the result set rows;
column - Returns a single column from the next row of a result set;
columnAll - Returns an array containing a single column from all of the result set rows;
pairs - Returns an array containing a pair of key-value column from all of the result set rows;
dialect - Returns the sql dialect of the current driver;
truncate - Truncates a table and returns the number of affected rows;
listen - Listens for executed queries;
describe - Describes a table;
select - Creates a SELECT statement;
insert - Creates a INSERT statement;
delete - Creates a DELETE statement;
update - Creates a UPDATE statement;
from - Creates a FROM clause;
join - Creates a JOIN clause;
where - Creates a WHERE clause;
having - Creates a HAVING clause;
orderBy - Creates a ORDER BY clause;
groupBy - Creates a GROUP BY clause;
limit - Creates a LIMIT clause;
offset - Creates a OFFSET clause;

transaction

Turns off autocommit mode and execute user defined callable. If run successfully, then the transaction will be committed, otherwise it will be rolled back.

public function transaction(callable($this): void $callable): $this;

$callable - The callable.

Example:

$connection->transaction(function(Greg\Orm\Connection\Connection $connection) {
    $connection->execute("UPDATE `Table` SET `Foo` = ?", ['foo']);
});

inTransaction

Determine if a transaction is currently active within the connection. This method only works for database drivers that support transactions.

public function inTransaction(): bool

Example:

$connection->inTransaction(); // result: false

$connection->beginTransaction();

$connection->inTransaction(); // result: true

beginTransaction

Turns off autocommit mode. While autocommit mode is turned off, changes made to the database via the PDO object instance are not committed until you end the transaction by calling commit. Calling rollBack will roll back all changes to the database and return the connection to autocommit mode.

public function beginTransaction(): bool

Example:

$connection->beginTransaction();

try {
    $connection->execute("UPDATE `Table` SET `Foo` = ?", ['foo']);

    $connection->commit();
} catch(Exception $e) {
    $connection->rollBack();
}

commit

Commits a transaction, returning the database connection to autocommit mode until the next call to beginTransaction starts a new transaction.

public function commit(): bool

Example:

See beginTransaction.

rollBack

Rolls back the current transaction, as initiated by beginTransaction. If the database was set to autocommit mode, this function will restore autocommit mode after it has rolled back the transaction.

public function rollBack(): bool

Example:

See beginTransaction.

execute

Executes an SQL statement in a single function call, returning the number of rows affected by the statement.

public function execute(string $sql, array $params = []): int

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters.

Example:

$connection->execute("UPDATE `Table` SET `Foo` = ?", ['foo']);

lastInsertId

Returns the ID of the last inserted row, or the last value from a sequence object, depending on the underlying driver.

public function lastInsertId(string $sequenceId = null): string

$sequenceId - Name of the sequence object from which the ID should be returned.

Example:

$connection->execute("INSERT INTO `Table` (`Column`) VALUES (?)", ['foo']);

$id = $connection->lastInsertId(); // result: 1

quote

Places quotes around the input value (if required) and escapes special characters within the input value, using a quoting style appropriate to the underlying driver.

public function quote(string $value): string

$value - The value to be quoted.

Example:

$connection->quote('I use "quotes".'); // result: I use ""quotes"".

fetch

Fetches the next row from a result set.

public function fetch(string $sql, array $params = []): ?array

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters.

Example:

$connection->fetch('Select `Column` from `Table`'); // result: ["Column" => 'foo']

fetchAll

Returns an array containing all of the remaining rows in the result set. The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch.

public function fetchAll(string $sql, array $params = []): array

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters.

Example:

$connection->fetchAll('Select `Column` from `Table`'); // result: [["Column" => 'foo'], ["Column" => 'bar']]

generate

Returns a generator containing all of the remaining rows in the result set. The generator represents each row as either an array of column values or an object with properties corresponding to each column name. An empty generator is returned if there are zero results to fetch.

public function generate(string $sql, array $params = []): \Generator

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters.

Example:

$generator = $connection->generate('Select `Column` from `Table`');

foreach($generator as $row) {
    // 1st result: ["Column" => 'foo']
    // 2nd result: ["Column" => 'bar']
}

column

Returns a single column from the next row of a result set or FALSE if there are no more rows.

public function column(string $sql, array $params = [], string $column = '0'): mixed

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters;
$column - The column you wish to retrieve from the row. If no value is supplied, it fetches the first column.

Example:

$connection->column('Select `Column` from `Table`'); // result: foo

columnAll

Returns an array containing a single column from all of the result set rows. An empty array is returned if there are zero results to fetch.

public function columnAll(string $sql, array $params = [], string $column = '0'): array

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters;
$column - The column you wish to retrieve from the row. If no value is supplied, it fetches the first column.

Example:

$connection->columnAll('Select `Column` from `Table`'); // result: ['foo', 'bar']

pairs

Returns an array containing a pair of key-value column from all of the result set rows. An empty array is returned if there are zero results to fetch.

public function pairs(string $sql, array $params = [], string $key = '0', string $value = '1'): array

$sql - The SQL statement to prepare and execute;
$params - SQL statement parameters;
$key - The key column you wish to retrieve from the row. If no value is supplied, it fetches the first column;
$value - The value column you wish to retrieve from the row. If no value is supplied, it fetches the second column.

Example:

$connection->pairs('Select `Id`, `Column` from `Table`'); // result: [1 => 'foo', 2 => 'bar']

dialect

Returns the dialect of the current connection.

public function dialect(): Greg\Orm\Dialect\SqlDialect

Example:

$connection->dialect()->concat(['`Column1`', '`Column2`'], '","');

truncate

Truncates a table and returns the number of affected rows.

public function truncate(string $tableName): int

$tableName - Table name.

Example:

$connection->truncate('Table');

listen

Listens for executed queries.

public function listen(callable(string $sql, array $params, $this): void $callable): $this

$callable - The callable.

Example:

$connection->truncate('Table');

describe

Describes a table.

public function describe(string $tableName, bool $force = false): array

$tableName - Table name;
$force - By default connection will save in memory the table description. Set it to true if you want to fetch from database new description.

Example:

$connection->describe('Table'); // result: ['columns' => [...], 'primary' => [...]]

select

Creates a SELECT statement.

public function select(): Greg\Orm\Query\SelectQuery

Example:

$query = $connection->select()->from('Table');

echo $query->toString(); // result: SELECT * FROM `Table`

insert

Creates a INSERT statement.

public function insert(): Greg\Orm\Query\InsertQuery

Example:

$query = $connection->insert()->into('Table')->data(['Column' => 'foo']);

echo $query->toString(); // result: INSERT INTO `Table` (`Column`) VALUES (?)

delete

Creates a DELETE statement.

public function delete(): Greg\Orm\Query\DeleteQuery

Example:

$query = $connection->delete()->from('Table');

echo $query->toString(); // result: DELETE FROM `Table`

update

Creates a UPDATE statement.

public function update(): Greg\Orm\Query\UpdateQuery

Example:

$query = $connection->update()->table('Table')->set(['Column' => 'foo']);

echo $query->toString(); // result: UPDATE `Table` SET `Column` = ?

from

Creates a FROM clause.

public function from(): Greg\Orm\Clause\FromClause

Example:

$query = $connection->from()->from('Table');

echo $query->toString(); // result: FROM `Table`

join

Creates a JOIN clause.

public function join(): Greg\Orm\Clause\JoinClause

Example:

$query = $connection->join()->inner('Table');

echo $query->toString(); // result: INNER JOIN `Table`

where

Creates a WHERE clause.

public function where(): Greg\Orm\Clause\WhereClause

Example:

$query = $connection->where()->where('Column', 1);

echo $query->toString(); // result: WHERE `Column` = ?

having

Creates a HAVING clause.

public function having(): Greg\Orm\Clause\HavingClause

Example:

$query = $connection->having()->having('Column', 1);

echo $query->toString(); // result: HAVING `Column` = ?

orderBy

Creates a ORDER BY clause.

public function orderBy(): Greg\Orm\Clause\OrderByClause

Example:

$query = $connection->orderBy()->orderAsc('Column');

echo $query->toString(); // result: ORDER BY `Column` ASC

groupBy

Creates a GROUP BY clause.

public function groupBy(): Greg\Orm\Clause\GroupByClause

Example:

$query = $connection->groupBy()->groupBy('Column');

echo $query->toString(); // result: GROUP BY `Column`

limit

Creates a LIMIT clause.

public function limit(): Greg\Orm\Clause\LimitClause

Example:

$query = $connection->limit()->limit(10');

echo $query->toString(); // result: LIMIT `Column`

offset

Creates a OFFSET clause.

public function offset(): Greg\Orm\Clause\OffsetClause

Example:

$query = $connection->offset()->offset(10');

echo $query->toString(); // result: OFFSET `Column`

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

DatabaseConnection.md

DatabaseConnection.md

Database Connection

Supported Drivers

MySQL Connection

SQLite Connection

Methods

transaction

inTransaction

beginTransaction

commit

rollBack

execute

lastInsertId

quote

fetch

fetchAll

generate

column

columnAll

pairs

dialect

truncate

listen

describe

select

insert

delete

update

from

join

where

having

orderBy

groupBy

limit

offset

Files

DatabaseConnection.md

Latest commit

History

DatabaseConnection.md

File metadata and controls

Database Connection

Supported Drivers

MySQL Connection

SQLite Connection

Methods

transaction

inTransaction

beginTransaction

commit

rollBack

execute

lastInsertId

quote

fetch

fetchAll

generate

column

columnAll

pairs

dialect

truncate

listen

describe

select

insert

delete

update

from

join

where

having

orderBy

groupBy

limit

offset