Phalcon db

NOTE

All classes are prefixed with Phalcon

Db\AbstractDb ¶

Namespace
- Phalcon\Db
Uses
- \PDO
Extends
Implements

Phalcon\Db and its related classes provide a simple SQL database interface for Phalcon Framework. The Phalcon\Db is the basic class you use to connect your PHP application to an RDBMS. There is a different adapter class for each brand of RDBMS.

This component is intended to lower level database operations. If you want to interact with databases using higher level of abstraction use Phalcon\Mvc\Model.

Phalcon\Db\AbstractDb is an abstract class. You only can use it with a database adapter like Phalcon\Db\Adapter\Pdo

use Phalcon\Db;
use Phalcon\Db\Exception;
use Phalcon\Db\Adapter\Pdo\Mysql as MysqlConnection;

try {
    $connection = new MysqlConnection(
        [
            "host"     => "192.168.0.11",
            "username" => "sigma",
            "password" => "secret",
            "dbname"   => "blog",
            "port"     => "3306",
        ]
    );

    $result = $connection->query(
        "SELECTFROM robots LIMIT 5"
    );

    $result->setFetchMode(Enum::FETCH_NUM);

    while ($robot = $result->fetch()) {
        print_r($robot);
    }
} catch (Exception $e) {
    echo $e->getMessage(), PHP_EOL;
}

Methods¶

public static function setup( array $options ): void;

Enables/disables options in the Database component

Db\Adapter\AbstractAdapter ¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter
Uses
- Phalcon\Db\ColumnInterface
- Phalcon\Db\DialectInterface
- Phalcon\Db\Enum
- Phalcon\Db\Exception
- Phalcon\Db\Index
- Phalcon\Db\IndexInterface
- Phalcon\Db\RawValue
- Phalcon\Db\Reference
- Phalcon\Db\ReferenceInterface
- Phalcon\Events\EventsAwareInterface
- Phalcon\Events\ManagerInterface
Extends
Implements
- AdapterInterface
- EventsAwareInterface

Base class for Phalcon\Db\Adapter adapters

Properties¶

/**
 * Connection ID
 *
 * @var int
 */
protected $static $connectionConsecutive = ;

/**
 * Active connection ID
 *
 * @var long
 */
protected $connectionId;

/**
 * Descriptor used to connect to a database
 *
 * @var array
 */
protected $descriptor;

/**
 * Dialect instance
 *
 * @var object
 */
protected $dialect;

/**
 * Name of the dialect used
 *
 * @var string
 */
protected $dialectType;

/**
 * Event Manager
 *
 * @var ManagerInterface
 */
protected $eventsManager;

/**
 * The real SQL statement - what was executed
 *
 * @var string
 */
protected $realSqlStatement;

/**
 * Active SQL Bind Types
 *
 * @var array
 */
protected $sqlBindTypes;

/**
 * Active SQL Statement
 *
 * @var string
 */
protected $sqlStatement;

/**
 * Active SQL bound parameter variables
 *
 * @var array
 */
protected $sqlVariables;

/**
 * Current transaction level
 *
 * @var int
 */
protected $transactionLevel = ;

/**
 * Whether the database supports transactions with save points
 *
 * @var bool
 */
protected $transactionsWithSavepoints = false;

/**
 * Type of database system the adapter is used for
 *
 * @var string
 */
protected $type;

Methods¶

public function __construct( array $descriptor );

Phalcon\Db\Adapter constructor

public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): bool;

Adds a column to a table

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): bool;

Adds a foreign key to a table

public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): bool;

Adds an index to a table

public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): bool;

Adds a primary key to a table

public function createSavepoint( string $name ): bool;

Creates a new savepoint

public function createTable( string $tableName, string $schemaName, array $definition ): bool;

Creates a table

public function createView( string $viewName, array $definition, string $schemaName = null ): bool;

Creates a view

public function delete( mixed $table, mixed $whereCondition = null, mixed $placeholders = null, mixed $dataTypes = null ): bool;

Deletes data from a table using custom RBDM SQL syntax

// Deleting existing robot
$success = $connection->delete(
    "robots",
    "id = 101"
);

// Next SQL sentence is generated
DELETE FROM `robots` WHERE `id` = 101

public function describeIndexes( string $table, string $schema = null ): IndexInterface[];

Lists table indexes

print_r(
    $connection->describeIndexes("robots_parts")
);

public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];

Lists table references

print_r(
    $connection->describeReferences("robots_parts")
);

public function dropColumn( string $tableName, string $schemaName, string $columnName ): bool;

Drops a column from a table

public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): bool;

Drops a foreign key from a table

public function dropIndex( string $tableName, string $schemaName, mixed $indexName ): bool;

Drop an index from a table

public function dropPrimaryKey( string $tableName, string $schemaName ): bool;

Drops a table's primary key

public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): bool;

Drops a table from a schema/database

public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): bool;

Drops a view

public function escapeIdentifier( mixed $identifier ): string;

Escapes a column/table/schema name

$escapedTable = $connection->escapeIdentifier(
    "robots"
);

$escapedTable = $connection->escapeIdentifier(
    [
        "store",
        "robots",
    ]
);

public function fetchAll( string $sqlQuery, int $fetchMode = static-constant-access, mixed $bindParams = null, mixed $bindTypes = null ): array;

Dumps the complete result of a query into an array

// Getting all robots with associative indexes only
$robots = $connection->fetchAll(
    "SELECTFROM robots",
    \Phalcon\Db\Enum::FETCH_ASSOC
);

foreach ($robots as $robot) {
    print_r($robot);
}

 // Getting all robots that contains word "robot" withing the name
$robots = $connection->fetchAll(
    "SELECTFROM robots WHERE name LIKE :name",
    \Phalcon\Db\Enum::FETCH_ASSOC,
    [
        "name" => "%robot%",
    ]
);
foreach($robots as $robot) {
    print_r($robot);
}

public function fetchColumn( string $sqlQuery, array $placeholders = [], mixed $column = int ): string | bool;

Returns the n'th field of first row in a SQL query result

// Getting count of robots
$robotsCount = $connection->fetchColumn("SELECT count(*) FROM robots");
print_r($robotsCount);

// Getting name of last edited robot
$robot = $connection->fetchColumn(
    "SELECT id, name FROM robots ORDER BY modified DESC",
    1
);
print_r($robot);

public function fetchOne( string $sqlQuery, mixed $fetchMode = static-constant-access, mixed $bindParams = null, mixed $bindTypes = null ): array;

Returns the first row in a SQL query result

// Getting first robot
$robot = $connection->fetchOne("SELECTFROM robots");
print_r($robot);

// Getting first robot with associative indexes only
$robot = $connection->fetchOne(
    "SELECTFROM robots",
    \Phalcon\Db\Enum::FETCH_ASSOC
);
print_r($robot);

public function forUpdate( string $sqlQuery ): string;

Returns a SQL modified with a FOR UPDATE clause

public function getColumnDefinition( ColumnInterface $column ): string;

Returns the SQL column definition from a column

public function getColumnList( mixed $columnList ): string;

Gets a list of columns

public function getConnectionId(): string;

Gets the active connection unique identifier

public function getDefaultIdValue(): RawValue;

Returns the default identity value to be inserted in an identity column

// Inserting a new robot with a valid default value for the column 'id'
$success = $connection->insert(
    "robots",
    [
        $connection->getDefaultIdValue(),
        "Astro Boy",
        1952,
    ],
    [
        "id",
        "name",
        "year",
    ]
);

public function getDefaultValue(): RawValue;

Returns the default value to make the RBDM use the default value declared in the table definition

// Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
    "robots",
    [
        "Astro Boy",
        $connection->getDefaultValue()
    ],
    [
        "name",
        "year",
    ]
);

@todo Return NULL if this is not supported by the adapter

public function getDescriptor(): array;

Return descriptor used to connect to the active database

public function getDialect(): DialectInterface;

Returns internal dialect instance

public function getDialectType(): string;

Name of the dialect used

public function getEventsManager(): ManagerInterface;

Returns the internal event manager

public function getNestedTransactionSavepointName(): string;

Returns the savepoint name to use for nested transactions

public function getRealSQLStatement(): string;

Active SQL statement in the object without replace bound parameters

