PHP MySQLi

Beginner
12 min read
Updated: Nov 2026
3 examples
Connect, query & fetch

Definition and Usage

PHP MySQLi is how beginners talk to a MySQL or MariaDB database from PHP. You open a link, set the character set, run SQL, read each row, check for errors, and close the connection when you are finished.

Connect with mysqli_connect() or new mysqli(...), then call mysqli_set_charset() (usually utf8mb4). Run fixed SQL with mysqli_query(), or use mysqli_prepare() when values come from the user. Loop rows with mysqli_fetch_assoc().

Always check mysqli_connect_error() or mysqli_connect_errno() after connect, and mysqli_errno() / mysqli_error() after queries. Finish with mysqli_close().

This page explains the ideas. The MySQLi Functions index lists all 77 function guides.

Set the charset early.

Call mysqli_set_charset($link, 'utf8mb4') right after a successful connect so text and escaping match the database.

Aliases and newer APIs.

mysqli_escape_string() is an alias of mysqli_real_escape_string(); mysqli_set_opt() is an alias of mysqli_options(). Prefer prepared statements over escaping when you can. PHP 8.2 adds mysqli_execute_query(); PHP 8.1 adds mysqli_fetch_column().

PHP Syntax

The usual pattern: connect, set charset, query or prepare, fetch rows, check errors, then close:

PHP
// Connect (procedural)
$link = mysqli_connect('127.0.0.1', 'user', 'pass', 'app_db');
if (mysqli_connect_errno()) {
    die('Connect failed: ' . mysqli_connect_error());
}
mysqli_set_charset($link, 'utf8mb4');

// Simple query + fetch loop
$result = mysqli_query($link, 'SELECT id, name FROM users LIMIT 10');
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['id'], ' ', $row['name'], PHP_EOL;
}
mysqli_free_result($result);

// Prepared statement (user input)
$stmt = mysqli_prepare($link, 'SELECT id, name FROM users WHERE id = ?');
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);

// Errors on the link
$errno = mysqli_errno($link);               // int
$err   = mysqli_error($link);               // string

mysqli_close($link);

Core Ideas

What to remember when you work with PHP MySQLi:

IdeaFunctionRemember
Open a linkmysqli_connect()Returns a link (or false); also available as new mysqli(...)
Connect failed?mysqli_connect_error()String message; pair with mysqli_connect_errno() for the number
Charsetmysqli_set_charset()Call after connect; prefer utf8mb4 for full Unicode
Run SQLmysqli_query()Good for fixed SQL; returns a result object, true/false for non-SELECT
Safe valuesmysqli_prepare()Use placeholders (?) for user input; bind, then execute
One call preparemysqli_execute_query()PHP 8.2+: prepare + bind + execute in one step; returns a result
Read a rowmysqli_fetch_assoc()Associative array by column name; loop until it returns null
Query errormysqli_errno() / mysqli_error()Check after a failed query; do not show raw SQL errors to end users
Closemysqli_close()Free the connection when the script is done with the database
Escape (legacy)mysqli_real_escape_string()Prefer prepare(); mysqli_escape_string() is the same call

Object style in short

With $mysqli = new mysqli(...) you write $mysqli->set_charset('utf8mb4'), $mysqli->query($sql), $result->fetch_assoc(), and $mysqli->close(). The ideas match the procedural names on this page.

Function Groups at a Glance

The MySQLi section is one family with several jobs. Use this map to pick the right group before you open a function page:

GroupTypical jobStart here
Connect & optionsOpen a link, set options/SSL/charset, switch DB, ping, closemysqli_connect(), mysqli_set_charset()
Errors & infoConnect failures, query errors, SQLSTATE, warnings, server infomysqli_connect_error(), mysqli_errno()
Query & resultsquery, prepare, multi-query, store/use result, affected rowsmysqli_query(), mysqli_prepare()
FetchRead rows as assoc, array, object, or one columnmysqli_fetch_assoc()
TransactionsBegin, commit, rollback, savepointsmysqli_begin_transaction(), mysqli_commit()
Escape & charsetEscape strings (legacy) and inspect the connection charsetmysqli_real_escape_string()

Neighboring tutorials that often sit next to this work: Network for headers and sockets, Variables for types and casting, JSON for API payloads beside database rows, and Strings for text you store or display.

PHP Examples

Three short scripts that show the core MySQLi ideas. Sample outputs below match a typical local setup so you can study the flow without a live database. Each block includes a fixed View Output panel that matches this tutorial layout.

Example 1 — Connect, charset, and errors

Open a link, check connect_errno / connect_error, set utf8mb4, then close.

PHP
<?php
$host = '127.0.0.1';
$user = 'app';
$pass = 'secret';
$db   = 'demo';

$link = mysqli_connect($host, $user, $pass, $db);

if (mysqli_connect_errno()) {
    echo 'connect_errno: ', mysqli_connect_errno(), PHP_EOL;
    echo 'connect_error: ', mysqli_connect_error(), PHP_EOL;
    exit(1);
}

echo 'connected: yes', PHP_EOL;

if (!mysqli_set_charset($link, 'utf8mb4')) {
    echo 'charset error: ', mysqli_error($link), PHP_EOL;
    mysqli_close($link);
    exit(1);
}

echo 'charset: ', mysqli_character_set_name($link), PHP_EOL;
echo 'host info: ', mysqli_get_host_info($link), PHP_EOL;

mysqli_close($link);
echo 'closed: yes', PHP_EOL;
?>

How It Works

  1. mysqli_connect() opens a link to the server and selects the database. On failure it returns false.
  2. mysqli_connect_errno() / mysqli_connect_error() report why connect failed. Exit early if either shows a problem.
  3. mysqli_set_charset($link, 'utf8mb4') sets the client charset so text and escaping stay consistent.
  4. mysqli_character_set_name() confirms the active charset; the sample prints utf8mb4.
  5. mysqli_close() ends the session. Always close when the script no longer needs the database.

