English
English
In PHP, the mysqli extension provides a more secure and flexible way to interact with the database. The mysqli::stmt_init function is a very useful function for initializing preparation statements and is usually used to perform SQL queries with parameters. This article will explore how to use mysqli::stmt_init to support dynamic parameters and flexibly process query parameters.
The mysqli::stmt_init function can be used to create a preprocessing statement object for SQL query statements, which can safely bind multiple parameters and execute queries. It reduces the risk of SQL injection by precompiling query statements, and is especially suitable for parameterized queries.
First, make sure you are connected to the database. Here is an example of a database connection:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
In some cases, we cannot determine the number of parameters for the query statement in advance. For example, you might want to build a query dynamically and determine how many query parameters are needed based on the incoming data.
Suppose we have a query that allows us to dynamically pass in any number of parameters to filter, like this:
SELECT * FROM users WHERE name = ? AND age = ? AND city = ?
You can see that name , age and city are conditional filtering fields, and we hope to be able to dynamically pass query conditions into them according to actual needs.
Building a query statement: We first dynamically build SQL statements through a simple array of conditions.
Bind parameters: Then, we use call_user_func_array to bind dynamic parameters to the query.
Here is an example of an implementation:
<?php
// 1. Build SQL Query statement
$baseQuery = "SELECT * FROM users WHERE ";
$conditions = [];
$params = [];
$types = "";
// Dynamic condition array，Assume that POST Get
$filters = [
'name' => 'John',
'age' => 25,
'city' => 'New York'
];
// 根据传入的过滤条件动态生成Query statement
foreach ($filters as $key => $value) {
$conditions[] = "$key = ?";
$params[] = $value;
$types .= "s"; // Assume that all conditions are string types
}
$query = $baseQuery . implode(" AND ", $conditions);
// 2. use stmt_init Initialize query
$stmt = $conn->stmt_init();
if ($stmt->prepare($query)) {
// 3. Dynamic binding parameters
$stmt->bind_param($types, ...$params);
// 4. Execute a query
$stmt->execute();
$result = $stmt->get_result();
// 5. Get结果
while ($row = $result->fetch_assoc()) {
print_r($row);
}
// 6. Close statement
$stmt->close();
} else {
echo "Error: " . $stmt->error;
}
?>
Dynamically build queries: We dynamically build SQL query statements based on the $filters array, adding conditions only when needed.
Dynamic binding parameters: Use bind_param() to bind query parameters. We pass parameters through call_user_func_array , which allows us to handle any number of query parameters.
Query execution: Finally, execute the query through execute() and use get_result() to get the query result.
Through the above method, we can implement the support of dynamic parameters in the mysqli::stmt_init function, and we can also flexibly process query parameters. Dynamic parameterized queries not only increase the flexibility of the query, but also effectively prevent SQL injection and ensure the security of the application.
Please note: In actual projects, make sure all parameters from user input are strictly verified and disinfected, especially when handling dynamic queries. Avoid splicing of SQL statements directly, and always use preparation statements and binding parameters to execute queries.