public function getSQLBindTypes(): array;

Active SQL statement in the object

public function getSQLStatement(): string;

Active SQL statement in the object

public function getSQLVariables(): array;

Active SQL variables in the object

public function getType(): string;

Type of database system the adapter is used for

public function insert( string $table, array $values, mixed $fields = null, mixed $dataTypes = null ): bool;

Inserts data into a table using custom RDBMS SQL syntax

// Inserting a new robot
$success = $connection->insert(
    "robots",
    ["Astro Boy", 1952],
    ["name", "year"]
);

// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);

public function insertAsDict( string $table, mixed $data, mixed $dataTypes = null ): bool;

Inserts data into a table using custom RBDM SQL syntax

// Inserting a new robot
$success = $connection->insertAsDict(
    "robots",
    [
        "name" => "Astro Boy",
        "year" => 1952,
    ]
);

// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);

public function isNestedTransactionsWithSavepoints(): bool;

Returns if nested transactions should use savepoints

public function limit( string $sqlQuery, int $number ): string;

Appends a LIMIT clause to $sqlQuery argument

echo $connection->limit("SELECTFROM robots", 5);

public function listTables( string $schemaName = null ): array;

List all tables on a database

print_r(
    $connection->listTables("blog")
);

public function listViews( string $schemaName = null ): array;

List all views on a database

print_r(
    $connection->listViews("blog")
);

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): bool;

Modifies a table column based on a definition

public function releaseSavepoint( string $name ): bool;

Releases given savepoint

public function rollbackSavepoint( string $name ): bool;

Rollbacks given savepoint

public function setDialect( DialectInterface $dialect );

Sets the dialect used to produce the SQL

public function setEventsManager( ManagerInterface $eventsManager ): void;

Sets the event manager

public function setNestedTransactionsWithSavepoints( bool $nestedTransactionsWithSavepoints ): AdapterInterface;

Set if nested transactions should use savepoints

public function sharedLock( string $sqlQuery ): string;

Returns a SQL modified with a LOCK IN SHARE MODE clause

public function supportSequences(): bool;

Check whether the database system requires a sequence to produce auto-numeric values

public function supportsDefaultValue(): bool;

Check whether the database system support the DEFAULT keyword (SQLite does not support it)

@deprecated Will re removed in the next version

public function tableExists( string $tableName, string $schemaName = null ): bool;

Generates SQL checking for the existence of a schema.table

var_dump(
    $connection->tableExists("blog", "posts")
);

public function tableOptions( string $tableName, string $schemaName = null ): array;

Gets creation options from a table

print_r(
    $connection->tableOptions("robots")
);

public function update( string $table, mixed $fields, mixed $values, mixed $whereCondition = null, mixed $dataTypes = null ): bool;

Updates data on a table using custom RBDM SQL syntax

// Updating existing robot
$success = $connection->update(
    "robots",
    ["name"],
    ["New Astro Boy"],
    "id = 101"
);

// Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101

// Updating existing robot with array condition and $dataTypes
$success = $connection->update(
    "robots",
    ["name"],
    ["New Astro Boy"],
    [
        "conditions" => "id = ?",
        "bind"       => [$some_unsafe_id],
        "bindTypes"  => [PDO::PARAM_INT], // use only if you use $dataTypes param
    ],
    [
        PDO::PARAM_STR
    ]
);

Warning! If $whereCondition is string it not escaped.

public function updateAsDict( string $table, mixed $data, mixed $whereCondition = null, mixed $dataTypes = null ): bool;

Updates data on a table using custom RBDM SQL syntax Another, more convenient syntax

// Updating existing robot
$success = $connection->updateAsDict(
    "robots",
    [
        "name" => "New Astro Boy",
    ],
    "id = 101"
);

// Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101

public function useExplicitIdValue(): bool;

Check whether the database system requires an explicit value for identity columns

public function viewExists( string $viewName, string $schemaName = null ): bool;

Generates SQL checking for the existence of a schema.view

var_dump(
    $connection->viewExists("active_users", "posts")
);

Db\Adapter\AdapterInterface ¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter
Uses
- Phalcon\Db\ColumnInterface
- Phalcon\Db\DialectInterface
- Phalcon\Db\IndexInterface
- Phalcon\Db\RawValue
- Phalcon\Db\ReferenceInterface
- Phalcon\Db\ResultInterface
Extends
Implements

Interface for Phalcon\Db adapters

Methods¶

public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): bool;

Adds a column to a table

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): bool;

Adds a foreign key to a table

public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): bool;

Adds an index to a table

public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): bool;

Adds a primary key to a table

public function affectedRows(): int;

Returns the number of affected rows by the last INSERT/UPDATE/DELETE reported by the database system

public function begin( bool $nesting = bool ): bool;

Starts a transaction in the connection

public function close(): bool;

Closes active connection returning success. Phalcon automatically closes and destroys active connections within Phalcon\Db\Pool

public function commit( bool $nesting = bool ): bool;

Commits the active transaction in the connection

public function connect( array $descriptor = null ): bool;

This method is automatically called in \Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection

public function createSavepoint( string $name ): bool;

Creates a new savepoint

public function createTable( string $tableName, string $schemaName, array $definition ): bool;

Creates a table

public function createView( string $viewName, array $definition, string $schemaName = null ): bool;

Creates a view

public function delete( mixed $table, mixed $whereCondition = null, mixed $placeholders = null, mixed $dataTypes = null ): bool;

Deletes data from a table using custom RDBMS SQL syntax

public function describeColumns( string $table, string $schema = null ): ColumnInterface[];

Returns an array of Phalcon\Db\Column objects describing a table

public function describeIndexes( string $table, string $schema = null ): IndexInterface[];

Lists table indexes

public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];

Lists table references

public function dropColumn( string $tableName, string $schemaName, string $columnName ): bool;

Drops a column from a table

public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): bool;

Drops a foreign key from a table

public function dropIndex( string $tableName, string $schemaName, string $indexName ): bool;

Drop an index from a table

public function dropPrimaryKey( string $tableName, string $schemaName ): bool;

Drops primary key from a table

public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): bool;

Drops a table from a schema/database

public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): bool;

Drops a view

public function escapeIdentifier( mixed $identifier ): string;

Escapes a column/table/schema name

public function escapeString( string $str ): string;

Escapes a value to avoid SQL injections

public function execute( string $sqlStatement, mixed $placeholders = null, mixed $dataTypes = null ): bool;

Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server doesn't return any rows

public function fetchAll( string $sqlQuery, int $fetchMode = int, mixed $placeholders = null ): array;

Dumps the complete result of a query into an array

public function fetchColumn( string $sqlQuery, array $placeholders = [], mixed $column = int ): string | bool;

Returns the n'th field of first row in a SQL query result

// Getting count of robots
$robotsCount = $connection->fetchColumn("SELECT COUNT(*) FROM robots");
print_r($robotsCount);

// Getting name of last edited robot
$robot = $connection->fetchColumn(
    "SELECT id, name FROM robots ORDER BY modified DESC",
    1
);
print_r($robot);

public function fetchOne( string $sqlQuery, int $fetchMode = int, mixed $placeholders = null ): array;

Returns the first row in a SQL query result

public function forUpdate( string $sqlQuery ): string;

Returns a SQL modified with a FOR UPDATE clause

public function getColumnDefinition( ColumnInterface $column ): string;

Returns the SQL column definition from a column

public function getColumnList( mixed $columnList ): string;

Gets a list of columns

public function getConnectionId(): string;

Gets the active connection unique identifier

public function getDefaultIdValue(): RawValue;

Return the default identity value to insert in an identity column

public function getDefaultValue(): RawValue;

Returns the default value to make the RBDM use the default value declared in the table definition

// Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
    "robots",
    [
        "Astro Boy",
        $connection->getDefaultValue()
    ],
    [
        "name",
        "year",
    ]
);

@todo Return NULL if this is not supported by the adapter

public function getDescriptor(): array;

Return descriptor used to connect to the active database

public function getDialect(): DialectInterface;

Returns internal dialect instance

public function getDialectType(): string;

Returns the name of the dialect used

public function getInternalHandler(): \PDO;

Return internal PDO handler

public function getNestedTransactionSavepointName(): string;

