Export mysqli_result data to CSV file

In PHP, when using the mysqli extension for database operations, we usually need to export the query results as a CSV file. The mysqli_result class is used to process query result sets, and to export this data as a CSV file, we need to read the query result line by line and write it to the CSV file. This article will explain in detail how to use the mysqli_result function to complete this operation.

1. Connect to the database

First, we need to establish a database connection to ensure that we can successfully query the data. Here is a code example for establishing a connection:

 <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

2. Execute query

Next, execute the SQL query and get the mysqli_result object. Suppose we want to query a table called users , query the names and emails of all users.

 <?php
$sql = "SELECT name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Data exists，Can export
    exportToCSV($result);
} else {
    echo "No data";
}
?>

3. Export data as CSV file

Now, we will use the mysqli_result object to export the query results as a CSV file. We implement this function through the fputcsv function.

 <?php
function exportToCSV($result) {
    // Set file name
    $filename = "users_data_" . date("Y-m-d") . ".csv";

    // Open the output stream，Or create and open a CSV document
    $output = fopen('php://output', 'w');

    // set up HTTP Header information，强制浏览器下载document
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    // Output CSV document的列头
    $columns = $result->fetch_fields();
    $header = [];
    foreach ($columns as $column) {
        $header[] = $column->name;
    }
    fputcsv($output, $header);

    // Output查询结果
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, $row);
    }

    // 关闭Output流
    fclose($output);
}
?>

4. Complete code example

The following is the complete PHP code to export the query results to a CSV file:

 <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Execute a query
$sql = "SELECT name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    exportToCSV($result);
} else {
    echo "No data";
}

// Export CSV function
function exportToCSV($result) {
    // Set file name
    $filename = "users_data_" . date("Y-m-d") . ".csv";

    // Open the output stream，Or create and open a CSV document
    $output = fopen('php://output', 'w');

    // set up HTTP Header information，强制浏览器下载document
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    // Output CSV document的列头
    $columns = $result->fetch_fields();
    $header = [];
    foreach ($columns as $column) {
        $header[] = $column->name;
    }
    fputcsv($output, $header);

    // Output查询结果
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, $row);
    }

    // 关闭Output流
    fclose($output);
}

// Close the database connection
$conn->close();
?>

Through the above steps, we can easily export the MySQL query results as a CSV file. Just make sure to connect to the database and query the data correctly, the results can be processed and exported through mysqli_result .