English
English
When developing PHP applications, mysqli extensions are a common way to interact with MySQL databases. As the project continues to expand, using mysqli functions directly may lead to duplication of the code, high coupling, and difficult to maintain. In order to improve code reusability and maintainability, we can encapsulate the mysqli_result function into a common data access layer.
This article will introduce how to encapsulate the mysqli_result function and provide code examples to help you build an efficient and easy-to-maintain data access layer.
The purpose of the data access layer is to separate database operations from business logic, so that the access logic of the database does not appear directly in the application's business code. By encapsulating the database operations, the reusability and maintainability of the code can be improved, and the difficulty of replacing the database in the later stage can be reduced.
Here are a few points to consider when designing a data access layer:
Encapsulate database connections : Avoid repeated writing of connection code every time you operate the database.
Simplify query result processing : encapsulate the operation of mysqli_result and process query results in a unified manner.
Support CRUD operations : Support common addition, deletion, modification and check operations.
Improve code reusability : Through encapsulation, the database operation code reusability is higher and code redundancy is reduced.
First, we need to create a database class to manage the connection and query operations of the database.
class Database {
private $connection;
// Constructor initializes database connection
public function __construct($host, $user, $password, $dbname) {
$this->connection = new mysqli($host, $user, $password, $dbname);
if ($this->connection->connect_error) {
die("Connection failed: " . $this->connection->connect_error);
}
}
// Execute a query，Return result set
public function query($sql) {
$result = $this->connection->query($sql);
if (!$result) {
die("Query failed: " . $this->connection->error);
}
return $result;
}
// Close the database connection
public function close() {
$this->connection->close();
}
}
In the above code, we encapsulate a Database class with a constructor for initializing the database connection, and a query method for executing SQL queries and returning the result set.
mysqli_result is an object in mysqli that is used to store query results. When we build the data access layer, we usually need to encapsulate mysqli_result to make the operation of the result set more concise.
Here is a code example of how to encapsulate mysqli_result :
class DbResult {
private $result;
// The constructor receives query results
public function __construct($result) {
$this->result = $result;
}
// Get all results
public function fetchAll() {
$rows = [];
while ($row = $this->result->fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
// Get single line results
public function fetchOne() {
return $this->result->fetch_assoc();
}
// Get the number of rows
public function numRows() {
return $this->result->num_rows;
}
}
In the above code, the DbResult class encapsulates the mysqli_result object and provides the following methods:
fetchAll() : Get all query results.
fetchOne() : Get the result of a single query.
numRows() : Returns the number of rows in the query result.
By combining the database class and the result set encapsulation class, we can create a complete data access layer, as shown below:
class DataAccessLayer {
private $db;
// Constructor initializes database connection
public function __construct($host, $user, $password, $dbname) {
$this->db = new Database($host, $user, $password, $dbname);
}
// Query the data and return the encapsulated result set
public function fetchData($sql) {
$result = $this->db->query($sql);
$dbResult = new DbResult($result);
return $dbResult;
}
// Insert data
public function insertData($sql) {
return $this->db->query($sql);
}
// Update data
public function updateData($sql) {
return $this->db->query($sql);
}
// Delete data
public function deleteData($sql) {
return $this->db->query($sql);
}
// Close the connection
public function close() {
$this->db->close();
}
}
In this example, the DataAccessLayer class encapsulates common database operations such as query, insert, update, and delete. We use the DbResult class to encapsulate the query results, making the operation of the results more convenient.
Now we can use the encapsulated data access layer in our business code:
// Create a database access object
$dal = new DataAccessLayer('localhost', 'root', '', 'test_db');
// Query data
$sql = "SELECT * FROM users";
$dbResult = $dal->fetchData($sql);
// Get query results
$users = $dbResult->fetchAll();
foreach ($users as $user) {
echo $user['username'] . "<br>";
}
// Close the database connection
$dal->close();
In this way, we can easily perform database operations without repeatedly writing logic such as connections, query results processing, etc. in each page.
By encapsulating the mysqli_result result set and database connection, we create a common data access layer. This data access layer makes database operations more concise, reusable, and improves the maintainability of the code. You can further expand this data access layer according to your business needs and support more database operations, such as transaction management, batch insertion, etc.
If you want to manage database connections more flexible, you can introduce a singleton pattern to avoid creating database connections multiple times.
I hope this article can help you build a more efficient and maintainable database operation layer and improve your development efficiency.