Returns the savepoint name to use for nested transactions

public function getRealSQLStatement(): string;

Active SQL statement in the object without replace bound parameters

public function getSQLBindTypes(): array;

Active SQL statement in the object

public function getSQLStatement(): string;

Active SQL statement in the object

public function getSQLVariables(): array;

Active SQL statement in the object

public function getType(): string;

Returns type of database system the adapter is used for

public function insert( string $table, array $values, mixed $fields = null, mixed $dataTypes = null ): bool;

Inserts data into a table using custom RDBMS SQL syntax

public function insertAsDict( string $table, mixed $data, mixed $dataTypes = null ): bool;

Inserts data into a table using custom RBDM SQL syntax

// Inserting a new robot
$success = $connection->insertAsDict(
    "robots",
    [
        "name" => "Astro Boy",
        "year" => 1952,
    ]
);

// Next SQL sentence is sent to the database system
INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);

public function isNestedTransactionsWithSavepoints(): bool;

Returns if nested transactions should use savepoints

public function isUnderTransaction(): bool;

Checks whether connection is under database transaction

public function lastInsertId( mixed $sequenceName = null );

Returns insert id for the auto_increment column inserted in the last SQL statement

public function limit( string $sqlQuery, int $number ): string;

Appends a LIMIT clause to sqlQuery argument

public function listTables( string $schemaName = null ): array;

List all tables on a database

public function listViews( string $schemaName = null ): array;

List all views on a database

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): bool;

Modifies a table column based on a definition

public function query( string $sqlStatement, mixed $placeholders = null, mixed $dataTypes = null ): ResultInterface | bool;

Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server returns rows

public function releaseSavepoint( string $name ): bool;

Releases given savepoint

public function rollback( bool $nesting = bool ): bool;

Rollbacks the active transaction in the connection

public function rollbackSavepoint( string $name ): bool;

Rollbacks given savepoint

public function setNestedTransactionsWithSavepoints( bool $nestedTransactionsWithSavepoints ): AdapterInterface;

Set if nested transactions should use savepoints

public function sharedLock( string $sqlQuery ): string;

Returns a SQL modified with a LOCK IN SHARE MODE clause

public function supportSequences(): bool;

Check whether the database system requires a sequence to produce auto-numeric values

public function supportsDefaultValue(): bool;

SQLite does not support the DEFAULT keyword

@deprecated Will re removed in the next version

public function tableExists( string $tableName, string $schemaName = null ): bool;

Generates SQL checking for the existence of a schema.table

public function tableOptions( string $tableName, string $schemaName = null ): array;

Gets creation options from a table

public function update( string $table, mixed $fields, mixed $values, mixed $whereCondition = null, mixed $dataTypes = null ): bool;

Updates data on a table using custom RDBMS SQL syntax

public function updateAsDict( string $table, mixed $data, mixed $whereCondition = null, mixed $dataTypes = null ): bool;

Updates data on a table using custom RBDM SQL syntax Another, more convenient syntax

// Updating existing robot
$success = $connection->updateAsDict(
    "robots",
    [
        "name" => "New Astro Boy",
    ],
    "id = 101"
);

// Next SQL sentence is sent to the database system
UPDATE `robots` SET `name` = "Astro boy" WHERE id = 101

public function useExplicitIdValue(): bool;

Check whether the database system requires an explicit value for identity columns

public function viewExists( string $viewName, string $schemaName = null ): bool;

Generates SQL checking for the existence of a schema.view

Db\Adapter\Pdo\AbstractPdo ¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter\Pdo
Uses
- Phalcon\Db\Adapter\AbstractAdapter
- Phalcon\Db\Column
- Phalcon\Db\Exception
- Phalcon\Db\ResultInterface
- Phalcon\Db\Result\PdoResult
- Phalcon\Events\ManagerInterface
Extends

AbstractAdapter
Implements

Phalcon\Db\Adapter\Pdo is the Phalcon\Db that internally uses PDO to connect to a database

use Phalcon\Db\Adapter\Pdo\Mysql;

$config = [
    "host"     => "localhost",
    "dbname"   => "blog",
    "port"     => 3306,
    "username" => "sigma",
    "password" => "secret",
];

$connection = new Mysql($config);

Properties¶

/**
 * Last affected rows
 *
 * @var int
 */
protected $affectedRows;

/**
 * PDO Handler
 *
 * @var \PDO
 */
protected $pdo;

Methods¶

public function __construct( array $descriptor );

Constructor for Phalcon\Db\Adapter\Pdo

public function affectedRows(): int;

Returns the number of affected rows by the latest INSERT/UPDATE/DELETE executed in the database system

$connection->execute(
    "DELETE FROM robots"
);

echo $connection->affectedRows(), " were deleted";

public function begin( bool $nesting = bool ): bool;

Starts a transaction in the connection

public function close(): bool;

Closes the active connection returning success. Phalcon automatically closes and destroys active connections when the request ends

public function commit( bool $nesting = bool ): bool;

Commits the active transaction in the connection

public function connect( array $descriptor = null ): bool;

This method is automatically called in \Phalcon\Db\Adapter\Pdo constructor.

Call it when you need to restore a database connection.

use Phalcon\Db\Adapter\Pdo\Mysql;

// Make a connection
$connection = new Mysql(
    [
        "host"     => "localhost",
        "username" => "sigma",
        "password" => "secret",
        "dbname"   => "blog",
        "port"     => 3306,
    ]
);

// Reconnect
$connection->connect();

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

Converts bound parameters such as :name: or ?1 into PDO bind params ?

print_r(
    $connection->convertBoundParams(
        "SELECTFROM robots WHERE name = :name:",
        [
            "Bender",
        ]
    )
);

public function escapeString( string $str ): string;

Escapes a value to avoid SQL injections according to the active charset in the connection

$escapedStr = $connection->escapeString("some dangerous value");

public function execute( string $sqlStatement, mixed $bindParams = null, mixed $bindTypes = null ): bool;

Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server doesn't return any rows

// Inserting data
$success = $connection->execute(
    "INSERT INTO robots VALUES (1, 'Astro Boy')"
);

$success = $connection->execute(
    "INSERT INTO robots VALUES (?, ?)",
    [
        1,
        "Astro Boy",
    ]
);

public function executePrepared( \PDOStatement $statement, array $placeholders, mixed $dataTypes ): \PDOStatement;

Executes a prepared statement binding. This function uses integer indexes starting from zero

use Phalcon\Db\Column;

$statement = $db->prepare(
    "SELECTFROM robots WHERE name = :name"
);

$result = $connection->executePrepared(
    $statement,
    [
        "name" => "Voltron",
    ],
    [
        "name" => Column::BIND_PARAM_STR,
    ]
);

public function getErrorInfo();

Return the error info, if any

public function getInternalHandler(): \PDO;

Return internal PDO handler

public function getTransactionLevel(): int;

Returns the current transaction nesting level

public function isUnderTransaction(): bool;

Checks whether the connection is under a transaction

$connection->begin();

// true
var_dump(
    $connection->isUnderTransaction()
);

public function lastInsertId( mixed $sequenceName = null ): int | bool;

Returns the insert id for the auto_increment/serial column inserted in the latest executed SQL statement

// Inserting a new robot
$success = $connection->insert(
    "robots",
    [
        "Astro Boy",
        1952,
    ],
    [
        "name",
        "year",
    ]
);

// Getting the generated id
$id = $connection->lastInsertId();

public function prepare( string $sqlStatement ): \PDOStatement;

Returns a PDO prepared statement to be executed with 'executePrepared'

use Phalcon\Db\Column;

$statement = $db->prepare(
    "SELECTFROM robots WHERE name = :name"
);

$result = $connection->executePrepared(
    $statement,
    [
        "name" => "Voltron",
    ],
    [
        "name" => Column::BIND_PARAM_INT,
    ]
);

public function query( string $sqlStatement, mixed $bindParams = null, mixed $bindTypes = null ): ResultInterface | bool;

Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server is returning rows

// Querying data
$resultset = $connection->query(
    "SELECTFROM robots WHERE type = 'mechanical'"
);

$resultset = $connection->query(
    "SELECTFROM robots WHERE type = ?",
    [
        "mechanical",
    ]
);

