Mysqli_result::fetch_column Tips for using mysqli_query with

When operating MySQL databases in PHP, mysqli extension is a common and efficient choice. Especially when processing query results, the combination of mysqli_result::fetch_column and mysqli_query function can greatly simplify the process of data acquisition and improve the simplicity and readability of the code. This article will focus on the combination of these two and share some practical tips and examples to help you better master and apply them.

1. Brief introduction to mysqli_query

mysqli_query is the core function that executes SQL statements. It accepts database connection objects and SQL query statements as parameters, and returns a result set ( mysqli_result object) or a boolean value (for non-query statements).

 $mysqli = new mysqli('gitbox.net', 'username', 'password', 'database');
$sql = "SELECT name FROM users WHERE status = 1";
$result = $mysqli->query($sql);

2. Introduction to mysqli_result::fetch_column

fetch_column is a method of the newly added mysqli_result class in PHP 8.1, which is used to obtain a single piece of data from a specified column from the result set. Compared with the previous fetch_assoc() or fetch_row() , it is more concise and is especially suitable for scenarios where only a single column of data is needed.

 $name = $result->fetch_column(0);
echo $name;

Where parameter 0 means taking the data in column 0, the default is also 0.

3. Practical skills for use together

1. Get a single piece of data for a single field

When querying only requires one field value, use fetch_column to get the results quickly without complex array indexing operations.

 $sql = "SELECT email FROM users WHERE id = 123";
$result = $mysqli->query($sql);
if ($result) {
    $email = $result->fetch_column();
    echo "User email is：" . $email;
}

2. Avoid redundant data acquisition and improve performance

When querying in the database, try to select only the required fields and cooperate with fetch_column to reduce memory overhead.

 $sql = "SELECT COUNT(*) FROM orders WHERE status = 'pending'";
$result = $mysqli->query($sql);
if ($result) {
    $pendingCount = $result->fetch_column();
    echo "Pending order quantity：" . $pendingCount;
}

3. Integrate security and reliability by combining error checking

When using mysqli_query , the return value should be checked to prevent program exceptions due to SQL errors. Reading data with fetch_column should also ensure that the result set is valid.

 $sql = "SELECT username FROM users WHERE id = 999";
$result = $mysqli->query($sql);
if (!$result) {
    die("Query failed：" . $mysqli->error);
}
$username = $result->fetch_column();
if ($username === null) {
    echo "The user does not exist";
} else {
    echo "The username is：" . $username;
}

4. Optimize security in combination with preprocessing statements

Although this article focuses on mysqli_query , combining preprocessing statements and fetch_column can also achieve safer parameter transfer.

 $stmt = $mysqli->prepare("SELECT email FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$userId = 5;
$stmt->execute();
$result = $stmt->get_result();
$email = $result->fetch_column();
echo "User email：" . $email;

4. Summary

Through the above examples, we can see that the combination of mysqli_result::fetch_column and mysqli_query not only makes the code more concise, but also helps improve data reading efficiency. Especially suitable for querying single columns of data. I hope the tips shared in this article can help you operate MySQL database more efficiently and implement elegant PHP programming.