PHP MySQLi: Interpolated Queries Exposed 50k Records

Every meaningful web application stores data — user accounts, orders, blog posts, settings. PHP can do a lot on its own, but the moment you need to persist or retrieve that data, you need a database layer. MySQL is the world's most popular open-source database, and MySQLi (MySQL Improved) is PHP's built-in extension for talking to it. It's not an optional nice-to-have — it's the backbone of practically every PHP-powered site you've ever visited.

The original mysql_* functions were deprecated in PHP 5.5 and removed entirely in PHP 7.0. MySQLi replaced them with a more powerful API that supports prepared statements, multiple statements, transactions, and both procedural and object-oriented styles. The single biggest reason it exists is security: the old API made SQL injection trivially easy. MySQLi's prepared statements make it structurally hard to write vulnerable code.

By the end of this article you'll know how to open a database connection the right way, run SELECT and INSERT queries safely using prepared statements, handle errors without leaking sensitive info to users, and fetch results in whatever format your app needs. You'll also understand the traps that catch intermediate developers — including the one that silently corrupts your data.

What is PHP with MySQL — MySQLi?

MySQLi is the 'MySQL improved' extension, offering a modern interface with support for prepared statements, transactions, and multiple statement handling. It replaced the ancient mysql_* functions that were insecure and deprecated. MySQLi can be used in two styles: procedural (like the old extension but improved) and object-oriented (which groups all functions into a class). In production, the OO style is preferred for its cleaner syntax and IDE autocompletion. It's not just a new coat of paint — the prepared statement support is the main event. That's what makes SQL injection structurally impossible.

Here's the thing: many tutorials show you the procedural style because it looks simpler for beginners. But once you start writing real applications, you'll want OOP. You get autocomplete in your IDE, you don't have to pass the connection resource to every function, and the code reads more naturally. The procedural style was a transitional API to help migrate from the old mysql_* functions. Today, there's no reason to start new code in procedural mode.

Always enable error reporting from the start. That single call to mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) will save you hours of debugging. Without it, a failed prepare() returns false and you get a cryptic "Call to member function on boolean" error. With it, you get a proper exception with a clear message.

<?php
namespace io\thecodeforge;

use mysqli;

// Object-oriented connection
$conn = new mysqli('localhost', 'user', 'password', 'test_db');
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

// Prepared statement
$stmt = $conn->prepare('SELECT name FROM users WHERE id = ?');
$stmt->bind_param('i', $userId);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
echo $name;
$stmt->close();
$conn->close();

Connecting to MySQL with MySQLi: The Right Way

Connecting to MySQL looks simple — new mysqli(...) — but the defaults can burn you. By default, MySQLi uses buffered queries (good), connects via TCP (localhost may use socket), and does not throw exceptions on failure. That last one is dangerous.

Always set the connection charset explicitly, especially when dealing with UTF-8 data. A missing charset can corrupt multi-byte characters without any error. Also, decide early whether your app should die on connection failure or handle it gracefully. In production, you probably want to log the error and show a friendly message, not expose the full error to the user.

Here's a production-ready connection pattern using OO style with exception handling. Note the use of try-catch with mysqli_sql_exception. This ensures that every connection issue is caught and logged, while the user sees a generic error page. Never expose database error details to end users — that's a security vulnerability and a gift to attackers.

<?php
namespace io\thecodeforge;

use mysqli;
use mysqli_sql_exception;

// Enable full exception reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli('localhost', 'app_user', 'strong_password', 'myapp_db');
    $conn->set_charset('utf8mb4');
} catch (mysqli_sql_exception $e) {
    error_log('Database connection failed: ' . $e->getMessage());
    http_response_code(500);
    die('Service unavailable. Please try again later.');
}

// Now $conn is safe to use everywhere

Prepared Statements: How They Block SQL Injection

Prepared statements are the single most important feature of MySQLi. Instead of building SQL strings by concatenating user input, you send a template with placeholders (?) and then send the actual values separately. The database server compiles the query template first, then binds the values. Because the values are never parsed as SQL — they're always treated as literal data — injection becomes impossible.

This doesn't just prevent injection; it also eliminates the need to escape strings (no more mysqli_real_escape_string()). And for repeated queries, prepared statements can offer performance gains because the query plan is cached.

Here's how to use them correctly with different data types. The bind_param type specifiers are crucial: 'i' for integer, 'd' for double, 's' for string, 'b' for blob. Get them wrong and you can introduce subtle bugs — for example, using 's' for an integer column forces a string-to-integer cast that may ignore the index and cause a full table scan. Always double-check your types, especially when the data comes from user input.

<?php
namespace io\thecodeforge;

use mysqli;

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = new mysqli('localhost', 'app_user', 'password', 'myapp_db');
$conn->set_charset('utf8mb4');

// Insert with multiple parameters
$stmt = $conn->prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');
$stmt->bind_param('ssi', $name, $email, $age);

