Chose Language:
comments
Author: Admin/Publisher |finished | checked

PHP – PDO class

In this post, we will theoretically explore what the PDO class of the PDO extension in PHP does and its methods. We will cover the following topics:

  1. The functioning of the PDO class
  2. The methods of the PDO class
  3. How to use the PDO class to connect to a database
  4. How to use the PDO class to perform queries on a database

The PDO class represents a connection between PHP and a database server.

Sinopsis / Synopsis
class PDO {
/* Constants */
public const int PARAM_NULL;
public const int PARAM_BOOL = 5;
public const int PARAM_INT = 1;
public const int PARAM_STR = 2;
public const int PARAM_LOB = 3;
public const int PARAM_STMT = 4;
public const int PARAM_INPUT_OUTPUT;
public const int PARAM_STR_NATL;
public const int PARAM_STR_CHAR;
public const int PARAM_EVT_ALLOC;
public const int PARAM_EVT_FREE;
public const int PARAM_EVT_EXEC_PRE;
public const int PARAM_EVT_EXEC_POST;
public const int PARAM_EVT_FETCH_PRE;
public const int PARAM_EVT_FETCH_POST;
public const int PARAM_EVT_NORMALIZE;
public const int FETCH_DEFAULT;
public const int FETCH_LAZY;
public const int FETCH_ASSOC;
public const int FETCH_NUM;
public const int FETCH_BOTH;
public const int FETCH_OBJ;
public const int FETCH_BOUND;
public const int FETCH_COLUMN;
public const int FETCH_CLASS;
public const int FETCH_INTO;
public const int FETCH_FUNC;
public const int FETCH_GROUP;
public const int FETCH_UNIQUE;
public const int FETCH_KEY_PAIR;
public const int FETCH_CLASSTYPE;
public const int FETCH_SERIALIZE;
public const int FETCH_PROPS_LATE;
public const int FETCH_NAMED;
public const int ATTR_AUTOCOMMIT;
public const int ATTR_PREFETCH;
public const int ATTR_TIMEOUT;
public const int ATTR_ERRMODE;
public const int ATTR_SERVER_VERSION;
public const int ATTR_CLIENT_VERSION;
public const int ATTR_SERVER_INFO;
public const int ATTR_CONNECTION_STATUS;
public const int ATTR_CASE;
public const int ATTR_CURSOR_NAME;
public const int ATTR_CURSOR;
public const int ATTR_ORACLE_NULLS;
public const int ATTR_PERSISTENT;
public const int ATTR_STATEMENT_CLASS;
public const int ATTR_FETCH_TABLE_NAMES;
public const int ATTR_FETCH_CATALOG_NAMES;
public const int ATTR_DRIVER_NAME;
public const int ATTR_STRINGIFY_FETCHES;
public const int ATTR_MAX_COLUMN_LEN;
public const int ATTR_EMULATE_PREPARES;
public const int ATTR_DEFAULT_FETCH_MODE;
public const int ATTR_DEFAULT_STR_PARAM;
public const int ERRMODE_SILENT;
public const int ERRMODE_WARNING;
public const int ERRMODE_EXCEPTION;
public const int CASE_NATURAL;
public const int CASE_LOWER;
public const int CASE_UPPER;
public const int NULL_NATURAL;
public const int NULL_EMPTY_STRING;
public const int NULL_TO_STRING;
public const string ERR_NONE;
public const int FETCH_ORI_NEXT;
public const int FETCH_ORI_PRIOR;
public const int FETCH_ORI_FIRST;
public const int FETCH_ORI_LAST;
public const int FETCH_ORI_ABS;
public const int FETCH_ORI_REL;
public const int CURSOR_FWDONLY;
public const int CURSOR_SCROLL;
/* Methods */
public __construct(
    string $dsn,
    ?string $username = null,
    ?string $password = null,
    ?array $options = null
)
public beginTransaction(): bool
public commit(): bool
public errorCode(): ?string
public errorInfo(): array
public exec(string $statement): int|false
public getAttribute(int $attribute): mixed
public static getAvailableDrivers(): array
public inTransaction(): bool
public lastInsertId(?string $name = null): string|false
public prepare(string $query, array $options = []): PDOStatement|false
public query(string $query, ?int $fetchMode = null): PDOStatement|false
public query(string $query, ?int $fetchMode = PDO::FETCH_COLUMN, int $colno): PDOStatement|false
public query(
    string $query,
    ?int $fetchMode = PDO::FETCH_CLASS,
    string $classname,
    array $constructorArgs
): PDOStatement|false
public query(string $query, ?int $fetchMode = PDO::FETCH_INTO, object $object): PDOStatement|false
public quote(string $string, int $type = PDO::PARAM_STR): string|false
public rollBack(): bool
public setAttribute(int $attribute, mixed $value): bool
}

