How to avoid duplicate data when using mysqli_result::fetch_column

In PHP development, it is very common to use mysqli extension for database operations. The mysqli_result::fetch_column function is a simple method for extracting single column data from query results, but in actual use, many developers will encounter the problem of data duplication. This article will analyze the causes of duplicate data in response to this phenomenon and provide effective solutions and sample code to help everyone avoid this problem.

1. Reasons for duplicate data generation

1. Improper design of database query statements <br> Many times, the root cause of duplicate data is the SQL query itself, such as not using DISTINCT , or the table connection condition errors during the association query cause duplication of data.

2. Data extraction method error <br> When using fetch_column , if the data is processed unreasonably in the loop, it may also lead to repeated output.

3. Data cache or loop logic defects <br> If the data is not deduplicated correctly after being stored in the array, or there is a problem with the loop traversal logic, it will also cause duplication.

2. How to avoid duplicate data

1. Optimize SQL query

The most direct and efficient way is to deduplicate in SQL statements:

 $sql = "SELECT DISTINCT column_name FROM table_name WHERE conditions";
$result = $mysqli->query($sql);

This ensures that every piece of data is unique in the returned result set.

2. Use mysqli_result::fetch_column reasonably

fetch_column only extracts a column of data in the current row. If you call it a loop, you can get the data row by row, but you need to pay attention to:

• Make sure that no duplicate calls result in duplicate output.

• Avoid not properly freeing or reusing the result set.

3. Deduplication at the PHP level

If the database layer cannot ensure uniqueness, the PHP layer can use array deduplication:

 $data = [];
while ($value = $result->fetch_column()) {
    $data[] = $value;
}
$data = array_unique($data);  // Remove duplicate values

3. Sample code

Here is a complete example that demonstrates how to avoid duplicate data:

 <?php
$mysqli = new mysqli("gitbox.net", "username", "password", "database");

if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

// useDISTINCTAvoid duplicate data
$sql = "SELECT DISTINCT user_email FROM users WHERE status = 'active'";
$result = $mysqli->query($sql);

if ($result) {
    $emails = [];
    while ($email = $result->fetch_column()) {
        $emails[] = $email;
    }

    // Make sure to remove the weight again（preventSQLThe statement does not work）
    $emails = array_unique($emails);

    foreach ($emails as $email) {
        echo $email . "<br>";
    }

    $result->free();
} else {
    echo "Query failed: " . $mysqli->error;
}

$mysqli->close();
?>

4. Summary

• When designing SQL, use DISTINCT to deduplication is preferred.

• fetch_column extracts single column data by row and calls reasonably.

• The PHP side uses array_unique as a secondary deduplication method.

• Check the associated query and data structure to avoid unintentional duplicate rows.

Through the above techniques, the duplicate data problem when using mysqli_result::fetch_column can be basically eliminated, and the code robustness and data accuracy can be improved.