public function rollback( bool $nesting = bool ): bool;

Rollbacks the active transaction in the connection

abstract protected function getDsnDefaults(): array;

Returns PDO adapter DSN defaults as a key-value map.

protected function prepareRealSql( string $statement, array $parameters ): void;

Constructs the SQL statement (with parameters)

@see https://stackoverflow.com/a/8403150

Db\Adapter\Pdo\Mysql¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter\Pdo
Uses
- Phalcon\Db\Adapter\Pdo\AbstractPdo
- Phalcon\Db\Column
- Phalcon\Db\ColumnInterface
- Phalcon\Db\Enum
- Phalcon\Db\Exception
- Phalcon\Db\Index
- Phalcon\Db\IndexInterface
- Phalcon\Db\Reference
- Phalcon\Db\ReferenceInterface
Extends

PdoAdapter
Implements

Specific functions for the MySQL database system

use Phalcon\Db\Adapter\Pdo\Mysql;

$config = [
    "host"     => "localhost",
    "dbname"   => "blog",
    "port"     => 3306,
    "username" => "sigma",
    "password" => "secret",
];

$connection = new Mysql($config);

Properties¶

/**
 * @var string
 */
protected $dialectType = mysql;

/**
 * @var string
 */
protected $type = mysql;

Methods¶

public function __construct( array $descriptor );

Constructor for Phalcon\Db\Adapter\Pdo

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): bool;

Adds a foreign key to a table

public function describeColumns( string $table, string $schema = null ): ColumnInterface[];

Returns an array of Phalcon\Db\Column objects describing a table

print_r(
    $connection->describeColumns("posts")
);

public function describeIndexes( string $table, string $schema = null ): IndexInterface[];

Lists table indexes

print_r(
    $connection->describeIndexes("robots_parts")
);

public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];

Lists table references

print_r(
    $connection->describeReferences("robots_parts")
);

protected function getDsnDefaults(): array;

Returns PDO adapter DSN defaults as a key-value map.

Db\Adapter\Pdo\Postgresql¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter\Pdo
Uses
- Phalcon\Db\Adapter\Pdo\AbstractPdo
- Phalcon\Db\Column
- Phalcon\Db\ColumnInterface
- Phalcon\Db\Enum
- Phalcon\Db\Exception
- Phalcon\Db\RawValue
- Phalcon\Db\Reference
- Phalcon\Db\ReferenceInterface
- Throwable
Extends

PdoAdapter
Implements

Specific functions for the PostgreSQL database system

use Phalcon\Db\Adapter\Pdo\Postgresql;

$config = [
    "host"     => "localhost",
    "dbname"   => "blog",
    "port"     => 5432,
    "username" => "postgres",
    "password" => "secret",
];

$connection = new Postgresql($config);

Properties¶

/**
 * @var string
 */
protected $dialectType = 'postgresql';

/**
 * @var string
 */
protected $type = 'pgsql';

Methods¶

public function __construct( array $descriptor );

Constructor for Phalcon\Db\Adapter\Pdo\Postgresql

public function connect( array $descriptor = null ): bool;

This method is automatically called in Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection.

public function createTable( string $tableName, string $schemaName, array $definition ): bool;

Creates a table

public function describeColumns( string $table, string $schema = null ): ColumnInterface[];

Returns an array of Phalcon\Db\Column objects describing a table

print_r(
    $connection->describeColumns("posts")
);

public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];

Lists table references

print_r(
    $connection->describeReferences("robots_parts")
);

public function getDefaultIdValue(): RawValue;

Returns the default identity value to be inserted in an identity column

// Inserting a new robot with a valid default value for the column 'id'
$success = $connection->insert(
    "robots",
    [
        $connection->getDefaultIdValue(),
        "Astro Boy",
        1952,
    ],
    [
        "id",
        "name",
        "year",
    ]
);

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): bool;

Modifies a table column based on a definition

public function supportSequences(): bool;

Check whether the database system requires a sequence to produce auto-numeric values

public function useExplicitIdValue(): bool;

Check whether the database system requires an explicit value for identity columns

protected function getDsnDefaults(): array;

Returns PDO adapter DSN defaults as a key-value map.

Db\Adapter\Pdo\Sqlite¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter\Pdo
Uses
- Phalcon\Db\Adapter\Pdo\AbstractPdo
- Phalcon\Db\Column
- Phalcon\Db\ColumnInterface
- Phalcon\Db\Enum
- Phalcon\Db\Exception
- Phalcon\Db\Index
- Phalcon\Db\IndexInterface
- Phalcon\Db\RawValue
- Phalcon\Db\Reference
- Phalcon\Db\ReferenceInterface
Extends

PdoAdapter
Implements

Specific functions for the SQLite database system

use Phalcon\Db\Adapter\Pdo\Sqlite;

$connection = new Sqlite(
    [
        "dbname" => "/tmp/test.sqlite",
    ]
);

Properties¶

/**
 * @var string
 */
protected $dialectType = 'sqlite';

/**
 * @var string
 */
protected $type = 'sqlite';

Methods¶

public function __construct( array $descriptor );

Constructor for Phalcon\Db\Adapter\Pdo\Sqlite

public function connect( array $descriptor = null ): bool;

This method is automatically called in Phalcon\Db\Adapter\Pdo constructor. Call it when you need to restore a database connection.

public function describeColumns( string $table, string $schema = null ): ColumnInterface[];

Returns an array of Phalcon\Db\Column objects describing a table

print_r(
    $connection->describeColumns("posts")
);

public function describeIndexes( string $table, string $schema = null ): IndexInterface[];

Lists table indexes

print_r(
    $connection->describeIndexes("robots_parts")
);

public function describeReferences( string $table, string $schema = null ): ReferenceInterface[];

Lists table references

public function getDefaultValue(): RawValue;

Returns the default value to make the RBDM use the default value declared in the table definition

// Inserting a new robot with a valid default value for the column 'year'
$success = $connection->insert(
    "robots",
    [
        "Astro Boy",
        $connection->getDefaultValue(),
    ],
    [
        "name",
        "year",
    ]
);

public function supportsDefaultValue(): bool;

SQLite does not support the DEFAULT keyword

@deprecated Will re removed in the next version

public function useExplicitIdValue(): bool;

Check whether the database system requires an explicit value for identity columns

protected function getDsnDefaults(): array;

Returns PDO adapter DSN defaults as a key-value map.

Db\Adapter\PdoFactory¶

Source on GitHub

Namespace
- Phalcon\Db\Adapter
Uses
- Phalcon\Factory\AbstractFactory
- Phalcon\Helper\Arr\Get
Extends

AbstractFactory
Implements

This file is part of the Phalcon Framework.

(c) Phalcon Team team@phalcon.io

For the full copyright and license information, please view the LICENSE.txt file that was distributed with this source code.

Methods¶

public function __construct( array $services = [] );

Constructor

public function load( mixed $config ): AdapterInterface;

Factory to create an instance from a Config object

public function newInstance( string $name, array $options = [] ): AdapterInterface;

Create a new instance of the adapter

protected function getAdapters(): array;

Returns the available adapters

Db\Column¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements
- ColumnInterface

Allows to define columns to be used on create or alter table operations

use Phalcon\Db\Column as Column;

// Column definition
$column = new Column(
    "id",
    [
        "type"          => Column::TYPE_INTEGER,
        "size"          => 10,
        "unsigned"      => true,
        "notNull"       => true,
        "autoIncrement" => true,
        "first"         => true,
        "comment"       => "",
    ]
);

// Add column to existing table
$connection->addColumn("robots", null, $column);

Constants¶

