PHP mysqli class

The PHP mysqli class is the main class of the MySQLi extension of PHP. This class represents a connection to the database created with a MySQL DBMS.

The following synopsis is the same as that given on PHP.net

class mysqli {
/* Propiedades */
int $affected_rows;
int $connect_errno;
string $connect_error;
int $errno;
array $error_list;
string $error;
int $field_count;
int $client_version;
string $host_info;
string $protocol_version;
string $server_info;
int $server_version;
string $info;
mixed $insert_id;
string $sqlstate;
int $thread_id;
int $warning_count;
/* Métodos */
__construct(
    string $host = ini_get("mysqli.default_host"),
    string $username = ini_get("mysqli.default_user"),
    string $passwd = ini_get("mysqli.default_pw"),
    string $dbname = "",
    int $port = ini_get("mysqli.default_port"),
    string $socket = ini_get("mysqli.default_socket")
)
autocommit(bool $mode): bool
change_user(string $user, string $password, string $database): bool
character_set_name(): string
close(): bool
commit(int $flags = ?, string $name = ?): bool
debug(string $message): bool
dump_debug_info(): bool
get_charset(): object
get_client_info(): string
get_connection_stats(): bool
mysqli_stmt::get_server_info(): string
get_warnings(): mysqli_warning
init(): mysqli
kill(int $processid): bool
more_results(): bool
multi_query(string $query): bool
next_result(): bool
options(int $option, mixed $value): bool
ping(): bool
public static poll(
    array &$read,
    array &$error,
    array &$reject,
    int $sec,
    int $usec = ?
): int
prepare(string $query): mysqli_stmt
query(string $query, int $resultmode = MYSQLI_STORE_RESULT): mixed
real_connect(
    string $host = ?,
    string $username = ?,
    string $passwd = ?,
    string $dbname = ?,
    int $port = ?,
    string $socket = ?,
    int $flags = ?
): bool
escape_string(string $escapestr): string
real_query(string $query): bool
public reap_async_query(): mysqli_result
public refresh(int $options): bool
rollback(int $flags = ?, string $name = ?): bool
select_db(string $dbname): bool
set_charset(string $charset): bool
ssl_set(
    string $key,
    string $cert,
    string $ca,
    string $capath,
    string $cipher
): bool
stat(): string
stmt_init(): mysqli_stmt
store_result(int $option = ?): mysqli_result
use_result(): mysqli_result
}

• Properties
  • affected_rows
  • connect_errno
  • connect_error
  • errno
  • error_list
  • error
  • field_count
  • client_version
  • host_info
  • protocol_version
  • server_info
  • server_version
  • info
  • insert_id
  • sqlstate
  • thread_id
  • warning_count