Example 2 — Query and fetch_assoc loop

Run a SELECT, loop each row as an associative array, then free the result.

PHP
<?php
$link = mysqli_connect('127.0.0.1', 'app', 'secret', 'demo');
if (mysqli_connect_errno()) {
    die(mysqli_connect_error());
}
mysqli_set_charset($link, 'utf8mb4');

$sql = 'SELECT id, name FROM users ORDER BY id ASC LIMIT 3';
$result = mysqli_query($link, $sql);

if ($result === false) {
    echo 'errno: ', mysqli_errno($link), PHP_EOL;
    echo 'error: ', mysqli_error($link), PHP_EOL;
    mysqli_close($link);
    exit(1);
}

echo 'num_rows: ', mysqli_num_rows($result), PHP_EOL;

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['id'], ' | ', $row['name'], PHP_EOL;
}

mysqli_free_result($result);
mysqli_close($link);
?>

How It Works

  1. mysqli_query() sends the SQL. For a SELECT it returns a result object, or false on failure.
  2. If the result is false, read mysqli_errno() and mysqli_error() before you continue.
  3. mysqli_num_rows() counts rows already buffered in the result (works after a normal query()).
  4. mysqli_fetch_assoc() returns one row as an associative array, then the next, until it returns null.
  5. mysqli_free_result() releases the result memory; then mysqli_close() closes the link.

Example 3 — query vs prepare

Compare a fixed query() with a prepared statement that binds a user id safely.

PHP
<?php
$link = mysqli_connect('127.0.0.1', 'app', 'secret', 'demo');
if (mysqli_connect_errno()) {
    die(mysqli_connect_error());
}
mysqli_set_charset($link, 'utf8mb4');

// Fixed SQL — no user values: query() is fine
$fixed = mysqli_query($link, 'SELECT COUNT(*) AS total FROM users');
$row = mysqli_fetch_assoc($fixed);
echo 'total users: ', $row['total'], PHP_EOL;
mysqli_free_result($fixed);

// User input — use prepare() + bind
$id = 2; // e.g. from $_GET after validation
$stmt = mysqli_prepare($link, 'SELECT id, name FROM users WHERE id = ?');
mysqli_stmt_bind_param($stmt, 'i', $id);
mysqli_stmt_execute($stmt);
$res = mysqli_stmt_get_result($stmt);
$one = mysqli_fetch_assoc($res);

echo 'found: ', $one ? $one['id'] . ' | ' . $one['name'] : 'none', PHP_EOL;

mysqli_stmt_close($stmt);
mysqli_close($link);
?>

How It Works

  1. The first block uses mysqli_query() for SQL with no user values—a simple count is enough.
  2. mysqli_prepare() compiles SQL with a ? placeholder so values are not glued into the string.
  3. mysqli_stmt_bind_param() attaches the integer $id as type i, then mysqli_stmt_execute() runs it.
  4. mysqli_stmt_get_result() turns the statement into a result you can fetch with mysqli_fetch_assoc().
  5. Prefer prepare for anything that came from a form, URL, or API. In PHP 8.2 you can also try mysqli_execute_query() for a shorter path.

Next: PHP MySQLi Functions Index

After you know connect, charset, query vs prepare, fetch_assoc, and close, open the functions index for all 77 guides.

Start withWhy
mysqli_connect()Open a connection to MySQL or MariaDB
mysqli_set_charset()Set utf8mb4 right after a successful connect
mysqli_query()Run a simple SQL statement
mysqli_prepare()Use placeholders for user-supplied values
mysqli_fetch_assoc()Read each result row as an associative array
mysqli_connect_error()Read the connect failure message
mysqli_close()Close the connection when you are done

Full list: PHP MySQLi Functions index (/php/mysqli/function).

Frequently Asked Questions

MySQLi (MySQL Improved) is PHP's extension for talking to a MySQL or MariaDB server. You open a connection, set a character set, run SQL with query() or prepare(), read rows with fetch helpers, check errors with connect_error or errno, and close when you are done.
Start with mysqli_connect() (or new mysqli()), then mysqli_set_charset(), then a simple mysqli_query() plus mysqli_fetch_assoc() loop. After that, learn mysqli_prepare() for user input, and always check mysqli_connect_error() or mysqli_errno() before you trust a result.
mysqli_query() sends a finished SQL string. mysqli_prepare() builds a statement with placeholders (?), then you bind values and execute. Prefer prepare() whenever values come from the user—it helps avoid SQL injection. For fixed SQL with no user data, query() is fine.
After connect, check mysqli_connect_error() (string) or mysqli_connect_errno() (number). On an open link, mysqli_error() and mysqli_errno() report the last query error. Stop and show a safe message if connect failed—do not run queries on a bad link.
mysqli_execute_query() (PHP 8.2) prepares, binds, and executes in one call and returns a result. mysqli_fetch_column() (PHP 8.1) returns one column from the next row. Confirm function_exists() if you support older PHP.
Both call the same extension. Procedural names look like mysqli_query($link, $sql). Object style uses $mysqli->query($sql). This tutorial shows both patterns; pick one style and stay consistent in a project.

Browse every MySQLi function

Open the functions index for categorized guides with examples and FAQs.

MySQLi Functions Index →

About the author

Mari Selvan M P
Mari Selvan M P 🔗

Developer, cloud engineer, and technical writer

  • Experience 12 years building web and cloud systems
  • Focus Full Stack Development, AWS, and Developer Education

I write practical tutorials so students and working developers can learn by doing—from databases and APIs to deployment on AWS.

9 people found this page helpful