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.

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

public PDO::__construct(
    string $dsn,
    string $username = ?,
    string $password = ?,
    array $options = ?
)

$pdo_c =  new PDO($dsn,$username,$password,$options);

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

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

$dns = "midsn"

pdo.dsn.midsn = "mysql:host=localhost;dbname=nombre_de_la_base_de_datos"

$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í
];

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.

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.

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

public PDO::exec(string $statement): int

getAttribute

Gets a database connection attribute.

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

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.

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.

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

public PDO::prepare(string $statement, array $driver_options = array()): PDOStatement

query

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

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

quote

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

 
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

 
public PDO::setAttribute(int $attribute, mixed $value): bool

Parameters

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

Possible values are:

PDO::ATTR_ERRMODE: Error report.

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::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.

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.

public PDO::beginTransaction(): bool

//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.

public PDO::commit(): bool

$pdo_connection->beginTransaction();

Returns

true in case of success.

false in case of error.

rollBack

Rolls back a transaction

public PDO::rollBack(): bool

$pdo_connection->rollBack();

Returns

true in case of success.

false in case of error.

Let’s see an example of transactions

// 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
}