PHP mysqli_stmt class

The PHP class mysqli_stmt represents a prepared statement in MySQL.

 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
}

• Propiedades
  • affected_rows
  • insert_id
  • num_rows
  • param_count
  • field_count
  • errno
  • error
  • error_list
  • sqlstate
  • id
• Métodos
  • __construct
  • attr_get
  • attr_set
  • bind_param
  • bind_result
  • close
  • data_seek
  • execute
  • fetch
  • free_result
  • get_result
  • get_warnings
  • more_results
  • next_result
  • num_rows
  • prepare
  • reset
  • result_metadata
  • send_long_data
  • store_result

Puedes cerrar o abrir este menú cliqueando sobre el boton 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:

• mysqli prepare
• mysqli stmt_init

We can also create a mysqli_stmt object using its constructor.

Properites

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

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

$stmt->affected_rows;

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.

$stmt->insert_id;

num_rows

Number of rows from a previously executed query.

$stmt->num_rows;

param_count

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

$stmt->param_count;

field_count

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

$stmt->field_count;

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.

$stmt->errno;

error

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

$stmt->error;

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.

$stmt->error_list;

sqlstate

SQLSTATE error code from the previous operation.

$stmt->sqlstate;

id

Prepare statement ID

$stmt->id;

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

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.

public mysqli_stmt::__construct(mysqli $mysqli, ?string $query = null)

$stmt = new mysqli_stmt($mysqli,$query);

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.

public mysqli_stmt::attr_get(int $attribute): int

$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

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

public mysqli_stmt::attr_set(int $attribute, int $value): bool

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

bind_param

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

public mysqli_stmt::bind_param(string $types, mixed &$var, mixed &...$vars): bool

$stmt->bind_param($types,$var1,$var2, ..varN);

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.

public mysqli_stmt::bind_result(mixed &$var, mixed &...$vars): bool

$stmt->bind_result($var1,$var2,$var3, ..$varN)

close

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

public mysqli_stmt::close(): bool

$stmt->close();

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.

public mysqli_stmt::data_seek(int $offset): void

$stmt->data_seek($offset);

execute

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

public mysqli_stmt::execute(?array $params = null): bool

$stmt->execute($params);

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.

public mysqli_stmt::fetch(): ?bool

$stmt->fetch();

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.

public mysqli_stmt::free_result(): void

$stmt->free_result();

get_result

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

public mysqli_stmt::get_result(): mysqli_result|false

$result = $stmt->get_result();

get_warnings

Retrieves the result of the SHOW WARNINGS query.

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

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)

public mysqli_stmt::get_warnings(): mysqli_warning|false

$warnings = $stmt->get_warnings();

more_results

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

public mysqli_stmt::more_results(): bool

next_result

Retrieves the next result of a multiple query.

public mysqli_stmt::next_result(): bool

num_rows

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

public mysqli_stmt::num_rows(): int|string

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.

public mysqli_stmt::prepare(string $query): bool

$stmt->prepare($query);

reset

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

public mysqli_stmt::reset(): bool

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.

public mysqli_stmt::result_metadata(): mysqli_result|false

$stmt->result_metadata();

send_long_data

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

public mysqli_stmt::send_long_data(int $param_num, string $data): bool

$stmt->send_long_data($param,$data);

store_result

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

public mysqli_stmt::store_result(): bool

$stmt->store_result();

<?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();
?>