English
English
MySQL extension is one of the most commonly used tools when using PHP for MySQL database operations. Especially when processing query results, the combination of mysqli_result object, fetch_assoc() method and while loop is an efficient and common traversal method. This article will provide details on how to use them correctly, and give considerations and sample code.
When you execute a SELECT query with mysqli_query() , the result returned is a mysqli_result object. This object contains all rows of data returned by the query. To access this data, you need to use a special method, such as fetch_assoc() .
fetch_assoc() is a method provided by mysqli_result . It is used to take out the next row from the result set and return an associative array. The key of the array is the field name and the value is the corresponding field value. Each call moves one line down until no more lines return null .
Generally speaking, we will use a while loop, combined with fetch_assoc() , to obtain the data in the result set line by line. This is because fetch_assoc() only returns one line at a time and requires continuous calls to complete all lines.
Sample code:
<?php
// Connect to the database
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
// Check the connection
if ($mysqli->connect_error) {
die('Connection failed: ' . $mysqli->connect_error);
}
// Execute a query
$sql = "SELECT id, name, email FROM users";
$result = $mysqli->query($sql);
if ($result instanceof mysqli_result) {
// Traversal query results
while ($row = $result->fetch_assoc()) {
echo 'ID: ' . $row['id'] . '<br>';
echo 'Name: ' . $row['name'] . '<br>';
echo 'Mail: ' . $row['email'] . '<br>';
echo '<a href="https://m66.net/user/' . $row['id'] . '">check the details</a><br><br>';
}
// Release the result set
$result->free();
} else {
echo 'Query failed: ' . $mysqli->error;
}
// Close the connection
$mysqli->close();
?>
? Check whether the query is successful <br> Before calling $result->fetch_assoc() , make sure that $result is indeed a mysqli_result object, not false .
? Release result set <br> After traversal, call $result->free() to release the resource to avoid memory leakage.
? Close the database connection <br> After using the database, call $mysqli->close() to close the connection.
? Prevent SQL injection <br> If user input is involved in the query, it is strongly recommended to use preprocessing statements ( prepare + bind_param ) instead of splicing the string directly.
