English
English
In PHP, database operations are an essential part of a developer's daily tasks. To improve efficiency and prevent SQL injection attacks, using MySQLi extension's prepared statements is highly recommended. This article will provide a detailed guide to the usage process of fetch_fields, prepare, and bind_result in PHP's MySQLi.
First, we need to establish a database connection. Then, we can use the prepare method to prepare an SQL statement. This method takes an SQL query as a parameter and returns a prepared statement object.
$mysqli = new mysqli("localhost", "user", "password", "database");
<p>if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>$query = "SELECT id, name, email FROM users WHERE active = ?";<br>
$stmt = $mysqli->prepare($query);<br>
In the code above, the prepare method preprocesses the SQL statement and prepares it for subsequent parameter binding.
Next, we need to bind parameters. The bind_param method binds actual parameter values to the SQL statement. In this case, the query filters data through the active field, so we need to bind a parameter to replace the ?.
$active = 1;
$stmt->bind_param("i", $active); // "i" indicates an integer type
The first argument of bind_param is a string that defines the types of parameters in the SQL statement. Common types include:
i: Integer
d: Double (floating point number)
s: String
b: BLOB (Binary Large Object)
In this example, we bind an integer-type parameter ($active).
Once the parameters are bound, we can execute the SQL statement using the execute method:
$stmt->execute();
At this point, the SQL query will be executed, but no data has been returned yet. We need to use fetch_fields to get the structure of the query result, and use bind_result to bind each column's value.
The fetch_fields method retrieves the field information of the query result, including column names, types, etc. It returns an array of field objects, which can be used for further result processing.
$fields = $stmt->fetch_fields();
foreach ($fields as $field) {
echo "Field name: " . $field->name . "<br>";
echo "Field type: " . $field->type . "<br>";
}
The code above will output the name and type of each field. The fetch_fields method returns a field object with rich metadata, making it easier for developers to obtain database table structure information.
After executing the query, the next step is to use the bind_result method to bind the result columns to PHP variables. The bind_result method allows you to extract each row of data from the result set by passing variables.
$stmt->bind_result($id, $name, $email);
Here, we bind the id, name, and email fields from the query result to the $id, $name, and $email variables, respectively.
After binding the result variables, we can use the fetch method to retrieve the query results row by row.
while ($stmt->fetch()) {
echo "ID: $id, Name: $name, Email: $email<br>";
}
The fetch method returns a boolean value indicating whether a row of data has been successfully retrieved. If successful, the current row’s data can be accessed through the bound variables.
After all operations are completed, remember to close the prepared statement and database connection to release resources:
$stmt->close();
$mysqli->close();
Here is the complete code that integrates all the steps:
<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
<p>if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>$query = "SELECT id, name, email FROM users WHERE active = ?";<br>
$stmt = $mysqli->prepare($query);</p>
<p>$active = 1;<br>
$stmt->bind_param("i", $active);</p>
<p>$stmt->execute();</p>
<p>$fields = $stmt->fetch_fields();<br>
foreach ($fields as $field) {<br>
echo "Field name: " . $field->name . "<br>";<br>
echo "Field type: " . $field->type . "<br>";<br>
}</p>
<p>$stmt->bind_result($id, $name, $email);<br>
while ($stmt->fetch()) {<br>
echo "ID: $id, Name: $name, Email: $email<br>";<br>
}</p>
<p data-is-last-node="" data-is-only-node="">$stmt->close();<br>
$mysqli->close();<br>
?><br>
Related Tags:
fetch_fields
