Blog de programación, errores, soluciones

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

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

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

Many of the methods we have are for binding, so I find it necessary to explain what is meant by binding.

In programming, a bind is an operation that associates a value with a symbol. The symbol can be a variable, a constant, or an expression.

In our case, we will be associating values with variables and values from the database with variables, as well as with query parameters.

[ADS_FD/]

bindColumn

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

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

Parameters

$column – It must be the number (starting at 1) or the name of the column.

$var – Name of the variable with which the column will be associated, for example, $name.

$type -The data type of the parameter is specified by the classes:

  • PDO::PARAM_INT – It represents a parameter of type integer.
  • PDO::PARAM_STR – It represents a parameter of type string.
  • PDO::PARAM_BOOL – It represents a parameter of type boolean.o.
  • PDO::PARAM_NULL – It represents a parameter of type null.
  • PDO::PARAM_LOB – It represents a parameter of type Binary Large Object (BLOB).
Under no circumstances should you avoid defining this parameter; it is always better to define it.

$maxLength – The expected maximum length of the data in the column.

$driverOptions – Optional parameters for the driver.

Returns

true – in case of success

false – in case of failure

[ADS_FD/]

bindParam

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

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

Parameters

$param – Parameter identifier, for example, if your SQL query is something like:

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

The value of your $param will be ":name".

However, if you are using question marks ? as placeholders in your query, your $param should use the parameter position, starting from 1. For example:

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

In this case, the value you should give to $param is 1, as we only have 1 parameter, and it is in the first position.

$var – The name of the PHP variable that will be bound to the parameter in the SQL statement.

$type – The data type for the parameter; these can be:

  • PDO::PARAM_NULL: Represents a parameter of type null.
  • PDO::PARAM_INT: Represents a parameter of type integer.
  • PDO::PARAM_STR: Represents a parameter of type string.
  • PDO::PARAM_BOOL: Represents a parameter of type boolean.
  • PDO::PARAM_LOB: Represents a parameter of type Binary Large Object (BLOB).
  • PDO::PARAM_STMT: Represents a parameter of type statement. (Not supported by any driver – created for future applications)
  • PDO::PARAM_INPUT_OUTPUT: Represents a parameter that is both input and output for stored procedures.

$maxLength – The expected maximum length of the data in the column (in bytes).

$driverOptions -Optional parameters for the driver.

Return

true – In case of success.

false – If it fails.

[ADS_FD/]

bindValue

Associates a value with a parameter

Description / Descripción
public PDOStatement::bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool

Parameters

$param – The parameter identifier can be a number starting from 1 or the name given to the parameter in the SQL statement.

$value – The value that will be associated with the parameter.

$type – The data type that the parameter will be can be one of the following constants:

  • PDO::PARAM_NULL: Represents a parameter of type null.
  • PDO::PARAM_INT: Represents a parameter of type integer.
  • PDO::PARAM_STR: Represents a parameter of type string.
  • PDO::PARAM_BOOL: Represents a parameter of type boolean.
  • PDO::PARAM_LOB: Represents a parameter of type Binary Large Object (BLOB).
  • PDO::PARAM_STMT: Represents a parameter of type statement. (Not supported by any driver – created for future applications)
  • PDO::PARAM_INPUT_OUTPUT: Represents a parameter that is both input and output for stored procedures.

Returns

true – In case of success.

false – If it fails.

PHP’s bindValue() and bindParam() functions are used to bind parameters to variables in an SQL query. The main difference between them is that bindParam() takes a reference to a variable as an argument, while bindValue() can take either a variable or a literal value as an argument.
[ADS_FD/]

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.

Description / Descripción
public PDOStatement::closeCursor(): bool

Returns

true – In case of success.

false – If it fails.

[ADS_FD/]

columnCount

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

Description / Descripción
public PDOStatement::columnCount(): int
Sintaxis / Sintax
$sentencia->columnCount();

debugDumpParams

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

Description / Descripción
public PDOStatement::debugDumpParams(): void

errorCode

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

Description / Descripción
public PDOStatement::errorCode(): string

errorInfo

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

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

The elements of the array will contain the following information:

0 SQLSTATE error code (a five-character alphanumeric identifier defined in the ANSI SQL standard).
1 Driver-specific error code.
2 Driver-specific error message.

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 ?).

Description / Descripción
public PDOStatement::execute(array $input_parameters = ?): bool
Sintaxis / Sintax
$sentencia->execute();
$sentencia->execute($parameters);

Parameters

$inputs_parameters – By default, $inputs_parameter is optional. If values are passed, you should use an associative or numeric array depending on how the placeholders are marked.

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

In case we are using named placeholders:

//caso en que los placeholders esten marcados por ?
$inputs_parameter=array(":name"=>"juan",":pais"=>"bolivia");
My recommendation is to use bindParam or bindValue instead of passing parameters in this way.

Returns

true – In case of success.

false – If it fails.

fetch

Retrieves the next row in the result set.

Description / Descripción
public PDOStatement::fetch(int $mode = ?, int $cursorOrientation = PDO::FETCH_ORI_NEXT, int $cursorOffset = 0): mixed

Parameters

$mode – Controls how the next row will be fetched. The value of $mode must be one of the following constants:

The default value of $mode is PDO::FETCH_BOTH.
  • PDO::FETCH_ASSOC
  • PDO::FETCH_BOTH
  • PDO::FETCH_BOUND
  • PDO::FETCH_CLASS
  • PDO::FETCH_INTO
  • PDO::FETCH_LAZY
  • PDO::FETCH_NAMED
  • PDO::FETCH_NUM
  • PDO::FETCH_OBJ
  • PDO::FETCH_PROPS_LATE