$name = 'Alice';
$email = 'alice@example.com';
$age = 30;
$stmt->execute();

echo 'Inserted ID: ' . $stmt->insert_id;
$stmt->close();
$conn->close();

Fetching Results: Choosing the Right Method

MySQLi provides several ways to fetch results from SELECT queries. The most common are fetch_assoc(), fetch_row(), fetch_array(), and fetch_all(). The choice depends on how you plan to use the data and whether you're using buffered or unbuffered mode.

fetch_assoc() returns an associative array — you access columns by name. It's the most readable for most code. fetch_row() returns a numerically indexed array — useful if you know column order. fetch_array() returns both, but that's wasteful in memory. fetch_all() returns all rows at once as an array of arrays — convenient, but can eat memory on large result sets.

For prepared statements, you have two paths: bind_result() (lower level) or get_result() (returns a result set like a regular query). In modern PHP with mysqlnd, get_result() is almost always the better choice. The mysqlnd driver is included by default in PHP since 5.4, but some shared hosting providers still use libmysqlclient. If get_result() is not available, you'll get a fatal error. Check with function_exists('mysqli_get_result').

<?php
namespace io\thecodeforge;

use mysqli;

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = new mysqli('localhost', 'app_user', 'password', 'myapp_db');
$conn->set_charset('utf8mb4');

// Using get_result() with prepared statement
$stmt = $conn->prepare('SELECT id, name FROM users WHERE active = 1');
$stmt->execute();
$result = $stmt->get_result();

// fetch_assoc loop
while ($row = $result->fetch_assoc()) {
    echo $row['id'] . ': ' . $row['name'] . "\n";
}

// fetch_all() for small result set
$result->data_seek(0);
$allRows = $result->fetch_all(MYSQLI_ASSOC);

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

Transactions and Multiple Statements

Transactions ensure that multiple queries are treated as a single unit of work: all succeed or all fail together. MySQLi supports transactions via begin_transaction(), commit(), and rollback(). You can also set autocommit off and manage manually.

Multiple statement execution (multi_query()) allows sending multiple SQL statements in one call. This is useful for batch operations, but dangerous if you're not careful — it's a vector for second-order SQL injection if user input is involved.

Prepared statements with transactions: you can prepare once, execute with different parameters in a loop, then commit — all without re-parsing the SQL. This is a common pattern for bulk inserts. But don't forget to close the statement before the next prepare() call, or you'll get a "Commands out of sync" error.

<?php
namespace io\thecodeforge;

use mysqli_sql_exception;

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = new mysqli('localhost', 'app_user', 'password', 'myapp_db');
$conn->set_charset('utf8mb4');

try {
    $conn->begin_transaction();

    // Deduct from account A
    $stmt = $conn->prepare('UPDATE accounts SET balance = balance - ? WHERE account_id = ?');
    $stmt->bind_param('di', $amount, $fromId);
    $amount = 100.00;
    $fromId = 1;
    $stmt->execute();

    // Add to account B
    $stmt = $conn->prepare('UPDATE accounts SET balance = balance + ? WHERE account_id = ?');
    $stmt->bind_param('di', $amount, $toId);
    $toId = 2;
    $stmt->execute();

    $conn->commit();
} catch (mysqli_sql_exception $e) {
    $conn->rollback();
    error_log('Transfer failed: ' . $e->getMessage());
    die('Transfer failed, please try again.');
}

Best Practices and Common Production Pitfalls

Even with MySQLi, there are several ways your code can fail in production. Here are the most critical:

1. Not using persistent connections incorrectly: mysqli_pconnect() can cause connection pinning issues in some environments. Unless you fully understand the implications, stick to non-persistent connections.
2. Reusing a statement without resetting: After execute(), if you want to use the same prepared statement with new parameters, you must clear the old results with free_result() or close().
3. Ignoring the result of execute(): execute() returns true on success, but if you're on non-exception mode, it can return false without an obvious error. Always check.
4. Using mysqli_real_escape_string() when you should use prepared statements: If you're still escaping strings, you're doing it wrong. Prepared statements handle escaping natively.
5. Hardcoding credentials in PHP files: Store them outside the web root or in environment variables.

Another one that bites people: mixing up fetch_assoc() and fetch_row() orders. If you have a query with JOINs, the column order in the result set depends on the query, not the table order. Always access by column name (associative) rather than numeric index to avoid bugs when you add columns later.

<?php
namespace io\thecodeforge;

use mysqli;
use mysqli_sql_exception;

// Load config from environment - never hardcode
$host = getenv('DB_HOST') ?: 'localhost';
$user = getenv('DB_USER') ?: 'app_user';
$pass = getenv('DB_PASS') ?: '';
$db   = getenv('DB_NAME') ?: 'myapp_db';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = new mysqli($host, $user, $pass, $db);
$conn->set_charset('utf8mb4');

// Single connection singleton helper (not shown) - but avoid global variable

// Close explicitly when done
$conn->close();