PHP MySQLi Basics

MySQLi (“MySQL Improved”) is the extension PHP uses to talk directly to a MySQL or MariaDB database server. It lets you open a connection, send SQL statements, and read back the results as PHP arrays or objects. It matters because almost every dynamic PHP application — blogs, shopping carts, login systems — needs to store and retrieve data, and MySQLi (alongside PDO) is one of the two standard ways to do that safely and efficiently.

Overview: How MySQLi Works

MySQLi is a PHP extension, meaning it is compiled C code exposed to your scripts as classes and functions. Under the hood it uses either the native mysqlnd (MySQL Native Driver) or the older libmysqlclient library to speak MySQL’s binary network protocol over a TCP or Unix socket connection. When you connect, PHP opens a socket to the database server, performs an authentication handshake (username, password hash, database name), and receives back a connection handle that your script uses for every subsequent operation.

MySQLi offers two parallel APIs for everything it does:

  • Procedural style — functions like mysqli_connect(), mysqli_query(), where the connection is passed as the first argument.
  • Object-oriented style — a mysqli class whose methods you call on a connection object, e.g. $mysqli->query().

Both styles wrap the exact same underlying C functions, so there is no performance difference — it is purely a matter of code style. Modern PHP code almost always uses the OOP style because it composes better with prepared statements, exceptions, and typed properties. This lesson uses the OOP style throughout.

A key internal detail: MySQL query results can be buffered (the entire result set is copied into PHP’s memory immediately) or unbuffered (rows are streamed from the server one at a time as you fetch them). By default, mysqlnd buffers results, which is simpler and safer for typical web requests but uses more memory for very large result sets.

Syntax

The general shape of a MySQLi interaction is: connect, prepare a statement, bind parameters, execute, fetch, close.

<?php
$mysqli = new mysqli($host, $username, $password, $database);
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
Part Meaning
new mysqli(...) Opens a connection; constructor args are host, username, password, database name, and optionally port.
$sql An SQL string with ? placeholders instead of literal values.
prepare() Sends the statement template to the server and returns a mysqli_stmt object.
bind_param($types, ...) Attaches PHP variables to the placeholders. $types is a string of one letter per parameter: i integer, d double, s string, b blob.
execute() Runs the statement on the server with the bound values substituted safely.
get_result() Returns a mysqli_result object you can loop over with fetch_assoc(), fetch_object(), etc.

Examples

Example 1: Connecting to MySQL

<?php
$mysqli = new mysqli('localhost', 'app_user', 'secret_password', 'store_db');

if ($mysqli->connect_errno) {
    die('Connection failed: ' . $mysqli->connect_error);
}

echo 'Connected. Server version: ' . $mysqli->server_info . PHP_EOL;

$mysqli->close();

Output:

Connected. Server version: 8.0.34

The new mysqli(...) call attempts the connection immediately. If it fails, connect_errno is a non-zero error code and connect_error holds a human-readable message. The server_info property reports the MySQL/MariaDB server’s version string once the handshake succeeds. Always call close() when you are done, though PHP will also close the connection automatically at the end of the script.

Example 2: Inserting Data with a Prepared Statement

<?php
$mysqli = new mysqli('localhost', 'app_user', 'secret_password', 'store_db');
$mysqli->set_charset('utf8mb4');

$name = 'Wireless Mouse';
$price = 24.99;
$sku = 'WM-1001';

$stmt = $mysqli->prepare('INSERT INTO products (name, price, sku) VALUES (?, ?, ?)');
$stmt->bind_param('sds', $name, $price, $sku);
$stmt->execute();

echo 'Inserted product with ID ' . $stmt->insert_id . PHP_EOL;

$stmt->close();
$mysqli->close();

Output:

Inserted product with ID 57

The type string 'sds' tells MySQLi the first and third parameters are strings and the second is a double, matching $name, $price, $sku in order. After a successful INSERT into a table with an auto-increment primary key, $stmt->insert_id (or equivalently $mysqli->insert_id) gives you the newly generated ID. Calling set_charset('utf8mb4') right after connecting avoids garbled text and is considered a security best practice, discussed below.

Example 3: Querying Data and Looping Over Results

<?php
$mysqli = new mysqli('localhost', 'app_user', 'secret_password', 'store_db');

$minPrice = 20.00;

$stmt = $mysqli->prepare('SELECT id, name, price FROM products WHERE price >= ? ORDER BY price ASC');
$stmt->bind_param('d', $minPrice);
$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "#{$row['id']}: {$row['name']} - \${$row['price']}" . PHP_EOL;
}

$stmt->close();
$mysqli->close();

Output:

#4: Wireless Mouse - $24.99
#7: USB-C Hub - $29.5
#12: Mechanical Keyboard - $89.99

Here get_result() converts the prepared statement’s result into a familiar mysqli_result object. fetch_assoc() returns one row at a time as an associative array keyed by column name, or null when there are no more rows — which is exactly the condition the while loop checks. This pattern (prepare, bind, execute, fetch in a loop) is the workhorse of almost every read operation in MySQLi.

