English
English
When developing using PHP, especially in the MVC (Model-View-Controller) architecture, how to manage database operations is a key issue. The mysqli extension is a common way to access MySQL databases in PHP, and the mysqli_result function is used to get the result set from a database query. In MVC architecture, if database queries and result sets are processed directly in the controller, it may cause the code to be bloated, difficult to maintain, and even difficult to scale. Therefore, how to elegantly encapsulate the mysqli_result function to improve the maintainability and scalability of the code is a problem that developers need to solve.
This article will introduce how to elegantly encapsulate the use of mysqli_result in MVC mode and give relevant code examples.
In the MVC architecture, the division of responsibilities is very important:
Model : Responsible for interacting with data sources (such as databases), encapsulating data query and update operations.
View : Responsible for data display, usually does not involve database operations.
Controller : receives user input, calls the corresponding model to process data, and passes the result to the view.
In order to keep the code clear and cohesive, we should focus the logic of interacting with the database at the model layer, and the controller only needs to call methods in the model to get the data.
The mysqli_result object is the result set returned by executing the query statement. In order to improve the maintainability and scalability of the code, we should avoid directly manipulating the mysqli_result object in the controller. Reasons include:
Code duplication : When processing query results in multiple places, it is easy to cause duplicate code.
Difficult to test : Direct manipulation of mysqli_result object makes unit testing difficult.
Poor scalability : Directly operating mysqli_result will make it difficult to expand the database driver later (such as switching from MySQL to PostgreSQL).
Therefore, we should encapsulate the operation of the mysqli_result object so that it becomes an independent module that can be reused in different scenarios and can be modified and expanded more easily.
First, we can create a database abstract class that is responsible for handling database connections and query operations. This class will return the encapsulated result set to avoid directly manipulating the mysqli_result object in the controller.
<?php
abstract class Database {
protected $connection;
public function __construct($host, $username, $password, $dbname) {
$this->connection = new mysqli($host, $username, $password, $dbname);
if ($this->connection->connect_error) {
die("Connection failed: " . $this->connection->connect_error);
}
}
abstract public function query($sql);
}
?>
Then, create a specific database class that inherits from the Database class. This class will encapsulate the operations of querying and getting the result set.
<?php
class MySQLDatabase extends Database {
public function query($sql) {
$result = $this->connection->query($sql);
if ($result === false) {
return false; // Return when query fails false
}
return new MySQLResult($result); // Returns the encapsulated result set object
}
}
?>
Next, we create a MySQLResult class to encapsulate the mysqli_result object. This class will provide some convenient methods to simplify the operation of query results.
<?php
class MySQLResult {
private $result;
public function __construct(mysqli_result $result) {
$this->result = $result;
}
public function fetchAll() {
return $this->result->fetch_all(MYSQLI_ASSOC); // Get all data
}
public function fetchRow() {
return $this->result->fetch_assoc(); // Get a line of data
}
public function numRows() {
return $this->result->num_rows; // Get the number of rows in the result set
}
public function free() {
$this->result->free(); // Release the result set
}
}
?>
In this class, we encapsulate some common operations of mysqli_result , such as: getting all rows, getting a single row, getting a result set number, and releasing the result set. This makes database query operations more concise and easy to maintain.
In the controller, we can get data by calling methods in the model without caring about how to operate the mysqli_result object.
<?php
class UserController {
private $db;
public function __construct() {
$this->db = new MySQLDatabase('localhost', 'root', '', 'my_database');
}
public function getUserList() {
$sql = "SELECT * FROM users";
$result = $this->db->query($sql);
if ($result === false) {
echo "Query failed.";
return;
}
$users = $result->fetchAll(); // Get all user data
foreach ($users as $user) {
echo "User: " . $user['name'] . "<br>";
}
$result->free(); // Release the result set
}
}
?>
In the controller, we call the query method of the database model for query. The returned MySQLResult object can get all results through fetchAll() , or use fetchRow() to get single-line data. In this way, the controller's code becomes very concise and the logic is completely separated from the database operation, making it easy to maintain and expand.
By encapsulating the mysqli_result object in the MVC architecture, we not only concentrate database operations on the model layer, but also improve the maintainability and scalability of our code. Using the encapsulated MySQLResult class can simplify the processing of query results, allowing the controller to focus on business logic without caring about the underlying database implementation. This approach has great advantages for team collaboration and later feature expansion.
