Documentation

Menu

## Db
in package

Db.php : 45

Db class - PDO Database abstraction layer class

The Db class is a database abstraction layer that provides a simple, consistent interface for interacting with different types of databases. It handles connection management, query execution, pagination and result processing, allowing developers to focus on the business logic of their application.

Full documentation with code examples is available here: [https://www.powerlitepdo.com/]

The Db class is designed to be flexible and extensible, allowing developers to easily customize it to meet their specific needs. It supports multiple database types, including MySQL, PostgreSQL, Firebird, and Oracle, and can be easily extended to support additional databases.

The Db class is designed to be easy to use and understand. It provides a set of simple, intuitive methods for executing queries and retrieving data, and it automatically handles error handling and debugging. This makes it easy for developers to quickly get up and running with the class, without having to worry about low-level details such as database connections and query execution.

In addition, the Db class is designed to be highly efficient and fast. It uses the latest database features and optimization techniques to ensure that queries are executed quickly and efficiently, without sacrificing performance. This means that applications built using the Db class can scale easily and perform well under load, even with large amounts of data.

##### Tags

author

  Gilles Migliori

version
  1.0.0
license

  GNU General Public License v3.0

link
  https://github.com/gilles-migliori/php-pdo-db-class
link
  https://packagist.org/packages/gilles-migliori/php-pdo-db-class
link
  https://www.phpformbuilder.pro/documentation/db-help.php

### Table of Contents

#### Properties

$connection : DriverBase
$pdo : PDO
$queryBuilder : QueryBuilder

#### Methods

__construct() : mixed
  Constructor
convertToSimpleArray() : array<string|int, mixed>
  Converts a Query() or Select() array of records into a simple array using only one column or an associative array using another column as a key.
debugOnce() : self
  Sets the queryBuilder's debugOnceMode.
delete() : bool|int
  Deletes a record from the database table.
fetch() : mixed
  Fetches the next row from a result set and returns it according to the $fetchParameters format
fetchAll() : mixed
  Fetches all rows from a result set and return them according to the $fetchParameters format
getColumns() : mixed
  Get the information about the columns in a given table
getColumnsNames() : mixed
  Returns the columns names of the target table in a table
getDebug() : View
  Returns the debug information as a string.
getDebugMode() : bool|string
  Get the debug mode of the query builder.
getHTML() : string
  This function returns records from a SQL query as an HTML table.
getLastInsertId() : bool|string
  Get the last insert ID.
getMaximumValue() : mixed
  Retrieves the maximum value of a specified field from a given table.
getPdo() : PDO
  Returns the PDO object.
getQueryBuilder() : QueryBuilder
  Returns an instance of the QueryBuilder class.
getTables() : mixed
  Selects all the tables into the database
insert() : bool|int
  Inserts a new record into a table using PDO
numRows() : int|false
  Returns the number of rows in the result set of the current query.
query() : bool|int
  Executes a SQL query on the database.
queryRow() : mixed
  Executes a SQL query using PDO and returns one row
queryValue() : mixed
  Executes a SQL query using PDO and returns a single value only
select() : bool|int
  Selects data from the database.
selectCount() : mixed
  Select COUNT records using PDO
selectRow() : mixed
  Selects a single record using PDO
selectValue() : mixed
  Selects a single value using PDO
setDebug() : void
  Sets the queryBuilder's debugGlobalMode.
transactionBegin() : bool
  Begin transaction processing
transactionCommit() : bool
  Commit and end transaction processing.
transactionRollback() : bool
  Roll back transaction processing.
update() : bool|int
  Updates an existing record into a table using PDO

### Properties

#### $connection

Db.php : 47
`protected DriverBase $connection`

#### $pdo

Db.php : 49
`protected PDO $pdo`

#### $queryBuilder

Db.php : 51
`protected QueryBuilder $queryBuilder`

### Methods

#### __construct()

Db.php : 56

Constructor

`public __construct(DriverBase $driverBase, QueryBuilder $queryBuilder) : mixed`

##### Parameters

$driverBase : DriverBase
$queryBuilder : QueryBuilder

#### convertToSimpleArray()

Db.php : 379

Converts a Query() or Select() array of records into a simple array using only one column or an associative array using another column as a key.

`public convertToSimpleArray(mixed $array, string $value_field[, string|null $key_field = null ]) : array<string|int, mixed>`

##### Parameters

$array : mixed

  The array returned from a PDO query using fetchAll.

$value_field : string

  The name of the field that holds the value.

$key_field : string|null = null

  The name of the field that holds the key, making the return value an associative array.

##### Return values

array<string|int, mixed> —

Returns an array with only the specified data.

#### debugOnce()

Db.php : 589

Sets the queryBuilder's debugOnceMode.

`public debugOnce(bool|string $mode) : self`

##### Parameters

$mode : bool|string

  The debug mode to set.

##### Return values

self

#### delete()

Db.php : 362

Deletes a record from the database table.

`public delete(string $table[, array<int|string, mixed>|string $where = [] ][, bool|string $debug = false ]) : bool|int`

##### Parameters

$table : string

  The name of the table from which to delete the record.

$where : array<int|string, mixed>|string = []

  An associative array of conditions to match the record(s) to be deleted. The keys represent the column names and the values represent the matching values.

$debug : bool|string = false

  false, true or 'silent'. false: the delete is executed. true the delete is not executed. The query is displayed. 'silent': the delete is not executed. The query is registered then can be displayed using the getDebug() method.

##### Return values

bool|int —

The number of affected rows or false if there was an error.

#### fetch()

Db.php : 516

Fetches the next row from a result set and returns it according to the $fetchParameters format

`public fetch([int $fetchParameters = PDO::FETCH_OBJ ]) : mixed`

##### Parameters

$fetchParameters : int = PDO::FETCH_OBJ

  The PDO fetch style record options

##### Return values

mixed —

The next row or false if we reached the end

#### fetchAll()

Db.php : 527

Fetches all rows from a result set and return them according to the $fetchParameters format

`public fetchAll([int $fetchParameters = PDO::FETCH_OBJ ]) : mixed`

##### Parameters

$fetchParameters : int = PDO::FETCH_OBJ

  The PDO fetch style record options

##### Return values

mixed —

The rows according to PDO fetch style or false if no record

#### getColumns()

Db.php : 425

Get the information about the columns in a given table

`public getColumns(string $table[, int $fetchParameters = PDO::FETCH_OBJ ][, bool|string $debug = false ]) : mixed`

##### Parameters

$table : string

  The name of the table

$fetchParameters : int = PDO::FETCH_OBJ

  [OPTIONAL] The PDO fetch style record options

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

An associative array that contains the columns data or false if the table doesn't have any column. [ 'Field' => string, The name of the column. 'Type' => string, The column data type. 'Null' => string, The column nullability. The value is YES if NULL values can be stored in the column, NO if not. 'Key' => string, The column key if the column is indexed. 'Default' => mixed, The default value for the column. 'Extra' => string, Any additional information. The value is nonempty in these cases: - auto_increment for columns that have the AUTO_INCREMENT attribute. - on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute. - VIRTUAL GENERATED or STORED GENERATED for generated columns. - DEFAULT_GENERATED for columns that have an expression default value. ]

#### getColumnsNames()

Db.php : 444

Returns the columns names of the target table in a table

`public getColumnsNames(string $table[, bool|string $debug = false ]) : mixed`

##### Parameters

$table : string

  The name of the table

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

An array that contains the columns names or false if the table doesn't have any column.

#### getDebug()

Db.php : 600

Returns the debug information as a string.

`public getDebug() : View`

##### Return values

View —

The debug information.

#### getDebugMode()

Db.php : 620

Get the debug mode of the query builder.

`public getDebugMode() : bool|string`

##### Return values

bool|string —

The debug mode of the query builder.

#### getHTML()

Db.php : 542

This function returns records from a SQL query as an HTML table.

`public getHTML(array<string|int, mixed> $records[, bool $showCount = true ][, string|null $tableAttr = null ][, string|null $th_Attr = null ][, string|null $tdAttr = null ]) : string`

##### Parameters

$records : array<string|int, mixed>

  The records set - can be an array or array of objects according to the fetch parameters.

$showCount : bool = true

  (Optional) true if you want to show the row count, false if you do not want to show the count.

$tableAttr : string|null = null

  (Optional) Comma separated attributes for the table. e.g: 'class=my-class, style=color:#222'.

$th_Attr : string|null = null

  (Optional) Comma separated attributes for the header row. e.g: 'class=my-class, style=font-weight:bold'.

$tdAttr : string|null = null

  (Optional) Comma separated attributes for the cells. e.g: 'class=my-class, style=font-weight:normal'.

##### Return values

string —

HTML containing a table with all records listed.

#### getLastInsertId()

Db.php : 557

Get the last insert ID.

`public getLastInsertId() : bool|string`

##### Return values

bool|string —

The last insert ID or false if there was an error.

#### getMaximumValue()

Db.php : 569

Retrieves the maximum value of a specified field from a given table.

`public getMaximumValue(string $table, string $field) : mixed`

##### Parameters

$table : string

  The name of the table.

$field : string

  The name of the field.

##### Return values

mixed —

The maximum value of the specified field or false if no value is found.

#### getPdo()

Db.php : 70

Returns the PDO object.

`public getPdo() : PDO`

##### Return values

PDO —

The PDO object.

#### getQueryBuilder()

Db.php : 80

Returns an instance of the QueryBuilder class.

`public getQueryBuilder() : QueryBuilder`

##### Return values

QueryBuilder —

An instance of the QueryBuilder class.

#### getTables()

Db.php : 466

Selects all the tables into the database

`public getTables([bool|string $debug = false ]) : mixed`

##### Parameters

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

Array with tables if success otherwise false

#### insert()

Db.php : 311

Inserts a new record into a table using PDO

`public insert(string $table, array<string, mixed> $values[, bool|string $debug = false ]) : bool|int`

##### Parameters

$table : string

  Table name

$values : array<string, mixed>

  Associative array containing the fields and values e.g. ['name' => 'Cathy', 'city' => 'Cardiff']

$debug : bool|string = false

  false, true or 'silent'. false: the insert is executed. true: the insert is not executed. The query is displayed. 'silent': the insert is not executed. The query is registered then can be displayed using the getDebug() method.

##### Return values

bool|int —

The number of affected rows or false if there was an error.

#### numRows()

Db.php : 579

Returns the number of rows in the result set of the current query.

`public numRows() : int|false`

##### Return values

int|false —

The number of rows, or false on failure.

#### query()

Db.php : 93

Executes a SQL query on the database.

`public query(string $sql[, array<string, mixed> $placeholders = [] ][, bool|string $debug = false ]) : bool|int`

##### Parameters

$sql : string

  The SQL query to execute.

$placeholders : array<string, mixed> = []

  An indexed or associative array to store the placeholders used in the query.

$debug : bool|string = false

  false, true or silent.

##### Return values

bool|int —

Returns true or false for a SELECT query, returns the number of affected rows for other statements or false if there was an error.

#### queryRow()

Db.php : 117

Executes a SQL query using PDO and returns one row

`public queryRow(string $sql[, array<string, mixed> $placeholders = [] ][, int $fetchParameters = PDO::FETCH_OBJ ][, bool|string $debug = false ]) : mixed`

##### Parameters

$sql : string

  The SQL query to execute.

$placeholders : array<string, mixed> = []

  An indexed or associative array to store the placeholders used in the query.

$fetchParameters : int = PDO::FETCH_OBJ

  PDO fetch style record options

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

Array or object with values if success otherwise false

#### queryValue()

Db.php : 141

Executes a SQL query using PDO and returns a single value only

`public queryValue(string $sql[, array<string, mixed> $placeholders = [] ][, bool|string $debug = false ]) : mixed`

##### Parameters

$sql : string

  The SQL query to execute.A

$placeholders : array<string, mixed> = []

  An indexed or associative array to store the placeholders used in the query.

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

A returned value from the database if success otherwise false

#### select()

Db.php : 175

Selects data from the database.

`public select(string $from, string|array<string|int, string> $fields[, array<int|string, mixed>|string $where = [] ][, array<string, bool|int|string> $parameters = [] ][, bool|string $debug = false ]) : bool|int`

##### Parameters

$from : string

  The SQL FROM statement with optional joins. Example: 'table1 INNER JOIN table2 ON table1.id = table2.id'.

$fields : string|array<string|int, string>

  The columns to select. Can be a string or an array of strings.

$where : array<int|string, mixed>|string = []

  The WHERE clause. Can be a string or an array of conditions. If it's an array, the conditions will be joined with AND.

$parameters : array<string, bool|int|string> = []

  An associative array of parameter names and values.

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

bool|int —

True if the query was successful, false otherwise.

#### selectCount()

Db.php : 206

Select COUNT records using PDO

`public selectCount(string $from[, string|array<string|int, string> $fields = ['*' => 'rowsCount'] ][, array<int|string, mixed>|string $where = [] ][, array<string, bool|int|string> $parameters = [] ][, bool|string $debug = false ]) : mixed`

##### Parameters

$from : string

  The SQL FROM statement with optional joins. Example: 'table1 INNER JOIN table2 ON table1.id = table2.id'.

$fields : string|array<string|int, string> = ['*' => 'rowsCount']

  The columns to select. Can be a string or an array of strings.

$where : array<int|string, mixed>|string = []

  The WHERE clause. Can be a string or an array of conditions. If it's an array, the conditions will be joined with AND.

$parameters : array<string, bool|int|string> = []

  An associative array of parameter names and values.

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

The record row or false if no record has been found

#### selectRow()

Db.php : 254

Selects a single record using PDO

`public selectRow(string $from[, string|array<string|int, string> $fields = '*' ][, array<int|string, mixed>|string $where = [] ][, int $fetchParameters = PDO::FETCH_OBJ ][, bool|string $debug = false ]) : mixed`

##### Parameters

$from : string

  The SQL FROM statement with optional joins. Example: 'table1 INNER JOIN table2 ON table1.id = table2.id'.

$fields : string|array<string|int, string> = '*'

  The columns to select. Can be a string or an array of strings.

$where : array<int|string, mixed>|string = []

  The WHERE clause. Can be a string or an array of conditions. If it's an array, the conditions will be joined with AND.

$fetchParameters : int = PDO::FETCH_OBJ

  PDO fetch style record options

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

Array or object with values if success otherwise false

#### selectValue()

Db.php : 280

Selects a single value using PDO

`public selectValue(string $from, string|array<string|int, string> $field[, array<int|string, mixed>|string $where = [] ][, bool|string $debug = false ]) : mixed`

##### Parameters

$from : string

  The SQL FROM statement with optional joins. Example: 'table1 INNER JOIN table2 ON table1.id = table2.id'.

$field : string|array<string|int, string>

  The columns to select. Can be a string or an array of strings.

$where : array<int|string, mixed>|string = []

  The WHERE clause. Can be a string or an array of conditions. If it's an array, the conditions will be joined with AND.

$debug : bool|string = false

  false, true or 'silent'. false: the production mode. true: The query is displayed. 'silent': The query is registered then can be displayed using the getDebug() method.

##### Return values

mixed —

A returned value from the database if success otherwise false

#### setDebug()

Db.php : 610

Sets the queryBuilder's debugGlobalMode.

`public setDebug(bool|string $mode) : void`

##### Parameters

$mode : bool|string

  The debug mode to set. Pass `null` to disable debug mode.

#### transactionBegin()

Db.php : 481

Begin transaction processing

`public transactionBegin() : bool`

##### Tags

throws
  Exception

  If there is an error.

##### Return values

bool —

Returns true if the transaction begins successfully, false otherwise.

#### transactionCommit()

Db.php : 493

Commit and end transaction processing.

`public transactionCommit() : bool`

##### Tags

throws
  Exception

  If there is an error.

##### Return values

bool —

Returns true if the transaction is committed successfully, false otherwise.

#### transactionRollback()

Db.php : 505

Roll back transaction processing.

`public transactionRollback() : bool`

##### Tags

throws
  Exception

  If there is an error.

##### Return values

bool —

Returns true if the transaction is rolled back successfully, false otherwise.

#### update()

Db.php : 337

Updates an existing record into a table using PDO

`public update(string $table, array<string, mixed> $values[, array<int|string, mixed>|string $where = [] ][, bool|string $debug = false ]) : bool|int`

##### Parameters

$table : string

  Table name

$values : array<string, mixed>

  Associative array containing the fields and values e.g. ['name' => 'Cathy', 'city' => 'Cardiff']

$where : array<int|string, mixed>|string = []

  The WHERE clause. Can be a string or an array of conditions. If it's an array, the conditions will be joined with AND.

$debug : bool|string = false

  false, true or 'silent'. false: the update is executed. true the update is not executed. The query is displayed. 'silent': the update is not executed. The query is registered then can be displayed using the getDebug() method.

##### Return values

bool|int —

The number of affected rows or false if there was an error.

## Search results