PHP MySQLi — Complete Guide to MySQL Database Access
In this tutorial, you will learn about PHP MySQLi. We cover key concepts, practical examples, and best practices to help you master this topic.
PHP MySQLi (MySQL Improved) provides a native interface for connecting to and interacting with MySQL databases, supporting prepared statements, transactions, and both procedural and OOP APIs.
What You'll Learn
By the end of this tutorial, you'll connect to MySQL, execute queries with prepared statements, perform CRUD operations, use transactions, handle errors, and prevent SQL Injection.
Why MySQLi Matters
Databases store application data persistently. MySQLi is PHP's dedicated MySQL extension, providing secure access through prepared statements and efficient data retrieval for dynamic applications.
Real-World Use
A user registration system uses MySQLi prepared statements to insert new users, query login credentials, and update profiles. Prepared statements prevent SQL injection from user input.
MySQLi Learning Path
flowchart LR
A[DI] --> B[MySQLi]
B --> C[PDO]
C --> D[MVC]
D --> E[REST API]
A --> F{You Are Here}
style F fill:#f90,color:#fff
Connecting to MySQL
<?php
$host = "localhost";
$user = "root";
$pass = "";
$dbname = "myapp";
$mysqli = new mysqli($host, $user, $pass, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8mb4");
Prepared Statements
<?php
$stmt = $mysqli->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $name, $email, $age);
$name = "Alice";
$email = "alice@example.com";
$age = 25;
$stmt->execute();
echo "Inserted ID: " . $stmt->insert_id;
$stmt->close();
SELECT Queries
<?php
$stmt = $mysqli->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$userId = 1;
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc(); // Single row
// Multiple rows
while ($row = $result->fetch_assoc()) {
echo $row["name"] . "\n";
}
$stmt->close();
UPDATE and DELETE
<?php
$stmt = $mysqli->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bind_param("si", $newEmail, $userId);
$newEmail = "newemail@example.com";
$userId = 1;
$stmt->execute();
echo "Updated rows: " . $stmt->affected_rows;
$stmt->close();
// DELETE
$stmt = $mysqli->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
Transactions
<?php
$mysqli->begin_transaction();
try {
$stmt1 = $mysqli->prepare("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$stmt1->execute();
$stmt2 = $mysqli->prepare("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$stmt2->execute();
$mysqli->commit();
echo "Transfer complete";
} catch (Exception $e) {
$mysqli->rollback();
echo "Transfer failed: " . $e->getMessage();
}
Common Mistakes
1. Not Using Prepared Statements
Concatenating user input into SQL strings enables SQL injection. Always use prepared statements.
2. Forgetting to Close Statements
Unclosed statements waste memory. Always call $stmt->close() when done.
3. Not Checking for Errors
SQL errors (duplicate entry, constraint violation) go unnoticed without checking. Use $stmt->error after execute.
4. Using mysql Extension
The old mysql extension is deprecated and removed. Only use mysqli or PDO for new projects.
5. Not Escaping LIKE Wildcards
User input in LIKE clauses needs escaped % and _ characters. Use addcslashes() or manual escaping.
Practice Questions
1. What is the difference between mysqli and PDO?
mysqli is MySQL-specific. PDO supports multiple databases (MySQL, PostgreSQL, SQLite). Both support prepared statements.
2. Why use prepared statements?
They separate SQL logic from data, preventing SQL injection and improving performance for repeated queries.
3. What does bind_param("ssi") mean?
"ssi" specifies the types of bound parameters: s = string, s = string, i = integer. Other types: d (double), b (blob).
4. How do you get the last inserted ID?
$stmt->insert_id or $mysqli->insert_id after executing an INSERT query.
5. Challenge: Create a user registration function using prepared statements.
<?php
function registerUser(mysqli $mysqli, string $name, string $email, string $password): bool {
$hash = password_hash($password, PASSWORD_DEFAULT);
$stmt = $mysqli->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $hash);
$result = $stmt->execute();
$stmt->close();
return $result;
}
FAQ
Mini Project: User Repository
Build a complete user repository with CRUD operations using MySQLi.
<?php
class UserRepository {
private mysqli $db;
public function __construct(mysqli $db) { $this->db = $db; }
public function create(string $name, string $email): int {
$stmt = $this->db->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();
$id = $stmt->insert_id;
$stmt->close();
return $id;
}
public function find(int $id): ?array {
$stmt = $this->db->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result()->fetch_assoc();
$stmt->close();
return $result ?: null;
}
public function delete(int $id): bool {
$stmt = $this->db->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$result = $stmt->execute();
$stmt->close();
return $result;
}
}
What's Next
PHP PDO PHP MVC Pattern PHP REST API
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro