PHP PDOStatement class

The PHP PDOStatement class represents a prepared statement, which, after the statement is executed with execute, will represent a result set.

The ways to obtain a PDOStatement object is to use one of the following methods of the PDO class: query or prepare

 class PDOStatement implements IteratorAggregate {
/* Properties */
public string $queryString;
/* Methods */
public bindColumn(
    string|int $column,
    mixed &$var,
    int $type = PDO::PARAM_STR,
    int $maxLength = 0,
    mixed $driverOptions = null
): bool
public bindParam(
    string|int $param,
    mixed &$var,
    int $type = PDO::PARAM_STR,
    int $maxLength = 0,
    mixed $driverOptions = null
): bool
public bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool
public closeCursor(): bool
public columnCount(): int
public debugDumpParams(): ?bool
public errorCode(): ?string
public errorInfo(): array
public execute(?array $params = null): bool
public fetch(int $mode = PDO::FETCH_DEFAULT, int $cursorOrientation = PDO::FETCH_ORI_NEXT, int $cursorOffset = 0): mixed
public fetchAll(int $mode = PDO::FETCH_DEFAULT): array
public fetchAll(int $mode = PDO::FETCH_COLUMN, int $column): array
public fetchAll(int $mode = PDO::FETCH_CLASS, string $class, ?array $constructorArgs): array
public fetchAll(int $mode = PDO::FETCH_FUNC, callable $callback): array
public fetchColumn(int $column = 0): mixed
public fetchObject(?string $class = "stdClass", array $constructorArgs = []): object|false
public getAttribute(int $name): mixed
public getColumnMeta(int $column): array|false
public getIterator(): Iterator
public nextRowset(): bool
public rowCount(): int
public setAttribute(int $attribute, mixed $value): bool
public setFetchMode(int $mode): bool
public setFetchMode(int $mode = PDO::FETCH_COLUMN, int $colno): bool
public setFetchMode(int $mode = PDO::FETCH_CLASS, string $class, ?array $constructorArgs = null): bool
public setFetchMode(int $mode = PDO::FETCH_INTO, object $object): bool
}

Propiedades

$queryString – It is the string that will be used.

Methods

The PDOStatement class provides methods that we can execute to manipulate prepared statements, which are an efficient way to execute frequently repeated SQL queries.

bindColumn

It binds (associates or links) a column from the obtained result set with a variable

 public PDOStatement::bindColumn(
    string|int $column,
    mixed &$var,
    int $type = PDO::PARAM_STR,
    int $maxLength = 0,
    mixed $driverOptions = null
): bool

bindParam

Binds a parameter to a specific variable. When we refer to a parameter, we are talking about a parameter in an SQL query.

public PDOStatement::bindParam(
    string|int $param,
    mixed &$var,
    int $type = PDO::PARAM_STR,
    int $maxLength = 0,
    mixed $driverOptions = null
): bool

$consulta = $conexion->prepare("SELECT nombre, apellido, edad FROM usuarios WHERE nombre = :name");

$consulta = $conexion->prepare("SELECT nombre, apellido, edad FROM usuarios WHERE nombre = ?");

bindValue

Associates a value with a parameter

public PDOStatement::bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool

closeCursor

Closes the cursor, allowing a statement to be executed again.

This method exists because some drivers do not support executing a PDOStatement object when a previous PDOStatement object has not finished fetching rows from the result set. If this is the case, you may encounter an “out-of-sequence” error, and you should use closeCursor.

In the case of MySQL, PostgreSQL, SQLite drivers, it is likely that you may not need to perform a closeCursor.

public PDOStatement::closeCursor(): bool

columnCount

Returns the number of columns in the result set obtained after executing a query.

public PDOStatement::columnCount(): int

$sentencia->columnCount();

debugDumpParams

Dumps a prepared SQL statement. As the name suggests, it is used for debugging a query to identify any issues.

public PDOStatement::debugDumpParams(): void

errorCode

Gets the SQLSTATE associated with the last operation on the statement handle.

public PDOStatement::errorCode(): string

errorInfo

Retrieves error information associated with the last operation on the statement handle in the form of an array.

public PDOStatement::errorInfo(): array

The elements of the array will contain the following information:

execute

Executes a prepared statement created with PDO::prepare(). The execute method can receive parameter values for placeholders in the form of an array, which can be associative or numeric depending on the type of placeholder used (:name or ?).

