English
English
When operating MySQL databases in PHP, the mysqli extension provides a variety of methods to obtain query results, among which mysqli_result::fetch_column and mysqli_fetch_assoc are commonly used methods to read data. But many developers are wondering whether these two can be used together? Can they be combined to play a more flexible role? This article will describe their differences and combination usage in detail with examples.
mysqli_result::fetch_column
This method is used to get a single value of a specified column from the result set and return a single data for that column. It is usually used to save memory and code complexity when you only need the value of a certain column.
mysqli_fetch_assoc
This function returns a row of data in the result set and is presented in the form of an associative array. The key name of the array is the field name, which is convenient for obtaining multiple columns of data.
Simply put, fetch_column gets a single column of data, while fetch_assoc gets an associative array of data in the entire row.
From a technical point of view, mysqli_result::fetch_column is a method of the mysqli_result class, while mysqli_fetch_assoc is a process-oriented function, and they essentially belong to two different operating styles.
If you are using an object-oriented style:
Methods such as $result->fetch_assoc() and $result->fetch_column() should be used uniformly.
If you are using a process-oriented style:
Mysqli_fetch_assoc($result) should be used, and the procedural style does not have a fetch_column corresponding function.
Therefore, they cannot be called alternately directly on the same result set , otherwise it will cause data cursor confusion.
Suppose we query the user table users in the database, with fields id , name , and email , and demonstrate two methods to obtain data.
<?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");
// Check the connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "SELECT id, name, email FROM users";
$result = $mysqli->query($sql);
if ($result) {
// usefetch_assocGet each row of data
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . "，Name: " . $row['name'] . "，Mail: " . $row['email'] . "<br>";
}
$result->free();
}
// Execute the query again
$result2 = $mysqli->query($sql);
if ($result2) {
// usefetch_columnGet the first column（id）
while (($id = $result2->fetch_column(0)) !== null) {
echo "userID: " . $id . "<br>";
}
$result2->free();
}
$mysqli->close();
?>
In the above example, we performed two queries to use fetch_assoc and fetch_column respectively. This avoids the problem of confusion in the result set cursor.
If you want to use fetch_assoc to get the complete data first, and then use fetch_column to get a column, it is recommended to cache the result to the array and then operate the array:
<?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");
$sql = "SELECT id, name, email FROM users";
$result = $mysqli->query($sql);
$rows = [];
if ($result) {
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
$result->free();
}
// Get a column of data from the cache
$userIds = array_column($rows, 'id');
foreach ($userIds as $id) {
echo "userID: $id<br>";
}
$mysqli->close();
?>
This is both efficient and convenient, avoiding duplicate queries and cursor problems.
mysqli_result::fetch_column and mysqli_fetch_assoc belong to different operation styles and cannot be used directly.
If you want to use both, it is recommended to execute the query separately, or use fetch_assoc to get all of it, and then use PHP built-in functions to extract the specified columns.
When using it in combination, pay attention to the result set cursor status to avoid confusion in data reading.
By reasonably combining these two methods, you can make you more flexible and efficient when processing database data.
