English
English
When working with MySQL databases in PHP, the mysqli extension is one of the most commonly used tools. With mysqli, you can easily connect to the database, execute queries, and retrieve results. This article will explain how to combine the real_query function with the mysqli_fetch_assoc function to efficiently fetch and handle query results.
Before learning how to combine these two functions, let's first understand their basic purposes:
real_query: This is a function provided by the mysqli extension to execute SQL queries. Unlike the query function, real_query sends SQL commands directly to the database for processing. It offers more control and flexibility, especially for complex queries.
mysqli_fetch_assoc: This is another commonly used mysqli function that fetches a row from the result set as an associative array. Each field name becomes an array key, allowing you to access values by their field names.
First, create a database connection and use real_query to execute an SQL query. real_query does not return the results directly; instead, the result set is retrieved via 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 result<br>
$result = $mysqli->use_result(); </span>// Use the result</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">// Iterate 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 class="hljs-title function_ invoke__">fetch_assoc</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>[</span><span><span class="hljs-string">'id'</span></span><span>] . </span><span><span class="hljs-string">" | Name: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>[</span><span><span class="hljs-string">'name'</span></span><span>] . </span><span><span class="hljs-string">" | Age: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>[</span><span><span class="hljs-string">'age'</span></span><span>] . </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">"No results found"</span></span><span>;
}
} else {
echo "Query failed: " . $mysqli->error;
}
// Close the database connection
$mysqli->close();
?>
In this code, we first create a connection to the MySQL database using the mysqli object. If the connection succeeds, we use the real_query method to execute the SQL query. Then, we retrieve the result set with use_result and fetch each row using the mysqli_fetch_assoc function.
mysqli_fetch_assoc is a powerful function that returns each row in the result set as an associative array. In the example above, $row is an array, and you can access each row's data by field names, such as $row['id'], $row['name'], and $row['age'].
This approach is more readable compared to using mysqli_fetch_row (which returns a numerically indexed array), as you can access data directly by field names without remembering the position of each field.
Flexibility: real_query can handle more complex queries and provides greater control, suitable for large applications or advanced query operations. You can execute any SQL command directly, not just simple SELECT queries.
Efficiency: The associative array returned by mysqli_fetch_assoc makes processing results more readable and intuitive, ideal for scenarios where clear and efficient data handling is needed.
Error Handling: real_query provides more detailed error and diagnostic information, helping developers locate issues. Meanwhile, mysqli_fetch_assoc simplifies iterating through the result set and avoids manually managing indexes.
By combining real_query and mysqli_fetch_assoc, you can easily retrieve and process database query results. real_query offers flexible query execution, while mysqli_fetch_assoc makes result handling clear and straightforward. Mastering these functions will greatly enhance your efficiency and readability when working with databases in PHP.