Under the Hood: What Happens When You Run a Query

  • 1. Connect & authenticate. new mysqli(...) opens a socket to the server and exchanges an authentication handshake using the driver’s negotiated auth plugin (commonly caching_sha2_password on modern MySQL).
  • 2. Prepare. prepare() sends the SQL template, with ? placeholders, to the server. MySQL parses and plans the statement once and returns a statement handle — the placeholders are never substituted client-side as text.
  • 3. Bind. bind_param() stores references to your PHP variables along with their declared types inside the mysqli_stmt object. Nothing is sent to the server yet.
  • 4. Execute. execute() serializes the current values of the bound variables into MySQL’s binary protocol and sends them separately from the SQL text. Because the values are transmitted as typed binary data rather than interpolated into a string, there is no way for them to be interpreted as SQL syntax — this is what makes prepared statements immune to SQL injection.
  • 5. Fetch. The server streams the result set back; with buffered results (the mysqlnd default) the whole set is pulled into PHP memory by get_result(), and each call to fetch_assoc() simply advances an internal pointer.
  • 6. Close. $stmt->close() frees the statement handle on the server; $mysqli->close() tears down the socket. If you never call these explicitly, PHP frees them when the objects go out of scope or the script ends.

Common Mistakes

Mistake 1: Building queries with string concatenation (SQL injection)

Interpolating user input directly into a query string lets an attacker inject arbitrary SQL by crafting the input value.

<?php
$mysqli = new mysqli('localhost', 'app_user', 'secret_password', 'store_db');

$username = $_GET['username'];

$result = $mysqli->query("SELECT * FROM users WHERE username = '$username'");

while ($row = $result->fetch_assoc()) {
    echo $row['username'] . PHP_EOL;
}

If $_GET['username'] is set to something like ' OR '1'='1, the resulting query returns every row in the table instead of one user. Always use a prepared statement instead:

<?php
$mysqli = new mysqli('localhost', 'app_user', 'secret_password', 'store_db');

$username = $_GET['username'];

$stmt = $mysqli->prepare('SELECT * FROM users WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['username'] . PHP_EOL;
}

$stmt->close();

Mistake 2: Not checking for errors

If a query fails, query() returns false rather than throwing by default in older configurations. Calling a method on that false value produces a confusing fatal error far from the real cause.

<?php
$mysqli = new mysqli('localhost', 'app_user', 'wrong_password', 'store_db');

$result = $mysqli->query('SELECT * FROM products');

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . PHP_EOL;
}

Here a bad password means $mysqli never really connects, so query() returns false, and $result->fetch_assoc() fatally errors with “Call to a member function fetch_assoc() on bool”. Turn on exception-based reporting instead, so failures surface immediately with a clear message:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli('localhost', 'app_user', 'secret_password', 'store_db');
    $result = $mysqli->query('SELECT * FROM products');

    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . PHP_EOL;
    }
} catch (mysqli_sql_exception $e) {
    echo 'Database error: ' . $e->getMessage() . PHP_EOL;
}

As of PHP 8.1, MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT is actually the default reporting mode, so MySQLi errors already throw a mysqli_sql_exception automatically — calling mysqli_report() explicitly, as shown above, makes that behavior clear to readers and keeps it working the same way even in code running on older PHP versions.

Best Practices

  • Always use prepared statements with bind_param() for any query that includes user-supplied or variable data — never build SQL with string concatenation.
  • Call $mysqli->set_charset('utf8mb4') immediately after connecting to avoid encoding mismatches and a legacy character-set injection vector.
  • Let MySQLi throw exceptions (the PHP 8.1+ default, or set explicitly via mysqli_report()) instead of manually checking every return value for false.
  • Close statements with $stmt->close() and the connection with $mysqli->close() once you are done, especially in long-running scripts or loops that open many statements.
  • Never suppress connection or query errors with the @ operator — it hides exactly the information you need to debug a failure.
  • Store database credentials outside your source code (environment variables or a config file excluded from version control), and connect with a database user that has the minimum privileges the application needs.
  • Pick one API style, procedural or OOP, and use it consistently across your codebase; mixing them in the same file hurts readability.

Practice Exercises

  • Exercise 1: Write a script that connects to a database named library and inserts a new row into a books table with columns title, author, and year, using a prepared statement. Print the new row’s auto-increment ID.
  • Exercise 2: Write a script that accepts a $_GET['author'] value and safely selects every book by that author, ordered by year descending, printing each title and year on its own line.
  • Exercise 3: Modify Exercise 2 so that if the connection fails, the script catches the resulting mysqli_sql_exception and prints a friendly error message instead of letting PHP display a raw fatal error.

Summary

  • MySQLi is PHP’s extension for talking to MySQL/MariaDB, available in both procedural and object-oriented styles.
  • A connection is created with new mysqli(host, user, password, database) and should be checked for connect_errno.
  • Prepared statements (prepare(), bind_param(), execute()) send SQL and data separately, which is what prevents SQL injection.
  • Use get_result() plus fetch_assoc() in a while loop to read rows back as associative arrays.
  • Enable exception-based error reporting (default since PHP 8.1) so failures are loud and easy to debug rather than silent.
  • Always set the connection charset, close statements and connections when done, and never concatenate untrusted input into SQL strings.