__construct

Description / Descripción
public PDO::__construct(
    string $dsn,
    string $username = ?,
    string $password = ?,
    array $options = ?
)
Sintaxis / Sintax
$pdo_c =  new PDO($dsn,$username,$password,$options);

Parameters

$dsn – This variable contains the necessary information to connect to the database in the form of a string, usually in the following format: driver:dbname=database_name;host=host_ip. Most of the time, we use $dsn in this way, although there are other ways to utilize it. There are three ways to use $dsn:

  • Invocation of the complete DSN
  • Invocation of the URI
  • Using an alias

Invocation of the complete DSN: The complete invocation is the one we expressed before, such as driver:dbname=database_name;host=host_ip, for example.

$dsn = 'mysql:dbname=wpthemes;host=127.0.0.1';

Assuming we have a database for a WordPress site named “wpthemes,” and we use MySQL as the DBMS, and our database is hosted on our localhost.

Invocation of URI: In this method, the DSN is formed using a URI that defines the location of a file. This file can be on a local path or a remote URL.

As an example $dsn, the PHP official page provides this string: uri:file:///path/to/dsn_file. But how would we define a remote path? It would be done as follows:

$dsn = 'mysql://my_username:my_password@remote_host:remote_port/my_database';

Using an alias: In this way, our $dsn will contain a string that is the name with which we have configured our DSN inside the [file]php.ini[/file] file, for example:

dsn alias
$dns = "midsn"
php.ini
pdo.dsn.midsn = "mysql:host=localhost;dbname=nombre_de_la_base_de_datos"

$username – the username that will be used for the database.

$password – the password of the user who will be using the database.

$options – (optional) is used to specify connection options. These options can be those of setAttribute, as well as specific options for any of the drivers.

$ruta_ca = "/ruta/a/tu/archivo/ca.pem";

$options = [
    PDO::MYSQL_ATTR_SSL_CA => $ruta_ca,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // Otras opciones específicas de MySQL pueden ir aquí
];

Return

Object – will return an object of the PDO class.

errorCode

Gets the SQLSTATE associated with the last operation (query performed) that has been handled by the database handle. In essence, it is the code returned by the DBMS (Database Management System) being used. These codes serve the following purposes:

  • “S” denotes “Success” (class 00),
  • “W” denotes “Warning” (class 01),
  • “N” denotes “No data” (class 02)
  • “X” denotes “Exception” (all other classes).

This means that all those starting with 00 will be success, 01 – warning, 02 – no data; any numbers that do not start like the ones mentioned above will be exceptions.

Description / Descripción
public PDO::errorCode(): mixed

Return

string – the corresponding SQLSTATE.

null – if no operation has been run on the database handle.

errorInfo

Gets extended information about the error associated with the last operation of the database handle (DBMS); this information will be returned in an array.

Description / Descripción
public PDO::errorInfo(): array

Return

array – returns a numeric array of 3 elements in which its elements will have the following data.

  1. SQLSTATE Error Code
  2. Driver-specific error code.
  3. Driver-specific error message

exec

Executes a SQL statement and returns the number of affected rows

Description / Descripción
public PDO::exec(string $statement): int

Parameters

$statement – statement to be executed.

Return

int – number of columns that have been affected

