PHP and MySQLi (MySQL Improved)

MySQLi is an improved version of the older MySQL driver. It allows us to access MySQL databases in two ways: procedurally (using functions) and via object-oriented programming. In this tutorial, we will focus on the procedural method, as it is most suitable for beginners and those migrating from older systems.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "enterPassword";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);
if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

$table = "CREATE TABLE mysqli_table (
    id_table INT(100) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fruit VARCHAR(255) NOT NULL,
    vegetable VARCHAR(150) NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if (mysqli_query($connection, $table)) {
    echo "Table 'mysqli_table' has been created successfully.";
} else {
    echo "Error: " . mysqli_error($connection);
}

mysqli_close($connection);

RESULT

The result will be displayed here...

Why Use MySQLi?

The MySQLi extension offers numerous improvements compared to the old MySQL interface. The main advantages are:

Dual interface: supports both procedural and object-oriented programming.
Support for prepared statements, which significantly increases security.
Enhanced debugging support.
Optimized performance specifically for MySQL servers.

Comparison Between MySQLi and PDO

Feature	MySQLi	PDO
Database Support	MySQL only	12 different databases
Working Mode	Procedural and Object-oriented	Object-oriented only
Prepared Statements	Yes	Yes (including named parameters)
Performance	Slightly faster (for MySQL)	Slightly slower

In a MySQLi connection, we check for success using the mysqli_connect_error() function. If something goes wrong, the function returns the error description, which helps us quickly debug issues during development.

Overview of the Most Common Operations

Below is a list of key commands that we will cover in this tutorial. Each command includes an interactive example where you can test the PHP code and observe how the content changes in our simulator.

Command / Method	Description	Examples
`CREATE DATABASE`	Creates a new database on the server.	▶ View Code
`CREATE TABLE`	Creates a new table with specified columns in the database.	▶ View Code
`INSERT INTO`	Inserts a single record into the selected table.	▶ View Code
`INSERT MULTIPLE`	Inserts multiple rows of data using a single command.	▶ View Code
`SELECT`	Retrieves and displays data from the table.	▶ View Code
`LAST ID`	Retrieves the ID number of the last inserted record.	▶ View Code
`PREPARED`	Prepared statements for secure database operations.	▶ View Code
`WHERE`	Filters data based on specific conditions.	▶ View Code
`ORDER BY`	Sorts results (e.g., alphabetically or by date).	▶ View Code
`DELETE`	Deletes specific records from the table.	▶ View Code
`UPDATE`	Updates existing data within the table.	▶ View Code
`REPLACE`	Replaces an existing record or inserts a new one if it does not exist yet.	▶ View Code
`LIMIT`	Limits the number of rows to be displayed.	▶ View Code

PHP MySQLi Quick Reference (Cheat Sheet)

The table below provides an overview of the most important PHP MySQLi functions and constants. For each function, you can view a detailed description and a ready-to-use code example – simply click the button in the right column to open a modal window with an example tailored for our interactive simulator.

Iskanje funkcije: Označi zadetke Najdeno: -

