English
English
In PHP, when working with a MySQL database, the mysqli extension is one of the most commonly used tools. With mysqli, you can easily connect to the database, execute queries, and fetch results. This article explains how to combine the real_query and mysqli_fetch_assoc functions to efficiently retrieve and process query results.
Before learning how to combine these two functions, it’s important to understand their basic purposes:
real_query: This function is provided by the mysqli extension and is used to execute SQL queries. Unlike the query function, real_query sends SQL commands directly to the database for processing. When performing complex queries, real_query offers greater control and flexibility.
mysqli_fetch_assoc: Another commonly used mysqli function, it fetches one row of data from a query result and returns it as an associative array. Each column name becomes a key in the array, allowing you to access its value directly by name.
First, create a database connection and execute an SQL query using real_query. real_query does not return results directly; instead, the result set is retrieved using mysqli_use_result or mysqli_store_result.
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// Create database connection</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">'localhost'</span></span><span>, </span><span><span class="hljs-string">'root'</span></span><span>, </span><span><span class="hljs-string">''</span></span><span>, </span><span><span class="hljs-string">'test_db'</span></span><span>);
<p></span>// Check connection<br>
if ($mysqli->connect_error) {<br>
die('Connection failed: ' . $mysqli->connect_error);<br>
}</p>
<p>// Define SQL query<br>
$sql = "SELECT id, name, age FROM users";</p>
<p>// Execute query<br>
if ($mysqli->real_query($sql)) {<br>
// Fetch query results<br>
$result = $mysqli->use_result(); // Use the results</p>
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$result</span></span><span>) {
</span><span><span class="hljs-comment">// Loop through results</span></span><span>
</span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$row</span></span><span> = </span><span><span class="hljs-variable">$result</span></span><span>-></span><span><span class="hljs-title function_ invoke__">fetch_assoc</span></span><span>()) {
</span><span><span class="hljs-comment">// Process each row</span></span><span>
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"ID: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>['id'] . </span><span><span class="hljs-string">" | Name: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>['name'] . </span><span><span class="hljs-string">" | Age: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>['age'] . </span><span><span class="hljs-string">"<br>"</span></span><span>;
}
} </span><span><span class="hljs-keyword">else</span></span><span> {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Query returned no results"</span></span><span>;
}
} else {
echo "Query failed: " . $mysqli->error;
}
// Close database connection
$mysqli->close();
?>
In this code, we first create a connection to the MySQL database using the mysqli object. If the connection is successful, we execute the SQL query using the real_query method. Then, we fetch the result set with use_result and retrieve each row with mysqli_fetch_assoc.
mysqli_fetch_assoc is a powerful function that returns each row of a result set as an associative array. In the example above, $row is an array, and you can access each field by name, such as $row['id'], $row['name'], and $row['age'].
This approach is more readable compared to mysqli_fetch_row (which returns a numerically indexed array), since you can access data directly by column name without remembering the position of each field.
Flexibility: real_query can handle more complex queries, providing greater control for large applications or advanced query operations. You can execute any SQL command directly, not just simple SELECT queries.
Efficiency: The associative arrays returned by mysqli_fetch_assoc make query results more readable and intuitive, ideal for scenarios requiring clear and efficient data processing.
Error Handling: real_query provides more detailed error and diagnostic information, helping developers locate issues. Meanwhile, mysqli_fetch_assoc simplifies iterating through result sets, avoiding manual index management.
By combining real_query and mysqli_fetch_assoc, you can easily retrieve and process database query results. real_query provides flexible query execution, while mysqli_fetch_assoc makes result handling clearer and more intuitive. Mastering these functions will greatly improve your efficiency and readability when working with databases in PHP.