I recommend using prepare instead of executing an exec.

In exec, you will need to properly escape the data, which can be done with the quote method. This not to be done with prepare.

Also, with this method, we need to consider that it may return 0, which in some cases is treated as false. Therefore, we should use the operator ===.

getAttribute

Gets a database connection attribute.

Description / Descripción
public PDO::getAttribute(int $attribute): mixed

Parameters

In the $attribute parameter, any of the following constants can be used:

  • PDO::ATTR_AUTOCOMMIT
  • PDO::ATTR_CASE
  • PDO::ATTR_CLIENT_VERSION
  • PDO::ATTR_CONNECTION_STATUS
  • PDO::ATTR_DRIVER_NAME
  • PDO::ATTR_ERRMODE
  • PDO::ATTR_ORACLE_NULLS
  • PDO::ATTR_PERSISTENT
  • PDO::ATTR_PREFETCH
  • PDO::ATTR_SERVER_INFO
  • PDO::ATTR_SERVER_VERSION
  • PDO::ATTR_TIMEOUT

Returns

mixed – In case of success, it will return the value of the attribute.

null – In case of failure

getAvailableDrivers

Returns an array with the drivers that we can use

Description / Descripción
public static PDO::getAvailableDrivers(): array

inTransaction

The inTransaction() method is used to check if a transaction is currently active. This can be useful in the following cases:

  • To check if it is necessary to initiate a transaction before performing an operation that requires a transaction.
  • To check if a transaction has been successfully completed before proceeding with the next unit of work.
Description / Descripción
public PDO::inTransaction(): bool

Databases that support transactions and are most commonly used with PDO are

  • MySQL
  • PostgreSQL
  • Oracle
  • Microsoft SQL Server
  • IBM DB2
  • SQLite
  • Firebird

Returns

true – if a transaction is currently active

false – in case it does not have an active transaction

lastInsertId

Returns the ID of the last inserted row or sequence. The table on which the query was performed must have an AUTO_INCREMENT column.

Description / Descripción
public PDO::lastInsertId(string $name = null): string

Parameters

$name -It is the name of the column that should be returned; generally, you won’t need this parameter, but in some drivers, such as PostgreSQL, you may need it.

Returns

string

  • (In the case of an undefined $name), the ID of the row.
  • (In the case where $name is defined), the last value of the specified column.
  • IM001 – SQLSTATE, in the event that PDO does not support this feature

prepare

Prepares a statement for execution and returns a PDOStatement object. Please note that this statement will not be executed; instead, we are creating the object from which it will be executed using the execute() method

Description / Descripción
public PDO::prepare(string $statement, array $driver_options = array()): PDOStatement
The prepare method is not intended to be used in conjution with quote, use placeholder instead ? or :name inside the statement

Parameters

$statement – an SQL statement or query.

$driver_options – this parameter is optional; in some cases, it is better to provide an array with options for the driver. For example, PDO::ATTR_CURSOR; you can see more at https://www.php.net/manual/en/pdo.constants.php.

Returns

PDOStatement – in case the method is executed successfully.

false – if there has been a failure; it can also return a PDOException.

query

Executes an SQL statement, returning a result set as a PDOStatement object.

Description / Descripción

public PDO::query(
  string $query,
  ?int $fetchMode = null
): PDOStatement|false

public PDO::query(
  string $query,
  ?int $fetchMode = PDO::FETCH_COLUMN,
  int $colno
): PDOStatement|false

public PDO::query(
    string $query,
    ?int $fetchMode = PDO::FETCH_CLASS,
    string $classname,
    array $constructorArgs
): PDOStatement|false
public PDO::query(string $query, ?int $fetchMode = PDO::FETCH_INTO, object $object): PDOStatement|false

If our query involves variables (placeholders), it is recommended to use the prepare method instead of query.

In other words, query is useful for creating SELECT queries in our database. Some people also use this method for SELECT even with placeholders, but you should be aware of using quote in conjunction, or you could end up with a SQL injection attack equally quote is not complete secure vs SQLInjections.

