Blog de programación, errores, soluciones

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

PHP mysqli_stmt class

The PHP class mysqli_stmt represents a prepared statement in MySQL.

This class is part of PHP’s MySQLi extension
Sinopsis
 class mysqli_stmt {
/* Properties */
public readonly int|string $affected_rows;
public readonly int|string $insert_id;
public readonly int|string $num_rows;
public readonly int $param_count;
public readonly int $field_count;
public readonly int $errno;
public readonly string $error;
public readonly array $error_list;
public readonly string $sqlstate;
public int $id;
/* Methods */
public __construct(mysqli $mysql, ?string $query = null)
public attr_get(int $attribute): int
public attr_set(int $attribute, int $value): bool
public bind_param(string $types, mixed &$var, mixed &...$vars): bool
public bind_result(mixed &$var, mixed &...$vars): bool
public close(): bool
public data_seek(int $offset): void
public execute(?array $params = null): bool
public fetch(): ?bool
public free_result(): void
public get_result(): mysqli_result|false
public get_warnings(): mysqli_warning|false
public more_results(): bool
public next_result(): bool
public num_rows(): int|string
public prepare(string $query): bool
public reset(): bool
public result_metadata(): mysqli_result|false
public send_long_data(int $param_num, string $data): bool
public store_result(): bool
}
contenido

The mysqli_stmt class is generally initialized from the mysqli class using the prepare method.

Another way to initialize mysqli_stmt is by using the stmt_init method on a mysqli object.

In short, we have two different ways to initialize the mysqli_stmt class from another class, which are:

We can also create a mysqli_stmt object using its constructor.

Properites

https://blastcoding.com/en/php-mysqli_stmt-class/#properties

Before reviewing the properties, keep in mind that we need to have a mysqli_stmt object. In every property that we will see below, this object is represented by the variable $stmt.

affected_rows

https://blastcoding.com/en/php-mysqli_stmt-class/#affected_rows

Retrieves the number of rows affected by the last MySQL query executed using a prepared statement.

Sintaxis
$stmt->affected_rows;

insert_id

https://blastcoding.com/en/php-mysqli_stmt-class/#insert_id

Returns the ID generated in the previous INSERT operation if it has an AUTO_INCREMENT ID.

The previous operation must have an AUTO_INCREMENT ID.

Sintaxis
$stmt->insert_id;

num_rows

https://blastcoding.com/en/php-mysqli_stmt-class/#num_rows

Number of rows from a previously executed query.

Sintaxis
$stmt->num_rows;

param_count

https://blastcoding.com/en/php-mysqli_stmt-class/#param_count

The number of parameters passed in the query made with prepare.

Sintaxis
$stmt->param_count;

field_count

https://blastcoding.com/en/php-mysqli_stmt-class/#field_count

The number of fields used in the prepared query will be returned.

Sintaxis
$stmt->field_count;

errno

https://blastcoding.com/en/php-mysqli_stmt-class/#errno

Error code of the most recent query executed, regardless of whether it succeeded or failed. You can view error codes at https://dev.mysql.com/doc/dev/mysql-server/latest/errmsg_8h.html.

Sintaxis
$stmt->errno;

error

https://blastcoding.com/en/php-mysqli_stmt-class/#error

Description of the last error obtained after executing a query (SQL statement).

Sintaxis
$stmt->error;

error_list

https://blastcoding.com/en/php-mysqli_stmt-class/#error_list

List of errors from the last executed query in the form of an array. Each error contains an associative array with errno, error, and sqlstate.

Sintaxis
$stmt->error_list;

sqlstate

https://blastcoding.com/en/php-mysqli_stmt-class/#sqlstate

SQLSTATE error code from the previous operation.

Sintaxis
$stmt->sqlstate;

id

https://blastcoding.com/en/php-mysqli_stmt-class/#id

Prepare statement ID

Sintaxis
$stmt->id;

Methods

https://blastcoding.com/en/php-mysqli_stmt-class/#methods

Just like with the properties, we will represent the mysqli_stmt object with the variable $stmt in the syntax boxes.

Before continue, we need to explain binding?

In programming, binding refers to the process of associating a variable, value, or resource with an identifier or placeholder. In the context of database queries and prepared statements, binding typically means associating actual values (like user input) with the placeholders (like ? or named placeholders) in a query before the query is executed.

__construct

https://blastcoding.com/en/php-mysqli_stmt-class/#__construct