const BIND_PARAM_BLOB = 3;
const BIND_PARAM_BOOL = 5;
const BIND_PARAM_DECIMAL = 32;
const BIND_PARAM_INT = 1;
const BIND_PARAM_NULL = 0;
const BIND_PARAM_STR = 2;
const BIND_SKIP = 1024;
const TYPE_BIGINTEGER = 14;
const TYPE_BIT = 19;
const TYPE_BLOB = 11;
const TYPE_BOOLEAN = 8;
const TYPE_CHAR = 5;
const TYPE_DATE = 1;
const TYPE_DATETIME = 4;
const TYPE_DECIMAL = 3;
const TYPE_DOUBLE = 9;
const TYPE_ENUM = 18;
const TYPE_FLOAT = 7;
const TYPE_INTEGER = 0;
const TYPE_JSON = 15;
const TYPE_JSONB = 16;
const TYPE_LONGBLOB = 13;
const TYPE_LONGTEXT = 24;
const TYPE_MEDIUMBLOB = 12;
const TYPE_MEDIUMINTEGER = 21;
const TYPE_MEDIUMTEXT = 23;
const TYPE_SMALLINTEGER = 22;
const TYPE_TEXT = 6;
const TYPE_TIME = 20;
const TYPE_TIMESTAMP = 17;
const TYPE_TINYBLOB = 10;
const TYPE_TINYINTEGER = 26;
const TYPE_TINYTEXT = 25;
const TYPE_VARCHAR = 2;

Properties¶

/**
 * Column Position
 *
 * @var string|null
 */
protected $after;

/**
 * Column is autoIncrement?
 *
 * @var bool
 */
protected $autoIncrement = false;

/**
 * Bind Type
 *
 * @var int
 */
protected $bindType = 2;

/**
 * Default column value
 */
protected $_default;

/**
 * Position is first
 *
 * @var bool
 */
protected $first = false;

/**
 * The column have some numeric type?
 */
protected $isNumeric = false;

/**
 * Column's name
 *
 * @var string
 */
protected $name;

/**
 * Column's comment
 *
 * @var string
 */
protected $comment;

/**
 * Column not nullable?
 *
 * Default SQL definition is NOT NULL.
 *
 * @var bool
 */
protected $notNull = true;

/**
 * Column is part of the primary key?
 *
 * @var bool
 */
protected $primary = false;

/**
 * Integer column number scale
 *
 * @var int
 */
protected $scale = 0;

/**
 * Integer column size
 *
 * @var int|string
 */
protected $size = 0;

/**
 * Column data type
 *
 * @var int
 */
protected $type;

/**
 * Column data type reference
 *
 * @var int
 */
protected $typeReference = -1;

/**
 * Column data type values
 *
 * @var array|string
 */
protected $typeValues;

/**
 * Integer column unsigned?
 *
 * @var bool
 */
protected $unsigned = false;

Methods¶

public function __construct( string $name, array $definition );

Phalcon\Db\Column constructor

public function getAfterPosition(): string | null;

Check whether field absolute to position in table

public function getBindType(): int;

Returns the type of bind handling

public function getComment(): string

Column's comment

public function getName(): string

public function getScale(): int

public function getSize(): int | string

public function getType(): int

public function getTypeReference(): int

public function getTypeValues(): array|string

public function get_default()

public function hasDefault(): bool;

Check whether column has default value

public function isAutoIncrement(): bool;

Auto-Increment

public function isFirst(): bool;

Check whether column have first position in table

public function isNotNull(): bool;

Not null

public function isNumeric(): bool;

Check whether column have an numeric type

public function isPrimary(): bool;

Column is part of the primary key?

public function isUnsigned(): bool;

Returns true if number column is unsigned

Db\ColumnInterface ¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

Interface for Phalcon\Db\Column

Methods¶

public function getAfterPosition(): string | null;

Check whether field absolute to position in table

public function getBindType(): int;

Returns the type of bind handling

public function getDefault(): mixed;

Returns default value of column

public function getName(): string;

Returns column name

public function getScale(): int;

Returns column scale

public function getSize(): int | string;

Returns column size

public function getType(): int;

Returns column type

public function getTypeReference(): int;

Returns column type reference

public function getTypeValues(): array | string;

Returns column type values

public function hasDefault(): bool;

Check whether column has default value

public function isAutoIncrement(): bool;

Auto-Increment

public function isFirst(): bool;

Check whether column have first position in table

public function isNotNull(): bool;

Not null

public function isNumeric(): bool;

Check whether column have an numeric type

public function isPrimary(): bool;

Column is part of the primary key?

public function isUnsigned(): bool;

Returns true if number column is unsigned

Db\Dialect ¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements
- DialectInterface

This is the base class to each database dialect. This implements common methods to transform intermediate code into its RDBMS related syntax

Properties¶

/**
 * @var string
 */
protected $escapeChar;

/**
 * @var array
 */
protected $customFunctions;

Methods¶

public function createSavepoint( string $name ): string;

Generate SQL to create a new savepoint

final public function escape( string $str, string $escapeChar = null ): string;

Escape identifiers

final public function escapeSchema( string $str, string $escapeChar = null ): string;

Escape Schema

public function forUpdate( string $sqlQuery ): string;

Returns a SQL modified with a FOR UPDATE clause

$sql = $dialect->forUpdate("SELECTFROM robots");

echo $sql; // SELECTFROM robots FOR UPDATE

final public function getColumnList( array $columnList, string $escapeChar = null, mixed $bindCounts = null ): string;

Gets a list of columns with escaped identifiers

echo $dialect->getColumnList(
    [
        "column1",
        "column",
    ]
);

public function getCustomFunctions(): array;

Returns registered functions