public PDOStatement::execute(array $input_parameters = ?): bool

$sentencia->execute();
$sentencia->execute($parameters);

//caso en que los placeholders esten marcados por ?
$inputs_parameter=array("juan","bolivia");

//caso en que los placeholders esten marcados por ?
$inputs_parameter=array(":name"=>"juan",":pais"=>"bolivia");

fetch

Retrieves the next row in the result set.

public PDOStatement::fetch(int $mode = ?, int $cursorOrientation = PDO::FETCH_ORI_NEXT, int $cursorOffset = 0): mixed

fetchAll

Retrieves all remaining rows in the result set.

public PDOStatement::fetchAll(int $mode = PDO::FETCH_DEFAULT): array
public PDOStatement::fetchAll(int $mode = PDO::FETCH_COLUMN, int $column): array
public PDOStatement::fetchAll(int $mode = PDO::FETCH_CLASS, string $class, ?array $constructorArgs): array
public PDOStatement::fetchAll(int $mode = PDO::FETCH_FUNC, callable $callback): array

Parameters

$mode – Controls the content of the array returned, as in the fetch method. Its default value is PDO::FETCH_BOTH.

PDO::FETCH_COLUMN will return an array consisting of all values from a single column; you must specify that column with the $column parameter.

You can get unique results from a column by combining with PDO::FETCH_UNIQUE, and you can do this using | (pipe).

$resultados = $stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE)

To return an associative array grouped by the values of a specific column, you can combine PDO::FETCH_COLUMN and PDO::FETCH_GROUP with | (pipe). The following are dynamic parameters that depend on the fetch mode. They cannot be used with named parameters.

$resultados = $stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_GROUP);

Returns

array – Returns an array depending on the fetch mode used in $mode.

fetchColumn

Returns a single column from the next row in the result set.

public PDOStatement::fetchColumn(int $column_number = 0): mixed

fetchObject

Gets the next row, returning an object representing it.

 public PDOStatement::fetchObject(?string $class = "stdClass", array $constructorArgs = []): object|false

getAttribute

Gets an attribute of a statement. Currently, there are no generic attributes, but there are driver-specific attributes, constants like PDO::ATTR_*.

public PDOStatement::getAttribute(int $name): mixed

getColumnMeta

Gets the metadata of a column in a result set.

public PDOStatement::getColumnMeta(int $column): array|false

getIterator

Gets an Iterator object that allows iterating over the result set.

public PDOStatement::getIterator(): Iterator

In theory, you can do something like this with getIterator. I haven’t tried this method, but perhaps we’ll do it in the future.

// Conectarse a la base de datos
$pdo = new PDO("mysql:host=localhost;dbname=mi_base_de_datos", "usuario", "contraseña");

// Preparar la consulta
$sql = "SELECT * FROM usuarios";
$stmt = $pdo->prepare($sql);

// Ejecutar la consulta
$stmt->execute();

// Obtener el objeto Iterator
$iterator = $stmt->getIterator();

// Iterar sobre el conjunto de resultados
while ($iterator->valid()) {
    // Obtener el elemento actual
    $row = $iterator->current();

    // Imprimir el elemento actual
    echo $row["id"] . " - " . $row["name"] . PHP_EOL;

    // Avanzar al siguiente elemento
    $iterator->next();
}

nextRowset

Advances to the next row in a result set obtained by executing a statement that results in multiple rows (e.g., fetchAll).

public PDOStatement::nextRowset(): bool

rowCount

Returns the number of rows affected by the last SQL statement.

public PDOStatement::rowCount(): int

setAttribute

Sets a statement attribute. This attribute can be any PDO::ATTR_* constant.

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

setFetchMode

Sets the fetch mode for the current statement. Usually, we do this directly when calling fetch, but keep in mind that it can also be done this way. This method is relevant when making multiple queries.

public PDOStatement::setFetchMode(int $mode): bool
public PDOStatement::setFetchMode(int $mode = PDO::FETCH_COLUMN, int $colno): bool
public PDOStatement::setFetchMode(int $mode = PDO::FETCH_CLASS, string $class, ?array $constructorArgs = null): bool
public PDOStatement::setFetchMode(int $mode = PDO::FETCH_INTO, object $object): bool