There isn’t much to say __construct, as in any class, is the constructor method. With it, an object of the mysqli_stmt class is created. It’s clear that this class depends on the mysqli class, so we must create an object of that class first.

Description / Descripción
public mysqli_stmt::__construct(mysqli $mysqli, ?string $query = null)
Sintaxis
$stmt = new mysqli_stmt($mysqli,$query);

Parameters

$mysqli -Variable that contains a mysqli object or the object itself—try to avoid the latter. It’s better to be as clean and organized as possible.

$query – This parameter is optional, its default value is null.

attr_get

https://blastcoding.com/en/php-mysqli_stmt-class/#attr_get

Retrieves the value of an attribute of a prepared statement. These attributes modify the behavior of the prepared statement and can be set using attr_set.

Description / Descripción
public mysqli_stmt::attr_get(int $attribute): int
Sintaxis
$at = $stmt->attr_get($atribute);

If I may, the official PHP site’s information on this method is quite vague, so let’s test it out.

After doing some testing, it seems related to mysql-stmt-attr-get from MySQL. You can find more information here.

It appears to be the number of the option we have set.

attr_set

https://blastcoding.com/en/php-mysqli_stmt-class/#attr_set

It sets the attributes of a prepared statement, allowing us to modify the behavior of the prepared statement

Description / Descripción
public mysqli_stmt::attr_set(int $attribute, int $value): bool

Este método está directamente ligado a mysql-stmt-attr-set(MySQL function)

https://dev.mysql.com/doc/c-api/5.6/en/mysql-stmt-attr-set.html

bind_param

https://blastcoding.com/en/php-mysqli_stmt-class/#bind_param

This method helps to bind variables as parameters to a prepared statement query.

Description / Descripción
public mysqli_stmt::bind_param(string $types, mixed &$var, mixed &...$vars): bool
Sintaxis
$stmt->bind_param($types,$var1,$var2, ..varN);

Parameters

$types – it is a string specifying the different types of values, see the following table:

CharacterDescription
icorresponding variable has type int
dcorresponding variable has type float
scorresponding variable has type string
bcorresponding variable is a blob and will be sent in packets

For example, if $types = "idds" our method is expecting the following variables to be integer, float, float, string respectively.

$var1, $var2, …$varN – different variables that will be the parameters in the prepare statement, the number of variables must be equal to the number of characters in $types.

Returns

true or false depending on whether it was successful or not.

bind_result

https://blastcoding.com/en/php-mysqli_stmt-class/#bind_result

Binds variables to a prepared statement, and the query result is stored in them so they can be used later.

The statement must already have been executed before using this method.

Description / Descripción
public mysqli_stmt::bind_result(mixed &$var, mixed &...$vars): bool
Sintaxis
$stmt->bind_result($var1,$var2,$var3, ..$varN)

Parameters

$var1, $var2, $var3, ..$varN – These variables will be the columns you want to retrieve from the query.

Returns

It will return true or false depending on whether it succeeded or not.

close

https://blastcoding.com/en/php-mysqli_stmt-class/#close

Closes a prepared statement; if there are pending results, it will still close, canceling them.

Description / Descripción
public mysqli_stmt::close(): bool
Sintaxis
$stmt->close();

data_seek

https://blastcoding.com/en/php-mysqli_stmt-class/#data_seek

Retrieves a row based on its $offset parameter from the results obtained after executing a statement (prepared statement). We must call the store_result method before calling this method.

Description / Descripción
public mysqli_stmt::data_seek(int $offset): void
Sintaxis
$stmt->data_seek($offset);

Parameter

$offset – It will tell which row we want to obtain, offset starts from zero, and therefore, the row we want to obtain we must subtract 1(-1).

execute

https://blastcoding.com/en/php-mysqli_stmt-class/#execute

With the execute method, we will execute the prepared statement.

Description / Descripción
public mysqli_stmt::execute(?array $params = null): bool
Sintaxis
$stmt->execute($params);

Parameters

$params – This will be an array with the different values that will go into the parameters of the prepared statement.

To simplify this, you can use, for example, ["hello", 2, "parameter 3", $parameter4], or if you have the parameters in different variables, use compact.

You can see more about compact function in https://blastcoding.com/en/php-compact-function/

Finally, the solution that seems best to me is not to pass parameters and instead use bind_param before using execute.

Returns

It will return true or false depending on whether the execution was successful or not.

fetch

https://blastcoding.com/en/php-mysqli_stmt-class/#fetch

Retrieves the results of a prepared statement after it has been executed. This method depends on execute, as we cannot get results without running the query.