• Métodos
  • __construct
  • autocommit
  • change_user
  • character_set_name
  • close
  • commit
  • debug
  • dump_debug_info
  • get_charset
  • get_client_info(En desuso desde PHP 8.1
  • get_connection_stats
  • get_warnings
  • init(en des-uso vea __construct)
  • kill
  • more_results
  • multi_query
  • next_result
  • options
  • ping
  • prepare
  • query
  • real_connect
  • real_escape_string / escape_string
  • real_query
  • reap_async_query
  • refresh(Deprecated by MySQL)
  • rollback
  • select_db
  • set_charset
  • ssl_set
  • stat
  • stmt_init
  • store_result
  • use_result

Puedes cerrar o abrir este menú cliqueando sobre el boton contenido

Properites in PHP mysqli class

In the properties, we will use the variable $dbconnection, which will be the connection obtained after instantiating the class.

$dbconnection = new mysqli('localhost', 'my_user', 'my_password', 'my_bd');

affected_rows

Returns the number of rows affected in the last query

$dbconnection->affected_rows;

connect_errno

Error code from the last call.

$dbconnection->connect_errno;

connect_error

Returns a string with the description of the last connection error.

$dbconnection->connect_error;

errno

Error code from the last function call

$dbconnection->errno;

error_list

List of errors since the last executed command.

$dbconnection->error_list;

error

String that describes the last error.

$dbconnection->error;

field_count

Number of columns for the most recent query.

$dbconnection->field_count;

client_version

MySQL client version as an integer value. This is the version of the library, allowing us to determine whether we can use certain MySQL features or functionalities.

$dbconnection->client_version;

host_info

String representing the type of connection used.

$dbconnection->host_info;

protocol_version

Returns the version of the protocol used.

$dbconnection->protocol_version;

server_info

MySQL server version returned as a string.

$dbconnection->server_info;

server_version

MySQL server version as an integer

$dbconnection->server_version;

info

Information about the most recent query executed.

$dbconnection->info;

insert_id

This is the value generated by an AUTO_INCREMENT belonging to a column in our database, created by the last query.

It is clear that for the last query to give us this number, the table must have an AUTO_INCREMENT id.

$dbconnection->insert_id;

sqlstate

The SQLSTATE error from a previous operation (query) in MySQL.

$dbconnection->sqlstate;

thread_id

The thread ID of the current connection. You will probably never use this property; you would need a large number of users to worry about the threads connecting to the database

$dbconnection->thread_id;

warning_count

It will return the number of warnings since the last query for the given link.

$dbconnection->warning_count;

PHP mysqli class methods

Here we will see the different methods belonging to our mysqli class. This is the main class of the MySQLi extension and, therefore, has the largest number of methods.

__construct

Opens a new connection to our MySQL server, creating our mysqli object that will represent this connection.

public mysqli::__construct(
    string $host = ini_get("mysqli.default_host"),
    string $username = ini_get("mysqli.default_user"),
    string $passwd = ini_get("mysqli.default_pw"),
    string $dbname = "",
    int $port = ini_get("mysqli.default_port"),
    string $socket = ini_get("mysqli.default_socket")
)

$db = new mysqli($host, $username, $passwd, $dbname, $port, $socket);

Usually, one of the following names is used for the variable that contains the mysqli object: $conn, $db, $dbconn, $dbconnection, $database.

autocommit

This method determines whether we want queries to auto-commit or not. What I must emphasize about this is: we need to be careful when using it, as it makes no sense to use auto-commit if we want to perform a transaction.

For example, if we have several tables to change with an insert, what would happen if it fails in the middle of these inserts? We should be able to roll back, but unfortunately, we have already committed, and that doesn’t make sense.

public mysqli::autocommit(bool $mode): bool

The $mode parameter is of type bool and indicates whether it is enabled or not.

Typically, it is ensured that auto-commit is not enabled when performing a transaction, while it is used enabled for simple queries.

change_user

Changes the user for the database connection.

There are occasions when we may want to change the user with which we connect to the database, as they may have different permissions

public mysqli::change_user(string $user, string $password, string $database): bool

$dbconnection->change_user($user, $password, $database);

character_set_name

Returns the character set of the database connection. See https://dev.mysql.com/doc/refman/8.0/en/charset.html for a better understanding of character sets.

public mysqli::character_set_name(): string

$dbconnection->character_set_name();

close

This method will finalize the connection.

public mysqli::close(): bool

commit

Commits the current transaction.

public mysqli::commit(int $flags = ?, string $name = ?): bool

debug

Performs a debug operation.

We may explore this in depth in the near future. Returns true as the value.

public mysqli::debug(string $options): bool

$option is a string that represents the different operations to be performed, with each operation separated by a colon.

PHP.net provides the following example, which uses an alias function; nevertheless, we will analyze this example.

mysqli_debug("d:t:o,/tmp/client.trace");

Notice that our string is ‘d:t,/tmp/client.trace’; therefore, it will use option ‘d’, option ‘t’, and option ‘o’, which also has ‘,/tmp/client.trace’. This is the file where it will save the data for options ‘d’ and ‘t’. On the other hand, ‘o’ means output without specifying the operation, or this file would not be created.

How would the method be used when utilizing OOP?

Let’s suppose we already have a connection $dbconnection.

$dbconnection->debug("d:t:o,/tmp/client.trace");

In this table, what ‘d’ does is not found, but I have come across another source to learn about what these options do, as they are the same ones used in C/C++ debug.

This blog -> http://tiebing.blogspot.com/2011/10/cc-dbug-library.html

d :Enable output from DBUG_ macros for
for the current state. May be followed
by a list of keywords which selects output
only for the DBUG macros with that keyword.
A null list of keywords implies output for
all macros.

dump_debug_info

Makes a dump of the information while debugging in the log. In simple terms, it creates a dump (or copy, if you prefer) of what is happening in a log file.

The user utilizing this method should be a SUPERUSER or have the highest privileges for that database.

public mysqli::dump_debug_info(): bool

get_charset

Returns the character set as an object (stdClass), which provides several properties of the character set:

public mysqli::get_charset(): ?object

Returns an object of type stdClass with the following data:

get_client_info(En desuso desde PHP 8.1, use $client_info)

Obtains information about the MySQL client; when this method is run, we will get a string representing the library of the MySQL client.

public mysqli::get_client_info(): string

Returns information about the MySQL client as a string.

get_connection_stats

Returns statistics of the MySQL client connection. This data is very technical, and you will probably never use this method.

public mysqli::get_connection_stats(): array

Returns an array with the stats of the connection.

get_warnings

Obtains the result of running the SHOW WARNINGS query in MySQL. The result is returned as a mysqli_warning object.

SHOW WARNINGS is a diagnostic statement that displays information about the conditions (errors, warnings, and notes) resulting from executing a statement in the current session.

mysql.com

It will return a mysqli_warning object or false in case of an error

public mysqli::get_warnings(): mysqli_warning|false

init(en des-uso vea __construct)

We will not cover this function/method.

kill

Requests the MySQL server to terminate a thread process. This method is related to the thread_id, as the process ID corresponds to this ID

public mysqli::kill(int $process_id): bool

more_results

Checks if there are more results from a multi_query.

public mysqli::more_results(): bool

multi_query

Executes one or more queries to the database.

public mysqli::multi_query(string $query): bool

next_result

It is the next result if multi_query has been used. It must be used in conjunction with multi_query.

public mysqli::next_result(): bool

options

With this method, we can set options that change the behavior for a connection. For example, we can specify that the connection lasts a certain amount of time; after all, we may not want to keep a connection always open as it consumes resources.

public mysqli::options(int $option, mixed $value): bool

$dbconnection->options($option,$value);

ping

Pings the connection to the MySQL server; if the connection is down, it attempts to reconnect.

 public mysqli::ping(): bool

If the ping or reconnection is successful, the return will be true; otherwise, it will be false.

prepare

This method allows us to create a prepared statement for execution. It returns a mysqli_stmt object or false in case of an error.

public mysqli::prepare(string $query): mysqli_stmt|false

query

Allows us to perform a query on our database; the query will be passed as a parameter in the form of a string.

public mysqli::query(string $query, int $result_mode = MYSQLI_STORE_RESULT): mysqli_result|bool

real_connect

Opens a connection to the MySQL server; this connection uses the connection established with the __construct constructor.

 
public mysqli::real_connect(
    string $host = ?,
    string $username = ?,
    string $passwd = ?,
    string $dbname = ?,
    int $port = ?,
    string $socket = ?,
    int $flags = ?
): bool

real_escape_string / escape_string

Escapes special characters in a string for use in an SQL statement, taking into account the current character set of the connection.

public mysqli::real_escape_string(string $string): string

Pay close attention to the following example from php.net.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$city = "'s-Hertogenbosch";
/* this query with escaped $city will work */
$query = sprintf("SELECT CountryCode FROM City WHERE name='%s'",
    $mysqli->real_escape_string($city));
$result = $mysqli->query($query);
printf("Select returned %d rows.\n", $result->num_rows);
/* this query will fail, because we didn't escape $city */
$query = sprintf("SELECT CountryCode FROM City WHERE name='%s'", $city);
$result = $mysqli->query($query);

In the example, mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); is used to ensure that the escaping function must be used. But what would happen if we didn’t use this escape? It could easily allow someone to delete all the data from our table.