You can learn more about what these constants do at https://www.php.net/manual/en/pdo.constants.php. The most commonly used constants are: PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_OBJ.


$cursorOrientation – The value of this parameter determines which row will be fetched. Its value should be one of the following constants:

  • PDO::FETCH_ORI_NEXT – This is the default value. It indicates that the cursor will move to the next row.
  • PDO::FETCH_ORI_PRIOR – Indicates that the cursor will move to the previous row.
  • PDO::FETCH_ORI_FIRST – Indicates that the cursor will move to the first row.
  • PDO::FETCH_ORI_LAST – Indicates that the cursor will move to the last row.
  • PDO::FETCH_ORI_ABS – Indicates that the cursor will move to a specific row. You’ll need to provide a valid row number.
  • PDO::FETCH_ORI_REL – Indicates that the cursor will move to a row relative to its current position. You’ll need to provide a valid relative row number.

$cursorOffset – Its default value is 0. This value specifies the row number to retrieve and is used in conjunction with the $cursorOrientation parameter if it has the value PDO::FETCH_ORI_ABS or PDO::FETCH_ORI_REL.

$cursorOrientation = PDO::FETCH_ORI_ABS

If $cursorOrientation has the value PDO::FETCH_ORI_ABS, this value specifies the absolute row number in the result set that you want to retrieve.

$cursorOrientation = PDO::FETCH_ORI_REL

On the other hand, if $cursorOrientation has the value PDO::FETCH_ORI_REL, this value specifies the row to retrieve relative to the cursor’s position before PDOStatement::fetch() is called.

Returns

mixed – It will depend on $mode what will be returned.

false – In case of an error or if there are no more rows to return.

fetchAll

Retrieves all remaining rows in the result set.

Description / Descripción
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);
$mode = PDO::FETCH_COLUMN

$column -This parameter is used when $mode has the value PDO::FETCH_COLUMN. With $column, you indicate which column you want to retrieve, where 0 represents the first column

$mode = PDO::FETCH_CLASS

$class – This parameter should be used when $mode has the value PDO::FETCH_CLASS. It will be the name of the class to which the columns of the row will be mapped to named properties in the class.

$constructorArgs – Arguments of the custom class constructor when the $mode parameter is PDO::FETCH_CLASS.

$mode = PDO::FETCH_FUNC

$callback – You should use this parameter when using PDO::FETCH_FUNC as the value for $mode. The value of $callback will be a user-provided custom function where column values will be used as parameters.

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.

Description / Descripción
public PDOStatement::fetchColumn(int $column_number = 0): mixed

Parameters

$column_number – The number of the column to retrieve; the first column is 0, and the default value if not specified is also 0.

Returns

mixed – The return will depend on the data type of the column.

false – If there are no more rows.

fetchObject

Gets the next row, returning an object representing it.

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

Parameters

$class – Name of the class to instantiate if not specified; it will use stdClass if not provided.

$constructorArgs – An array that holds the parameters to be passed to the constructor of the class specified in $class.

Returns

object – Returns an instance of the class specified by $class.

false – If it fails.

getAttribute

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

Description / Descripción
public PDOStatement::getAttribute(int $name): mixed

getColumnMeta

Gets the metadata of a column in a result set.

Description / Descripción
public PDOStatement::getColumnMeta(int $column): array|false

Parameters

$column – It is the number of the column, starting from 0.

Returns

An array with the metadata, which includes the following:

  • name – The name of the column in the database.
  • table – The name of the table to which the column belongs.
  • native_type – The native data type of the column.
  • driver:decl_type – The SQL data type of the column.
  • flags – The flags set for the column.
  • len – The length of the column.
  • precision – The numeric precision of the column.
  • pdo_type – The data type of the column according to the PDO::PARAM_* constants.

false – If the required column does not exist or there are no results.

getIterator

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

Description / Descripción
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).

Description / Descripción
public PDOStatement::nextRowset(): bool

rowCount

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

Description / Descripción
public PDOStatement::rowCount(): int

setAttribute

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

Description / Descripción
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.

Description / Descripción
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

Parámetros

$mode – must be one of the following constants:

  • PDO::FETCH_ASSOC
  • PDO::FETCH_BOTH
  • PDO::FETCH_BOUND
  • PDO::FETCH_CLASS
  • PDO::FETCH_COLUMN
  • PDO::FETCH_INTO
  • PDO::FETCH_LAZY
  • PDO::FETCH_NAMED
  • PDO::FETCH_NUM
  • PDO::FETCH_OBJ
  • PDO::FETCH_PROPS_LATE
$mode = PDO::FETCH_COLUMN

$colno – This parameter is used when $mode = PDO::FETCH_COLUMN to specify the column you want to retrieve.

$mode = PDO::FETCH_CLASS

$class – In case you use $mode = PDO::FETCH_CLASS, with this parameter, you specify the class you will use, for example, Usuario or Usuario::class, not an initialized class instance like $usuario.

$constructorArgs – You may need to initialize some parameters of the class specified in $class. In this parameter, you should pass an array with those parameters.

If you do not specify a class, the stdClass class will be used.

$mode = PDO::FETCH_INTO

$object – This is the object where the mapping will occur.

Retornos

true – in case of success

false – in case of failure

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