English
English
When building modern web applications, database operations are often one of the core functionalities. Especially when handling requests from the frontend, using a REST API interface to interact with the database becomes particularly important. In PHP, the mysqli::stmt_init method is a common way to perform database operations, enabling us to execute SQL queries more securely and efficiently. This article explores how to integrate the mysqli::stmt_init function with a REST API interface to achieve efficient database request handling.
mysqli::stmt_init is a PHP function used to create a mysqli_stmt object, which is designed for preparing and executing SQL statements. Unlike directly using mysqli_query or mysqli_prepare, the stmt_init method provides better handling for preparing SQL statements and binding parameters, improving both the security and efficiency of your code.
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT name, email FROM users WHERE id = ?")) {
$stmt->bind_param("i", $user_id); // Bind parameter
$stmt->execute(); // Execute query
$stmt->close(); // Close statement
}
In the code above, stmt_init initializes a query statement, binds the required parameters, and then executes the SQL query.
REST (Representational State Transfer) is an architectural style based on the HTTP protocol, allowing web applications to interact with servers using standard HTTP requests such as GET, POST, PUT, and DELETE. REST APIs are commonly used for data exchange between frontend and backend.
A REST API acts as a bridge between the client and the server. The client sends a request, and the server returns data. With REST APIs, implementing CRUD (Create, Read, Update, Delete) operations becomes straightforward.
{
"method": "GET",
"url": "https://m66.net/api/users",
"response": [
{"id": 1, "name": "John Doe", "email": "john@example.com"},
{"id": 2, "name": "Jane Doe", "email": "jane@example.com"}
]
}
In the example above, the client sends a GET request, and the backend returns a list of users through the REST API.
In real-world development, we may need to expose database query functionality as a REST API. Using mysqli::stmt_init to handle SQL queries in combination with REST APIs for processing request parameters and response data is a common practice.
Let’s say we want to build a simple API that allows users to query user information through a GET request.
<?php
<p>// Set response headers<br>
header('Content-Type: application/json');</p>
<p>// Connect to the database<br>
$mysqli = new mysqli('localhost', 'user', 'password', 'database');</p>
<p>// Check the database connection<br>
if ($mysqli->connect_error) {<br>
echo json_encode(['error' => 'Database connection failed']);<br>
exit();<br>
}</p>
<p>// Get the requested user ID<br>
$user_id = isset($_GET['id']) ? (int)$_GET['id'] : 0;</p>
<p>if ($user_id > 0) {<br>
// Initialize stmt<br>
$stmt = $mysqli->stmt_init();</p>
if ($stmt->prepare("SELECT name, email FROM users WHERE id = ?")) {
// Bind parameter
$stmt->bind_param("i", $user_id);
// Execute query
$stmt->execute();
// Store result
$stmt->store_result();
if ($stmt->num_rows > 0) {
$stmt->bind_result($name, $email);
$stmt->fetch();
// Return data
echo json_encode([
'id' => $user_id,
'name' => $name,
'email' => $email
]);
} else {
echo json_encode(['error' => 'User not found']);
}
// Close statement
$stmt->close();
} else {
echo json_encode(['error' => 'Failed to prepare statement']);
}
} else {
echo json_encode(['error' => 'Invalid user ID']);
}
// Close database connection
$mysqli->close();
?>
Receive Request Parameter: Use $_GET['id'] to retrieve the user ID from the client. If not provided or set to 0, an error is returned.
Database Query: Use mysqli::stmt_init to initialize the database query, then bind_param to bind the query parameters, execute the SQL query, and return the result.
Return Data: The query result is returned to the client in JSON format. If successful, it includes the user's name and email; otherwise, an error message is returned.
Response Format: header('Content-Type: application/json') ensures the response format is JSON.
The client can fetch user information with the following GET request:
GET https://m66.net/api/user?id=1
{
"id": 1,
"name": "John Doe",
"email": "john@example.com"
}
To improve system performance, consider the following optimizations:
Parameter Binding: Use bind_param to bind SQL parameters, preventing SQL injection and enhancing query efficiency.
SQL Query Optimization: Ensure SQL queries are efficient, avoid unnecessary operations, and use indexing to reduce response time.
Database Connection Pooling: Under high concurrency, using a connection pool can reduce the overhead of repeatedly connecting to the database.
Caching: For frequently accessed queries, consider using a caching mechanism to reduce database load.
Combining the mysqli::stmt_init function with a REST API interface not only enhances the security of database operations but also boosts query efficiency. With well-designed APIs and optimized database operations, the system can remain stable and efficient even under high traffic. Applying best practices in both database handling and API design can significantly improve application performance and user experience.
Related Tags:
API