For example, if I have a form where I input the value I’m searching for, but instead of passing a city name, I pass something like "'s-Hertogenbosch" and instead enter something similar to weak";DROP TABLE City;

And our table disappears, meaning something like this

In the future, I believe we will talk about this.

There is a page that explains this topic very well, and it is https://portswigger.net/web-security/sql-injection.

real_query

Executes an SQL query on our database. As we mentioned before, using query with mysqli in PHP instead of prepare can lead to significant problems with SQL injection attacks. It is recommended to use prepare whenever we have inputs.

public mysqli::real_query(string $query): bool

reap_async_query

Retrieves the result of an asynchronous query. To use this method, you will need the mysqlnd (MySQL Native Driver).

public mysqli::reap_async_query(): mysqli_result[obj]

Returns a mysqli_result object, or false in case of an error.

refresh(Deprecated by MySQL)

Performs a refresh operation, flushing tables or caches, or resetting replicated server information.

rollback

Reverts the ongoing transaction.

public mysqli::rollback(int $flags = 0, ?string $name = null): bool

select_db

Selects the database. Although it’s possible to change the default database for a query, it’s recommended to use this method only to set the connection database, which can already be done with the constructor method.

You can change the database of the connection as long as the user remains the same.

ublic mysqli::select_db(string $database): bool

set_charset

Setea el charset de la conexión cliente MySQL para data proveniente desde el servidor, como para data hacia el servidor.

Si necesitas saber las charset que puedes usar este link muestra todas las charset de MySQL posibles

 public mysqli::set_charset(string $charset): bool

ssl_set

ssl_set es utilizado para establecer una conexión segura utilizando SSL.

public mysqli::ssl_set(
    ?string $key,
    ?string $certificate,
    ?string $ca_certificate,
    ?string $ca_path,
    ?string $cipher_algos
): bool

stat

Gets the current system status

public mysqli::stat(): string|false

stmt_init

Initializes a prepared statement and creates a mysqli_stmt object.

public mysqli::stmt_init(): mysqli_stmt|false

$stmt = $dbconnection->stmt_init();

store_result

Transfers a result set from the last executed query

public mysqli::store_result(int $mode = 0): mysqli_result|false

use_result

Starts retrieving a result set. Essentially, suppose you run a query and need the result later. This method will return the result of the last executed query

public mysqli::use_result(): mysqli_result|false