Function / Constant Name	Description	Simulator Example
`mysqli_affected_rows()`	Returns the number of affected rows in the last query (SELECT, INSERT, UPDATE, DELETE). Excellent for verifying operation success.	▶ View Code
`MYSQLI_ASSOC`	Constant for the associative fetch mode. Enables accessing data via column names (e.g., `$row['sadez']`).	▶ View Code
`MYSQLI_ASYNC`	Constant that allows executing queries asynchronously without waiting for the server response.	▶ View Code
`mysqli_autocommit()`	Turns on or off the auto-committing of database modifications.	▶ View Code
`mysqli_begin_transaction()`	Starts a new database transaction safely (disables autocommit).	▶ View Code
`mysqli_change_user()`	Changes the active user and default database for the currently open connection.	▶ View Code
`mysqli_character_set_name()`	Returns the default character set (charset) currently used for the connection.	▶ View Code
`mysqli_close()`	Closes a previously opened database connection and frees up resources.	▶ View Code
`mysqli_commit()`	Commits and permanently saves all modifications of the current transaction to the database.	▶ View Code
`mysqli_connect()`	Opens a new connection to a MySQL server. The baseline for any database work.	▶ View Code
`mysqli_connect_errno()`	Returns the error code from the last connection attempt to the server.	▶ View Code
`mysqli_connect_error()`	Returns the text description of the error from the last connection attempt.	▶ View Code
`mysqli_data_seek()`	Moves the internal result pointer to an arbitrary row (e.g., resetting to 0).	▶ View Code
`mysqli_debug()`	Performs advanced debugging operations to track errors.	▶ View Code
`mysqli_dump_debug_info()`	Dumps current debugging information directly into the system log.	▶ View Code
`mysqli_errno()`	Returns the numerical error code for the last executed SQL command.	▶ View Code
`mysqli_error()`	Returns the textual description of the last error from an SQL query execution.	▶ View Code
`mysqli_error_list()`	Returns an array of all errors for the last executed command.	▶ View Code
`mysqli_fetch_all()`	Fetches all result rows at once as an associative array, a numeric array, or both.	▶ View Code
`mysqli_fetch_array()`	Fetches the next result row as a combined, numeric, or associative matrix.	▶ View Code
`mysqli_fetch_assoc()`	Fetches a result row exclusively as an associative array (using column names).	▶ View Code
`mysqli_fetch_field()`	Returns information about the next column (field) in the result set as an object.	▶ View Code
`mysqli_fetch_field_direct()`	Returns metadata for a single specific column based on its field index.	▶ View Code
`mysqli_fetch_fields()`	Returns an array of objects containing detailed data about all columns in the result.	▶ View Code
`mysqli_fetch_lengths()`	Returns the lengths (character count) for each column in the current row of the result.	▶ View Code
`mysqli_fetch_object()`	Returns the current row of a result set as an object with properties.	▶ View Code
`mysqli_fetch_row()`	Fetches a result row accessible via numerical indexes (starting from 0).	▶ View Code
`mysqli_field_count()`	Returns the total number of columns fetched by the last executed query.	▶ View Code
`mysqli_field_seek()`	Moves the column pointer to a specified offset (useful for custom field reading).	▶ View Code
`mysqli_free_result()`	Frees up the allocated system memory associated with a result set.	▶ View Code
`mysqli_get_charset()`	Returns a complete object containing information about the current character set.	▶ View Code
`mysqli_get_client_info()`	Returns the MySQL client library version as a text string.	▶ View Code
`mysqli_get_client_stats()`	Returns detailed statistics about client processes and data transmission.	▶ View Code
`mysqli_get_client_version()`	Returns the MySQL client library version as an integer (e.g., 80212).	▶ View Code
`mysqli_get_connection_stats()`	Returns statistical data about the open client connection to the server.	▶ View Code
`mysqli_get_host_info()`	Returns the server hostname and the connection type used (e.g., TCP/IP).	▶ View Code
`mysqli_get_proto_info()`	Returns the version of the MySQL protocol used.	▶ View Code
`mysqli_get_server_info()`	Returns the version of the MySQL server we are connected to.	▶ View Code
`mysqli_get_server_version()`	Returns the MySQL server version as an integer.	▶ View Code
`mysqli_get_warnings()`	Retrieves warnings for the last operation performed on the database.	▶ View Code
`mysqli_info()`	Returns additional textual information about the last executed query.	▶ View Code
`mysqli_init()`	Initializes a MySQLi object for use with the advanced `mysqli_real_connect()` function.	▶ View Code
`mysqli_insert_id()`	Returns the automatically generated ID (AUTO_INCREMENT) from the last INSERT query.	▶ View Code
`mysqli_kill()`	Asks the server to immediately terminate a specific MySQL connection thread.	▶ View Code
`mysqli_more_results()`	Checks if any more result sets are available from multiple sequential queries.	▶ View Code
`mysqli_multi_query()`	Executes one or more concatenated SQL queries at once, separated by semicolons.	▶ View Code
`mysqli_next_result()`	Prepares and loads the next result set from a multi-query sequence initialized by `multi_query()`.	▶ View Code
`mysqli_num_rows()`	Returns the total number of rows in a result set (applicable only for SELECT).	▶ View Code
`mysqli_options()`	Sets additional, advanced options and parameters for managing connection behavior.	▶ View Code
`mysqli_ping()`	Pings an open connection or attempts to automatically reconnect if it dropped.	▶ View Code
`mysqli_poll()`	Polls asynchronous queries and detects their current execution status.	▶ View Code
`mysqli_prepare()`	Prepares a secure SQL statement (Prepared Statement) for future execution using parameter bindings.	▶ View Code
`mysqli_query()`	Executes a single SQL query against the database. The most frequently used function.	▶ View Code
`mysqli_real_connect()`	Establishes an advanced and secure connection to a MySQL server after initialization with `mysqli_init()`.	▶ View Code
`mysqli_real_escape_string()`	Secures a string from special characters (SQL Injection) before using it inside a query.	▶ View Code
`mysqli_real_query()`	Executes a raw SQL query without automatically buffering and storing the result rows in memory.	▶ View Code
`mysqli_reap_async_query()`	Retrieves and returns the final result from an asynchronous SQL query execution.	▶ View Code
`mysqli_rollback()`	Rolls back (reverts) all modifications made during the current transaction and restores the initial state.	▶ View Code
`mysqli_select_db()`	Changes the active (default) database database for all subsequent queries.	▶ View Code
`mysqli_set_charset()`	Sets the default client character set (e.g., required for proper diacritics: `utf8mb4`).	▶ View Code
`mysqli_set_local_infile_handler()`	Sets a custom callback function to handle execution of the `LOAD DATA LOCAL INFILE` command.	▶ View Code
`mysqli_sqlstate()`	Returns the standard five-character SQLSTATE error code for the last operation.	▶ View Code
`mysqli_ssl_set()`	Sets certificate paths to establish an encrypted and secure SSL connection.	▶ View Code
`mysqli_stat()`	Returns the complete current status of the MySQL server as a text string.	▶ View Code
`mysqli_stmt_init()`	Initializes and returns an empty statement object, ready for `mysqli_stmt_prepare()`.	▶ View Code
`mysqli_stmt_store_result()`	Transfers the result set from a prepared statement into local memory buffer.	▶ View Code
`mysqli_store_result()`	Transfers and stores the entire result set of the last query into the client's local memory.	▶ View Code
`mysqli_thread_id()`	Returns the unique identification number (thread ID) for the currently open connection.	▶ View Code
`mysqli_thread_safe()`	Checks and returns whether the installed MySQLi library is compiled as thread-safe.	▶ View Code
`mysqli_use_result()`	Initiates reading the result set directly from the server row-by-row (without storing everything in RAM).	▶ View Code
`mysqli_warning_count()`	Returns the total number of warnings that were triggered during the execution of the last query.	▶ View Code