Description / Descripción
public mysqli_stmt::fetch(): ?bool
Sintaxis
$stmt->fetch();

Returns

If data is retrieved, it will return true; if an error occurred, it will return false; and if there are no rows, it will return null. The latter can also occur if data truncation happened, whether intentional or not.

free_result

https://blastcoding.com/en/php-mysqli_stmt-class/#free_result

Releases the memory allocated when using the store_result method, allowing us to manage the current statement.

This method is related to store_result.

Description / Descripción
public mysqli_stmt::free_result(): void
Sintaxis
$stmt->free_result();

get_result

https://blastcoding.com/en/php-mysqli_stmt-class/#get_result

Retrieves the results of the prepared statement as a mysqli_result object. This method should not be used together with store_result.

Description / Descripción
public mysqli_stmt::get_result(): mysqli_result|false
Sintaxis
$result = $stmt->get_result();

get_warnings

https://blastcoding.com/en/php-mysqli_stmt-class/#get_warnings

Retrieves the result of the SHOW WARNINGS query.

SHOW WARNINGS returns information about various conditions (errors, warnings, and notes).

Ejemplo de SHOW WARNINGS en MYSQL
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'b' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'a' cannot be null
*************************** 3. row ***************************
  Level: Warning
   Code: 1264
Message: Out of range value for column 'a' at row 3
3 rows in set (0.00 sec)
Description / Descripción
public mysqli_stmt::get_warnings(): mysqli_warning|false
Sintaxis
$warnings = $stmt->get_warnings();

more_results

https://blastcoding.com/en/php-mysqli_stmt-class/#more_results

Checks if there are more query results when a multiple query has been made.

Description / Descripción
public mysqli_stmt::more_results(): bool

next_result

https://blastcoding.com/en/php-mysqli_stmt-class/#next_result

Retrieves the next result of a multiple query.

Description / Descripción
public mysqli_stmt::next_result(): bool

num_rows

https://blastcoding.com/en/php-mysqli_stmt-class/#num_rows

Number of rows returned in the query; you will need to use store_result to use this method.

Description / Descripción
public mysqli_stmt::num_rows(): int|string

prepare

https://blastcoding.com/en/php-mysqli_stmt-class/#prepare

Prepares a prepared statement for execution. If the statement has parameters, they must be indicated with the ? symbol, which is also known as a placeholder.

Description / Descripción
public mysqli_stmt::prepare(string $query): bool
Sentencia
$stmt->prepare($query);

Parameter

$query – This will be the query to be executed, for example: 'SELECT street, number, neighborhood FROM addresses WHERE street=?'. Remember that ? indicates the parameter we will pass with bind_param or during the execute execution.

reset

https://blastcoding.com/en/php-mysqli_stmt-class/#result

Resets a prepared statement, returning it to the state it was in before running the prepare.

Description / Descripción
public mysqli_stmt::reset(): bool

Returns

Returns true if it can reset, false in case of error.

result_metadata

https://blastcoding.com/en/php-mysqli_stmt-class/#result_metadata

Returns a mysqli_result object with the metadata of a prepared statement. The statement must be executed with execute beforehand to obtain a result before using result_metadata.

Description / Descripción
public mysqli_stmt::result_metadata(): mysqli_result|false
Sintaxis
$stmt->result_metadata();

Returns

In case of success, it will return a mysqli_result object.

False in case of failure.

send_long_data

https://blastcoding.com/en/php-mysqli_stmt-class/#send_long_data

It allows us to send the data in chunks (sections) of parameters of our prepared statement.

Description / Descripción
public mysqli_stmt::send_long_data(int $param_num, string $data): bool
Sintaxis
$stmt->send_long_data($param,$data);

Returns

Returns true if it can send the data, false in case of failure.

store_result

https://blastcoding.com/en/php-mysqli_stmt-class/#store_result

Stores the result of a prepared statement in an internal buffer; these results can be retrieved with fetch.

Description / Descripción
public mysqli_stmt::store_result(): bool
Sintaxis
$stmt->store_result();

Return

true if successful, false if it fails.

<?php
$stmt = $mysqli->prepare("SELECT * FROM your_table");
$stmt->execute();
// Store the result set in memory
$stmt->store_result();
// Bind variables to the result set columns
$stmt->bind_result($column1, $column2, $column3);
// Fetch and process each row
while ($stmt->fetch()) {
    echo "$column1 $column2 $column3<br>";
}
$stmt->close();
?>
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