Introduction to MySQLi
The MySQLi (MySQL Improved) extension in PHP provides a robust and flexible way to interact with MySQL databases. It offers both procedural and object-oriented interfaces, but using the object-oriented interface (OOP) has several advantages in terms of code organization, reusability, and maintainability. This article introduces the MySQLi class in OOP, highlighting its features and demonstrating its usage with practical examples.Why Use MySQLi OOP?
- Enhanced Security: MySQLi supports prepared statements, which help prevent SQL injection attacks.
- Better Performance: Prepared statements can be executed multiple times, reducing the overhead of parsing and compiling SQL queries.
- Modern Code Practices: OOP promotes better code organization and reusability, making your applications easier to maintain and extend.
- Comprehensive Feature Set: MySQLi offers a wide range of functionalities, including transactions, multi-query support, and the ability to retrieve metadata.
Setting Up the Environment
Before diving into examples, ensure you have a MySQL database set up. For this article, we’ll use a sample database named example_db with a users table.
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
age INT NOT NULL,
email VARCHAR(100) NOT NULL
);
INSERT INTO users (username, age, email) VALUES
('john_doe', 30, 'john@example.com'),
('jane_doe', 25, 'jane@example.com'),
('alice', 28, 'alice@example.com'),
('bob', 35, 'bob@example.com');
Connecting to the Database
To start using MySQLi in OOP, you need to create an instance of the MySQLi class and establish a connection to the database.
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "example_db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Performing Basic CRUD Operations
Creating Records
To insert a new record into the users table, you can use prepared statements.
<?php
// Assume $conn is already created and connected
// Prepare an insert statement
$stmt = $conn->prepare("INSERT INTO users (username, age, email) VALUES (?, ?, ?)");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$username = "charlie";
$age = 22;
$email = "charlie@example.com";
$stmt->bind_param("sis", $username, $age, $email); // "sis" denotes string, integer, string
// Execute the statement
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Execute failed: " . $stmt->error;
}
// Close the statement
$stmt->close();
?>
Reading Records
To retrieve data from the users table, you can use a prepared statement and bind the result variables.
<?php
// Assume $conn is already created and connected
// Prepare a select statement
$stmt = $conn->prepare("SELECT id, username, age, email FROM users WHERE age > ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$min_age = 25;
$stmt->bind_param("i", $min_age); // "i" denotes integer
// Execute the statement
$stmt->execute();
// Bind the result variables
$stmt->bind_result($id, $username, $age, $email);
// Fetch the results
while ($stmt->fetch()) {
echo "ID: $id, Username: $username, Age: $age, Email: $email\n";
}
// Close the statement
$stmt->close();
?>
Updating Records
To update existing records, use a prepared statement to set the new values based on a condition.
<?php
// Assume $conn is already created and connected
// Prepare an update statement
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE username = ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$new_email = "jane.doe@example.com";
$username = "jane_doe";
$stmt->bind_param("ss", $new_email, $username); // "ss" denotes two strings
// Execute the statement
if ($stmt->execute()) {
echo "Record updated successfully";
} else {
echo "Execute failed: " . $stmt->error;
}
// Close the statement
$stmt->close();
?>
Deleting Records
To delete a record from the users table, use a prepared statement to specify which record to delete.
<?php
// Assume $conn is already created and connected
// Prepare a delete statement
$stmt = $conn->prepare("DELETE FROM users WHERE username = ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$username = "charlie";
$stmt->bind_param("s", $username); // "s" denotes string
// Execute the statement
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Execute failed: " . $stmt->error;
}
// Close the statement
$stmt->close();
?>
Advanced MySQLi OOP Features
Using Transactions
Transactions allow you to execute a series of SQL statements as a single unit of work. This is useful for maintaining data integrity.
<?php
// Assume $conn is already created and connected
// Start a transaction
$conn->begin_transaction();
try {
// Execute multiple queries
$conn->query("UPDATE users SET age = age + 1 WHERE username = 'john_doe'");
$conn->query("UPDATE users SET age = age - 1 WHERE username = 'jane_doe'");
// Commit the transaction
$conn->commit();
echo "Transaction completed successfully";
} catch (Exception $e) {
// An error occurred, rollback the transaction
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
?>
Handling Errors and Exceptions
To handle errors effectively, use exceptions to catch and respond to database-related issues.
<?php
// Enable exception mode
$conn->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
try {
// Assume $conn is already created and connected
// Prepare and execute a query
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$username = "john_doe";
$stmt->execute();
// Fetch the results
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, Username: {$row['username']}, Age: {$row['age']}, Email: {$row['email']}\n";
}
// Close the statement
$stmt->close();
} catch (mysqli_sql_exception $e) {
echo "Error: " . $e->getMessage();
}
// Close the connection
$conn->close();
?>
Using MySQLi with Object-Oriented PHP
In a larger application, you might encapsulate database operations within a class to promote reusability and maintainability.
<?php
class Database {
private $conn;
public function __construct($servername, $username, $password, $dbname) {
$this->conn = new mysqli($servername, $username, $password, $dbname);
if ($this->conn->connect_error) {
die("Connection failed: " . $this->conn->connect_error);
}
}
public function query($sql, $params = []) {
$stmt = $this->conn->prepare($sql);
if ($stmt === false) {
throw new Exception("Prepare failed: " . $this->conn->error);
}
if ($params) {
$stmt->bind_param(...$params);
}
if (!$stmt->execute()) {
throw new Exception("Execute failed: " . $stmt->error);
}
return $stmt->get_result();
}
public function close() {
$this->conn->close();
}
}
// Usage example
try {
$db = new Database("localhost", "root", "password", "example_db");
$result = $db->query("SELECT * FROM users WHERE age > ?", ["i", 25]);
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, Username: {$row['username']}, Age: {$row['age']}, Email: {$row['email']}\n";
}
$db->close();
} catch (Exception $e) {
echo "Error: " . $e->getMessage();
}
?>
Conclusion
Using the MySQLi class in object-oriented PHP offers numerous benefits, including improved security, better performance, and more maintainable code. By encapsulating database operations within classes and using prepared statements, you can create robust and scalable applications. This article covered the basics of connecting to a MySQL database, performing CRUD operations, and handling advanced features like transactions and error handling. With these techniques, you are well-equipped to harness the full power of MySQLi in your PHP projects.