Parameters

$query – (string) the statement that will be executed.

$fetchMode – it will use the default one you chose before. In case you need to define the fetchMode, you have the fetch modes available at https://www.php.net/manual/en/pdo.constants.php.

Returns

PDOStatement – in case of success.

false – in case of failure.

quote

Quotes a string for use in a SQL query or statement.

Description / Descripción
 
public PDO::quote(string $string, int $type = PDO::PARAM_STR): string|false

Parameters

$string – the string that will be enclosed in quotes

$type – The $type parameter of the quote() method of PDO in PHP specifies the data type of the input string. This affects the way that special characters are escaped in the string.

The possible values for the $type parameter are:

  • PDO::PARAM_STR: The string is escaped as a string. This is the default value.
  • PDO::PARAM_INT: The string is converted to an integer.
  • PDO::PARAM_FLOAT: The string is converted into a floating-point number.
  • PDO::PARAM_BOOL: The string is converted to a boolean value.
  • PDO::PARAM_NULL: The string is set to NULL
  • PDO::PARAM_LOB: The string is escaped as a BLOB or CLOB object.

Returns

string – Returns a quoted string that is safe to pass to an SQL statement.

false – in case the driver does not support quoting in this manner.

setAttribute

Sets an attribute on the database handle. Some of the generic attributes available are listed below; some drivers may make use of additional specific attributes

Description / Descripción
 
public PDO::setAttribute(int $attribute, mixed $value): bool

Parameters


PDO::ATTR_CASE: Forces column names to a specific capitalization.

Possible values are:

  • PDO::CASE_LOWER: Forces column names to lowercase
  • PDO::CASE_NATURAL: Leave the column names as they are returned by the database driver.
  • PDO::CASE_UPPER: Force the column names to uppercase.

PDO::ATTR_ERRMODE: Error report.

  • PDO::ERRMODE_SILENT: When set, it instructs PDO to suppress error messages and warnings by default.
  • PDO::ERRMODE_WARNING: emit a warning (E_WARNING) message alongside setting the error code and information.
  • PDO::ERRMODE_EXCEPTION: when enabled, PDO will throw a PDOException object whenever a database error occurs.

PDO::ATTR_ORACLE_NULLS (disponible para todos los drivers, no sólo Oracle): PDO converts empty strings to null values when fetching them from the database.

Posible values: true, false

  • PDO::NULL_NATURAL: no convertions.
  • PDO::NULL_EMPTY_STRING: empty strings are converted to null.
  • PDO::NULL_TO_STRING: all NULL are converted to empty strings.

PDO::ATTR_STRINGIFY_FETCHES: Converts numerical values to strings when fetching. Possible values are: true or false.


PDO::ATTR_STATEMENT_CLASS: Sets the user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requires array(string classname, array(mixed constructor_args))


PDO::ATTR_TIMEOUT: Specifies the timeout duration in seconds.

Not all drivers support this option, and its meaning may differ between drivers


PDO::ATTR_AUTOCOMMIT (available in OCI, Firebird, and MySQL): This is used to enable autocommit on each statement. Possible values are true or false.


PDO::ATTR_EMULATE_PREPARES Enables or disables the use of prepared statement emulation.

true – enables prepared statement emulation.

false – disables emulation, attempting to use native prepared statements.

Will always fall back to emulation if the driver cannot support it

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (available in MySQL): Use buffered queries.

Buffered Query: It means that all results are retrieved from the database and stored in memory before your script begins to process them. This allows easier access and multiple passes through the results but may consume more memory, especially if result sets are large.

Unbuffered Query: It means that results are read as your script progresses, which can be more memory-efficient but may have limitations in terms of data manipulation and multiple passes through the results.

Possible values: true, false.


PDO::ATTR_DEFAULT_FETCH_MODE: Set default fetch mode.

You can see a list of possible values here https://www.php.net/manual/es/pdostatement.fetch.php

  • PDO::FETCH_ASSOC – Returns an associative array with column names as keys. This is the default value.
  • PDO::FETCH_NUM – Returns a numeric array with columns numbered.
  • PDO::FETCH_BOTH – Returns an associative and numeric array with columns.
  • PDO::FETCH_OBJ – Returns an object with columns as properties
  • PDO::FETCH_LAZY – Returns an object with columns as properties, but only loads values when accessed.

Returns

true in case of success.

false in case of error.


Transactions

I make a separation here, as I believe that beginTransaction, commit, and rollback are part of the same action.

Keep in mind that performing a transaction makes sense when we have multiple statements that depend on each other. It wouldn’t make much sense to have a transaction for a single statement


beginTransaction

Starts a transaction and deactivates autocommit mode.

Description / Descripción
public PDO::beginTransaction(): bool
Sintaxis / Sintax
//tenga en cuenta que no estamos chequeando si se conectio
$pdo_connection = new PDO($dsn, $usuario, $contraseña);
$pdo_connection->beginTransaction();

Returns

true in case of success.

false in case of error.

commit

Performs the transaction.

Description / Descripción
public PDO::commit(): bool
Sintaxis / Sintax
$pdo_connection->beginTransaction();

Returns

true in case of success.

false in case of error.

rollBack

Rolls back a transaction

Description / Descripción
public PDO::rollBack(): bool
Sintaxis / Sintax
$pdo_connection->rollBack();

Returns

true in case of success.

false in case of error.


Let’s see an example of transactions

Ejemplo de una transaccion en php
// Conectamos a la base de datos
$pdo = new PDO("mysql:host=localhost;dbname=mi_base_de_datos", "manolo", "my_password");

// Creamos la transacción
$pdo->beginTransaction();

// Preparamos las consultas
$consulta_actualizar = $pdo->prepare(
    "UPDATE productos
    SET precio = :precio
    WHERE id = :id"
);
$consulta_eliminar = $pdo->prepare(
    "DELETE FROM productos
    WHERE id = :id"
);

// Ejecutamos las consultas
try {
    $consulta_actualizar->execute(["precio" => 100, "id" => 1]);
    $consulta_eliminar->execute(["id" => 2]);
} catch (PDOException $e) {
    // La transacción se ha fallado, por lo que la revertimos
    $pdo->rollBack();
    // Imprimimos el error
    echo $e->getMessage();
}

// Commit de la transacción
$pdo->commit();

In PDO transactions, we don’t have a direct way to create a savepoint or roll back to it. However, we could use exec for this. Remember that with exec, we can execute SQL code so:

$pdo->exec('SAVEPOINT my_savepoint');

y el rollback a ese savepoint deberian funcionar sin problemas

$pdo->exec('ROLLBACK TO my_savepoint');

example:

try {
    $pdo->beginTransaction();

    // Tu código SQL preparado aquí
    $stmt = $pdo->prepare("INSERT INTO tu_tabla (columna1, columna2) VALUES (?, ?)");

    $valor1 = 'valor1';
    $valor2 = 'valor2';

    $stmt->bindParam(1, $valor1);
    $stmt->bindParam(2, $valor2);

    $stmt->execute();

    // Usar exec para crear un savepoint
    $pdo->exec('SAVEPOINT my_savepoint');

    // Otro código SQL preparado aquí
    $stmt = $pdo->prepare("UPDATE tu_tabla SET columna1 = ? WHERE columna2 = ?");

    $nuevoValor1 = 'nuevo_valor1';
    $condicionColumna2 = 'condicion';

    $stmt->bindParam(1, $nuevoValor1);
    $stmt->bindParam(2, $condicionColumna2);

    $stmt->execute();

    // Si todo está bien, confirmar la transacción
    $pdo->commit();
} catch (Exception $e) {
    // Si hay un error, puedes hacer un rollback hasta el savepoint
    $pdo->exec('ROLLBACK TO my_savepoint');
    // Manejar el error de alguna manera
}
Category: en-php
Something wrong? If you found an error or mistake in the content you can contact me on Twitter | @luisg2249_luis.
Last 4 post in same category

Comments