final public function getSqlColumn( mixed $column, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve Column expressions

public function getSqlExpression( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Transforms an intermediate representation for an expression into a database system valid expression

final public function getSqlTable( mixed $table, string $escapeChar = null ): string;

Transform an intermediate representation of a schema/table into a database system valid expression

public function limit( string $sqlQuery, mixed $number ): string;

Generates the SQL for LIMIT clause

// SELECTFROM robots LIMIT 10
echo $dialect->limit(
    "SELECTFROM robots",
    10
);

// SELECTFROM robots LIMIT 10 OFFSET 50
echo $dialect->limit(
    "SELECTFROM robots",
    [10, 50]
);

public function registerCustomFunction( string $name, callable $customFunction ): Dialect;

Registers custom SQL functions

public function releaseSavepoint( string $name ): string;

Generate SQL to release a savepoint

public function rollbackSavepoint( string $name ): string;

Generate SQL to rollback a savepoint

public function select( array $definition ): string;

Builds a SELECT statement

public function supportsReleaseSavepoints(): bool;

Checks whether the platform supports releasing savepoints.

public function supportsSavepoints(): bool;

Checks whether the platform supports savepoints

protected function checkColumnType( ColumnInterface $column ): string;

Checks the column type and if not string it returns the type reference

protected function checkColumnTypeSql( ColumnInterface $column ): string;

Checks the column type and returns the updated SQL statement

protected function getColumnSize( ColumnInterface $column ): string;

Returns the size of the column enclosed in parentheses

protected function getColumnSizeAndScale( ColumnInterface $column ): string;

Returns the column size and scale enclosed in parentheses

final protected function getSqlExpressionAll( array $expression, string $escapeChar = null ): string;

Resolve

final protected function getSqlExpressionBinaryOperations( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve binary operations expressions

final protected function getSqlExpressionCase( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve CASE expressions

final protected function getSqlExpressionCastValue( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve CAST of values

final protected function getSqlExpressionConvertValue( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve CONVERT of values encodings

final protected function getSqlExpressionFrom( mixed $expression, string $escapeChar = null ): string;

Resolve a FROM clause

final protected function getSqlExpressionFunctionCall( array $expression, string $escapeChar = null, mixed $bindCounts ): string;

Resolve function calls

final protected function getSqlExpressionGroupBy( mixed $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve a GROUP BY clause

final protected function getSqlExpressionHaving( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve a HAVING clause

final protected function getSqlExpressionJoins( mixed $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve a JOINs clause

final protected function getSqlExpressionLimit( mixed $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve a LIMIT clause

final protected function getSqlExpressionList( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve Lists

final protected function getSqlExpressionObject( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve object expressions

final protected function getSqlExpressionOrderBy( mixed $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve an ORDER BY clause

final protected function getSqlExpressionQualified( array $expression, string $escapeChar = null ): string;

Resolve qualified expressions

final protected function getSqlExpressionScalar( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve Column expressions

final protected function getSqlExpressionUnaryOperations( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve unary operations expressions

final protected function getSqlExpressionWhere( mixed $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Resolve a WHERE clause

protected function prepareColumnAlias( string $qualified, string $alias = null, string $escapeChar = null ): string;

Prepares column for this RDBMS

protected function prepareQualified( string $column, string $domain = null, string $escapeChar = null ): string;

Prepares qualified for this RDBMS

protected function prepareTable( string $table, string $schema = null, string $alias = null, string $escapeChar = null ): string;

Prepares table for this RDBMS

Db\Dialect\Mysql¶

Source on GitHub

Namespace
- Phalcon\Db\Dialect
Uses
- Phalcon\Db\Column
- Phalcon\Db\ColumnInterface
- Phalcon\Db\Dialect
- Phalcon\Db\DialectInterface
- Phalcon\Db\Exception
- Phalcon\Db\IndexInterface
- Phalcon\Db\ReferenceInterface
Extends

Dialect
Implements

Generates database specific SQL for the MySQL RDBMS

Properties¶

/**
 * @var string
 */
protected $escapeChar = '`';

Methods¶

public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;

Generates SQL to add a column to a table

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;

Generates SQL to add an index to a table

public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add an index to a table

public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add the primary key to a table

public function createTable( string $tableName, string $schemaName, array $definition ): string;

Generates SQL to create a table

public function createView( string $viewName, array $definition, string $schemaName = null ): string;

Generates SQL to create a view

public function describeColumns( string $table, string $schema = null ): string;

Generates SQL describing a table

print_r(
    $dialect->describeColumns("posts")
);

public function describeIndexes( string $table, string $schema = null ): string;

Generates SQL to query indexes on a table

public function describeReferences( string $table, string $schema = null ): string;

Generates SQL to query foreign keys on a table

public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;

Generates SQL to delete a column from a table

public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;

Generates SQL to delete a foreign key from a table

public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;

Generates SQL to delete an index from a table

public function dropPrimaryKey( string $tableName, string $schemaName ): string;

Generates SQL to delete primary key from a table

public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a table

public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a view

public function getColumnDefinition( ColumnInterface $column ): string;

Gets the column name in MySQL

public function getForeignKeyChecks(): string;

Generates SQL to check DB parameter FOREIGN_KEY_CHECKS.

public function listTables( string $schemaName = null ): string;

List all tables in database

print_r(
    $dialect->listTables("blog")
);

public function listViews( string $schemaName = null ): string;

Generates the SQL to list all views of a schema or user

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;

Generates SQL to modify a column in a table

public function sharedLock( string $sqlQuery ): string;

Returns a SQL modified with a LOCK IN SHARE MODE clause

$sql = $dialect->sharedLock("SELECTFROM robots");

echo $sql; // SELECTFROM robots LOCK IN SHARE MODE

public function tableExists( string $tableName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.table

echo $dialect->tableExists("posts", "blog");

echo $dialect->tableExists("posts");

public function tableOptions( string $table, string $schema = null ): string;

Generates the SQL to describe the table creation options

public function truncateTable( string $tableName, string $schemaName ): string;

Generates SQL to truncate a table

public function viewExists( string $viewName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.view

protected function getTableOptions( array $definition ): string;

Generates SQL to add the table creation options

Db\Dialect\Postgresql¶

Source on GitHub

Namespace
- Phalcon\Db\Dialect
Uses
- Phalcon\Db\Column
- Phalcon\Db\ColumnInterface
- Phalcon\Db\Dialect
- Phalcon\Db\DialectInterface
- Phalcon\Db\Exception
- Phalcon\Db\IndexInterface
- Phalcon\Db\ReferenceInterface
Extends

Dialect
Implements

Generates database specific SQL for the PostgreSQL RDBMS

Properties¶

/**
 * @var string
 */
protected $escapeChar = "\"";

Methods¶

public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;

Generates SQL to add a column to a table

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;

Generates SQL to add an index to a table

public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add an index to a table

public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add the primary key to a table

public function createTable( string $tableName, string $schemaName, array $definition ): string;

Generates SQL to create a table

public function createView( string $viewName, array $definition, string $schemaName = null ): string;

Generates SQL to create a view

public function describeColumns( string $table, string $schema = null ): string;

Generates SQL describing a table

print_r(
    $dialect->describeColumns("posts")
);

public function describeIndexes( string $table, string $schema = null ): string;

Generates SQL to query indexes on a table

public function describeReferences( string $table, string $schema = null ): string;

Generates SQL to query foreign keys on a table

public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;

Generates SQL to delete a column from a table

public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;

Generates SQL to delete a foreign key from a table

public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;

Generates SQL to delete an index from a table

public function dropPrimaryKey( string $tableName, string $schemaName ): string;

Generates SQL to delete primary key from a table

public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a table

public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a view

public function getColumnDefinition( ColumnInterface $column ): string;

Gets the column name in PostgreSQL

public function listTables( string $schemaName = null ): string;

List all tables in database

print_r(
    $dialect->listTables("blog")
);

public function listViews( string $schemaName = null ): string;

Generates the SQL to list all views of a schema or user

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;

Generates SQL to modify a column in a table

public function sharedLock( string $sqlQuery ): string;

Returns a SQL modified a shared lock statement. For now this method returns the original query

public function tableExists( string $tableName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.table

echo $dialect->tableExists("posts", "blog");

echo $dialect->tableExists("posts");

public function tableOptions( string $table, string $schema = null ): string;

Generates the SQL to describe the table creation options

public function truncateTable( string $tableName, string $schemaName ): string;

Generates SQL to truncate a table

public function viewExists( string $viewName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.view

protected function castDefault( ColumnInterface $column ): string;

protected function getTableOptions( array $definition ): string;

Db\Dialect\Sqlite¶

Source on GitHub

Namespace
- Phalcon\Db\Dialect
Uses
- Phalcon\Db\Column
- Phalcon\Db\ColumnInterface
- Phalcon\Db\Dialect
- Phalcon\Db\DialectInterface
- Phalcon\Db\Exception
- Phalcon\Db\IndexInterface
- Phalcon\Db\ReferenceInterface
Extends

Dialect
Implements

Generates database specific SQL for the SQLite RDBMS

Properties¶

/**
 * @var string
 */
protected $escapeChar = "\"";

Methods¶

public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;

Generates SQL to add a column to a table

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;

Generates SQL to add an index to a table

public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add an index to a table

public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add the primary key to a table

public function createTable( string $tableName, string $schemaName, array $definition ): string;

Generates SQL to create a table

public function createView( string $viewName, array $definition, string $schemaName = null ): string;

Generates SQL to create a view

public function describeColumns( string $table, string $schema = null ): string;

Generates SQL describing a table

print_r(
    $dialect->describeColumns("posts")
);

public function describeIndex( string $index ): string;

Generates SQL to query indexes detail on a table

public function describeIndexes( string $table, string $schema = null ): string;

Generates SQL to query indexes on a table

public function describeReferences( string $table, string $schema = null ): string;

Generates SQL to query foreign keys on a table

public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;

Generates SQL to delete a column from a table

public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;

Generates SQL to delete a foreign key from a table

public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;

Generates SQL to delete an index from a table

public function dropPrimaryKey( string $tableName, string $schemaName ): string;

Generates SQL to delete primary key from a table

public function dropTable( string $tableName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a table

public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a view

public function forUpdate( string $sqlQuery ): string;

Returns a SQL modified with a FOR UPDATE clause. For SQLite it returns the original query

public function getColumnDefinition( ColumnInterface $column ): string;

Gets the column name in SQLite

public function listIndexesSql( string $table, string $schema = null, string $keyName = null ): string;

Generates the SQL to get query list of indexes

print_r(
    $dialect->listIndexesSql("blog")
);

public function listTables( string $schemaName = null ): string;

List all tables in database

print_r(
    $dialect->listTables("blog")
);

public function listViews( string $schemaName = null ): string;

Generates the SQL to list all views of a schema or user

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;

Generates SQL to modify a column in a table

public function sharedLock( string $sqlQuery ): string;

Returns a SQL modified a shared lock statement. For now this method returns the original query

public function tableExists( string $tableName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.table

echo $dialect->tableExists("posts", "blog");

echo $dialect->tableExists("posts");

public function tableOptions( string $table, string $schema = null ): string;

Generates the SQL to describe the table creation options

public function truncateTable( string $tableName, string $schemaName ): string;

Generates SQL to truncate a table

public function viewExists( string $viewName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.view

Db\DialectInterface ¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

Interface for Phalcon\Db dialects

Methods¶

public function addColumn( string $tableName, string $schemaName, ColumnInterface $column ): string;

Generates SQL to add a column to a table

public function addForeignKey( string $tableName, string $schemaName, ReferenceInterface $reference ): string;

Generates SQL to add an index to a table

public function addIndex( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add an index to a table

public function addPrimaryKey( string $tableName, string $schemaName, IndexInterface $index ): string;

Generates SQL to add the primary key to a table

public function createSavepoint( string $name ): string;

Generate SQL to create a new savepoint

public function createTable( string $tableName, string $schemaName, array $definition ): string;

Generates SQL to create a table

public function createView( string $viewName, array $definition, string $schemaName = null ): string;

Generates SQL to create a view

public function describeColumns( string $table, string $schema = null ): string;

Generates SQL to describe a table

public function describeIndexes( string $table, string $schema = null ): string;

Generates SQL to query indexes on a table

public function describeReferences( string $table, string $schema = null ): string;

Generates SQL to query foreign keys on a table

public function dropColumn( string $tableName, string $schemaName, string $columnName ): string;

Generates SQL to delete a column from a table

public function dropForeignKey( string $tableName, string $schemaName, string $referenceName ): string;

Generates SQL to delete a foreign key from a table

public function dropIndex( string $tableName, string $schemaName, string $indexName ): string;

Generates SQL to delete an index from a table

public function dropPrimaryKey( string $tableName, string $schemaName ): string;

Generates SQL to delete primary key from a table

public function dropTable( string $tableName, string $schemaName ): string;

Generates SQL to drop a table

public function dropView( string $viewName, string $schemaName = null, bool $ifExists = bool ): string;

Generates SQL to drop a view

public function forUpdate( string $sqlQuery ): string;

Returns a SQL modified with a FOR UPDATE clause

public function getColumnDefinition( ColumnInterface $column ): string;

Gets the column name in RDBMS

public function getColumnList( array $columnList ): string;

Gets a list of columns

public function getCustomFunctions(): array;

Returns registered functions

public function getSqlExpression( array $expression, string $escapeChar = null, mixed $bindCounts = null ): string;

Transforms an intermediate representation for an expression into a database system valid expression

public function limit( string $sqlQuery, mixed $number ): string;

Generates the SQL for LIMIT clause

public function listTables( string $schemaName = null ): string;

List all tables in database

public function modifyColumn( string $tableName, string $schemaName, ColumnInterface $column, ColumnInterface $currentColumn = null ): string;

Generates SQL to modify a column in a table

public function registerCustomFunction( string $name, callable $customFunction ): Dialect;

Registers custom SQL functions

public function releaseSavepoint( string $name ): string;

Generate SQL to release a savepoint

public function rollbackSavepoint( string $name ): string;

Generate SQL to rollback a savepoint

public function select( array $definition ): string;

Builds a SELECT statement

public function sharedLock( string $sqlQuery ): string;

Returns a SQL modified with a LOCK IN SHARE MODE clause

public function supportsReleaseSavepoints(): bool;

Checks whether the platform supports releasing savepoints.

public function supportsSavepoints(): bool;

Checks whether the platform supports savepoints

public function tableExists( string $tableName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.table

public function tableOptions( string $table, string $schema = null ): string;

Generates the SQL to describe the table creation options

public function viewExists( string $viewName, string $schemaName = null ): string;

Generates SQL checking for the existence of a schema.view

Db\Enum¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

Constants for Phalcon\Db

Constants¶

const FETCH_ASSOC;
const FETCH_BOTH;
const FETCH_BOUND;
const FETCH_CLASS;
const FETCH_CLASSTYPE;
const FETCH_COLUMN;
const FETCH_FUNC;
const FETCH_GROUP;
const FETCH_INTO;
const FETCH_KEY_PAIR;
const FETCH_LAZY;
const FETCH_NAMED;
const FETCH_NUM;
const FETCH_OBJ;
const FETCH_PROPS_LATE;
const FETCH_SERIALIZE;
const FETCH_UNIQUE;

Db\Exception¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends

\Exception
Implements

Exceptions thrown in Phalcon\Db will use this class

Db\Index¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements
- IndexInterface

Allows to define indexes to be used on tables. Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index

// Define new unique index
$index_unique = new \Phalcon\Db\Index(
    'column_UNIQUE',
    [
        'column',
        'column',
    ],
    'UNIQUE'
);

// Define new primary index
$index_primary = new \Phalcon\Db\Index(
    'PRIMARY',
    [
        'column',
    ]
);

// Add index to existing table
$connection->addIndex("robots", null, $index_unique);
$connection->addIndex("robots", null, $index_primary);

Properties¶

/**
 * Index columns
 *
 * @var array
 */
protected $columns;

/**
 * Index name
 *
 * @var string
 */
protected $name;

/**
 * Index type
 *
 * @var string
 */
protected $type;

Methods¶

public function __construct( string $name, array $columns, string $type = string );

Phalcon\Db\Index constructor

public function getColumns(): array;

Index columns

public function getName(): string;

Index name

public function getType(): string;

Index type

Db\IndexInterface ¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

Interface for Phalcon\Db\Index

Methods¶

public function getColumns(): array;

Gets the columns that corresponds the index

public function getName(): string;

Gets the index name

public function getType(): string;

Gets the index type

Db\Profiler¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
- Phalcon\Db\Profiler\Item
Extends
Implements

Instances of Phalcon\Db can generate execution profiles on SQL statements sent to the relational database. Profiled information includes execution time in milliseconds. This helps you to identify bottlenecks in your applications.

use Phalcon\Db\Profiler;
use Phalcon\Events\Event;
use Phalcon\Events\Manager;

$profiler = new Profiler();
$eventsManager = new Manager();

$eventsManager->attach(
    "db",
    function (Event $event, $connection) use ($profiler) {
        if ($event->getType() === "beforeQuery") {
            $sql = $connection->getSQLStatement();

            // Start a profile with the active connection
            $profiler->startProfile($sql);
        }

        if ($event->getType() === "afterQuery") {
            // Stop the active profile
            $profiler->stopProfile();
        }
    }
);

// Set the event manager on the connection
$connection->setEventsManager($eventsManager);


$sql = "SELECT buyer_name, quantity, product_name
FROM buyers LEFT JOIN products ON
buyers.pid=products.id";

// Execute a SQL statement
$connection->query($sql);

// Get the last profile in the profiler
$profile = $profiler->getLastProfile();

echo "SQL Statement: ", $profile->getSQLStatement(), "\n";
echo "Start Time: ", $profile->getInitialTime(), "\n";
echo "Final Time: ", $profile->getFinalTime(), "\n";
echo "Total Elapsed Time: ", $profile->getTotalElapsedSeconds(), "\n";

Properties¶

/**
 * Active Item
 *
 * @var Item
 */
protected $activeProfile;

/**
 * All the Items in the active profile
 *
 * @var Item[]
 */
protected $allProfiles;

/**
 * Total time spent by all profiles to complete
 *
 * @var float
 */
protected $totalSeconds = 0;

Methods¶

public function getLastProfile(): Item;

Returns the last profile executed in the profiler

public function getNumberTotalStatements(): int;

Returns the total number of SQL statements processed

public function getProfiles(): Item[];

Returns all the processed profiles

public function getTotalElapsedSeconds(): double;

Returns the total time in seconds spent by the profiles

public function reset(): Profiler;

Resets the profiler, cleaning up all the profiles

public function startProfile( string $sqlStatement, mixed $sqlVariables = null, mixed $sqlBindTypes = null ): Profiler;

Starts the profile of a SQL sentence

public function stopProfile(): Profiler;

Stops the active profile

Db\Profiler\Item¶

Source on GitHub

Namespace
- Phalcon\Db\Profiler
Uses
Extends
Implements

This class identifies each profile in a Phalcon\Db\Profiler

Properties¶

/**
 * Timestamp when the profile ended
 *
 * @var double
 */
protected $finalTime;

/**
 * Timestamp when the profile started
 *
 * @var double
 */
protected $initialTime;

/**
 * SQL bind types related to the profile
 *
 * @var array
 */
protected $sqlBindTypes;

/**
 * SQL statement related to the profile
 *
 * @var string
 */
protected $sqlStatement;

/**
 * SQL variables related to the profile
 *
 * @var array
 */
protected $sqlVariables;

Methods¶

public function getFinalTime(): double;

Return the timestamp when the profile ended

public function getInitialTime(): double;

Return the timestamp when the profile started

public function getSqlBindTypes(): array;

Return the SQL bind types related to the profile

public function getSqlStatement(): string;

Return the SQL statement related to the profile

public function getSqlVariables(): array;

Return the SQL variables related to the profile

public function getTotalElapsedSeconds(): double;

Returns the total time in seconds spent by the profile

public function setFinalTime( double $finalTime )

Return the timestamp when the profile ended

public function setInitialTime( double $initialTime )

Return the timestamp when the profile started

public function setSqlBindTypes( array $sqlBindTypes )

Return the SQL bind types related to the profile

public function setSqlStatement( string $sqlStatement )

Return the SQL statement related to the profile

public function setSqlVariables( array $sqlVariables )

Return the SQL variables related to the profile

Db\RawValue¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

This class allows to insert/update raw data without quoting or formatting.

The next example shows how to use the MySQL now() function as a field value.

$subscriber = new Subscribers();

$subscriber->email     = "[email protected]";
$subscriber->createdAt = new \Phalcon\Db\RawValue("now()");

$subscriber->save();

Properties¶

/**
 * Raw value without quoting or formatting
 *
 * @var string
 */
protected $value;

Methods¶

public function __construct( mixed $value );

Phalcon\Db\RawValue constructor

public function __toString(): string;

public function getValue(): string;

Db\Reference¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements
- ReferenceInterface

Allows to define reference constraints on tables

$reference = new \Phalcon\Db\Reference(
    "field_fk",
    [
        "referencedSchema"  => "invoicing",
        "referencedTable"   => "products",
        "columns"           => [
            "producttype",
            "product_code",
        ],
        "referencedColumns" => [
            "type",
            "code",
        ],
    ]
);

Properties¶

/**
 * Local reference columns
 *
 * @var array
 */
protected $columns;

/**
 * Constraint name
 *
 * @var string
 */
protected $name;

/**
 * Referenced Columns
 *
 * @var array
 */
protected $referencedColumns;

/**
 * Referenced Schema
 *
 * @var string
 */
protected $referencedSchema;

/**
 * Referenced Table
 *
 * @var string
 */
protected $referencedTable;

/**
 * Schema name
 *
 * @var string
 */
protected $schemaName;

/**
 * ON DELETE
 *
 * @var string
 */
protected $onDelete;

/**
 * ON UPDATE
 *
 * @var string
 */
protected $onUpdate;

Methods¶

public function __construct( string $name, array $definition );

Phalcon\Db\Reference constructor

public function getColumns(): array;

Local reference columns

public function getName(): string;

Constraint name

public function getOnDelete(): string;

ON DELETE

public function getOnUpdate(): string;

ON UPDATE

public function getReferencedColumns(): array;

Referenced Columns

public function getReferencedSchema(): string;

Referenced Schema

public function getReferencedTable(): string;

Referenced Table

public function getSchemaName(): string;

Schema name

Db\ReferenceInterface ¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

Interface for Phalcon\Db\Reference

Methods¶

public function getColumns(): array;

Gets local columns which reference is based

public function getName(): string;

Gets the index name

public function getOnDelete(): string;

Gets the referenced on delete

public function getOnUpdate(): string;

Gets the referenced on update

public function getReferencedColumns(): array;

Gets referenced columns

public function getReferencedSchema(): string;

Gets the schema where referenced table is

public function getReferencedTable(): string;

Gets the referenced table

public function getSchemaName(): string;

Gets the schema where referenced table is

Db\Result\PdoResult¶

Source on GitHub

Namespace
- Phalcon\Db\Result
Uses
- Phalcon\Db\Adapter\AdapterInterface
- Phalcon\Db\Enum
- Phalcon\Db\ResultInterface
Extends
Implements
- ResultInterface

Encapsulates the resultset internals

$result = $connection->query("SELECTFROM robots ORDER BY name");

$result->setFetchMode(
    \Phalcon\Db\Enum::FETCH_NUM
);

while ($robot = $result->fetchArray()) {
    print_r($robot);
}

Properties¶

//
protected $bindParams;

//
protected $bindTypes;

//
protected $connection;

/**
 * Active fetch mode
 */
protected $fetchMode;

/**
 * Internal resultset
 *
 * @var \PDOStatement
 */
protected $pdoStatement;

//
protected $result;

//
protected $rowCount = false;

//
protected $sqlStatement;

Methods¶

public function __construct( AdapterInterface $connection, \PDOStatement $result, mixed $sqlStatement = null, mixed $bindParams = null, mixed $bindTypes = null );

Phalcon\Db\Result\Pdo constructor

public function dataSeek( long $number ): void;

Moves internal resultset cursor to another position letting us to fetch a certain row

$result = $connection->query(
    "SELECTFROM robots ORDER BY name"
);

// Move to third row on result
$result->dataSeek(2);

// Fetch third row
$row = $result->fetch();

public function execute(): bool;

Allows to execute the statement again. Some database systems don't support scrollable cursors. So, as cursors are forward only, we need to execute the cursor again to fetch rows from the beginning

public function fetch( mixed $fetchStyle = null, mixed $cursorOrientation = null, mixed $cursorOffset = null );

Fetches an array/object of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()

$result = $connection->query("SELECTFROM robots ORDER BY name");

$result->setFetchMode(
    \Phalcon\Enum::FETCH_OBJ
);

while ($robot = $result->fetch()) {
    echo $robot->name;
}

public function fetchAll( mixed $fetchStyle = null, mixed $fetchArgument = null, mixed $ctorArgs = null ): array;

Returns an array of arrays containing all the records in the result This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()

$result = $connection->query(
    "SELECTFROM robots ORDER BY name"
);

$robots = $result->fetchAll();

public function fetchArray();

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()

$result = $connection->query("SELECTFROM robots ORDER BY name");

$result->setFetchMode(
    \Phalcon\Enum::FETCH_NUM
);

while ($robot = result->fetchArray()) {
    print_r($robot);
}

public function getInternalResult(): \PDOStatement;

Gets the internal PDO result object

public function numRows(): int;

Gets number of rows returned by a resultset

$result = $connection->query(
    "SELECTFROM robots ORDER BY name"
);

echo "There are ", $result->numRows(), " rows in the resultset";

public function setFetchMode( int $fetchMode, mixed $colNoOrClassNameOrObject = null, mixed $ctorargs = null ): bool;

Changes the fetching mode affecting Phalcon\Db\Result\Pdo::fetch()

// Return array with integer indexes
$result->setFetchMode(
    \Phalcon\Enum::FETCH_NUM
);

// Return associative array without integer indexes
$result->setFetchMode(
    \Phalcon\Enum::FETCH_ASSOC
);

// Return associative array together with integer indexes
$result->setFetchMode(
    \Phalcon\Enum::FETCH_BOTH
);

// Return an object
$result->setFetchMode(
    \Phalcon\Enum::FETCH_OBJ
);

Db\ResultInterface ¶

Source on GitHub

Namespace
- Phalcon\Db
Uses
Extends
Implements

Interface for Phalcon\Db\Result objects

Methods¶

public function dataSeek( long $number );

Moves internal resultset cursor to another position letting us to fetch a certain row

public function execute(): bool;

Allows to execute the statement again. Some database systems don't support scrollable cursors. So, as cursors are forward only, we need to execute the cursor again to fetch rows from the beginning

public function fetch(): mixed;

Fetches an array/object of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()

public function fetchAll(): array;

Returns an array of arrays containing all the records in the result. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()

public function fetchArray(): mixed;

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. This method is affected by the active fetch flag set using Phalcon\Db\Result\Pdo::setFetchMode()

public function getInternalResult(): \PDOStatement;

Gets the internal PDO result object

public function numRows(): int;

Gets number of rows returned by a resultset

public function setFetchMode( int $fetchMode ): bool;

Changes the fetching mode affecting Phalcon\Db\Result\Pdo::fetch()