Use fputcsv() to export mysqli_result as a file

fputcsv() is a built-in function in PHP that writes the data of an array into a file and formats it into CSV format. Each row array is converted to CSV format lines, and each element in the array is separated by a comma (or a specified separator).

Function prototype:

 bool fputcsv ( resource $handle , array $fields [, string $separator = "," [, string $enclosure = "\"" [, string $escape = "\\" ]]] )

• $handle : The file handle of the target file, must be a valid file resource.

• $fields : The array to be written to a CSV file, usually a result obtained from a database query.

• $separator : Specifies the delimiter, defaults to comma.

• $enclosure : Specifies the quoted character, defaults to double quotes.

• $escape : Specifies escape characters, defaults to backslash.

Step 1: Connect to the database and execute the query

First, we need to connect to the database and execute the query to get the data. Here is a basic mysqli database connection and query operation:

 <?php
// Connect to the database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "your_database_name";

$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 id, name, email FROM users";  // Sample query
$result = $conn->query($sql);

// Check the query results
if ($result->num_rows > 0) {
    // Data can be exported
} else {
    echo "0 results";
}
?>

Step 2: Create and open the CSV file

We use the fopen() function to create and open a CSV file to store the exported data:

 <?php
// Open a CSV document（If not, create）
$file = fopen("export.csv", "w");

// Write CSV document头部
$headers = ['ID', 'Name', 'Email'];
fputcsv($file, $headers);
?>

Step 3: Write the query result to the CSV file

Next, we use fputcsv() to write the query results line by line into the CSV file. We need to use mysqli_fetch_assoc() or other functions to get each line of data of the query and write them to a file:

 <?php
// 逐行读取查询结果并Write CSV document
while($row = $result->fetch_assoc()) {
    // 将每一行数据Write CSV document
    fputcsv($file, $row);
}

// 关闭document
fclose($file);
?>

Step 4: Close the database connection

After the export is complete, remember to close the database connection:

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

Complete code example:

 <?php
// Connect to the database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "your_database_name";

$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 id, name, email FROM users";  // Sample query
$result = $conn->query($sql);

// Check the query results
if ($result->num_rows > 0) {
    // Open a CSV document（If not, create）
    $file = fopen("export.csv", "w");

    // Write CSV document头部
    $headers = ['ID', 'Name', 'Email'];
    fputcsv($file, $headers);

    // 逐行读取查询结果并Write CSV document
    while($row = $result->fetch_assoc()) {
        fputcsv($file, $row);
    }

    // 关闭document
    fclose($file);
    echo "Data has been exported to export.csv";
} else {
    echo "0 results";
}

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

Complete process summary:

1. Connect to the database : Use mysqli to connect to the database.

2. Execute the query : Run the SQL query to get the mysqli_result result set.

3. Create a CSV file : Use fopen() to open the CSV file and write to the header with fputcsv() .

4. Export data : Use fputcsv() loop to write each line of data to a CSV file.

5. Close connection : After the export is complete, close the file handle and database connection.

In this way, you can easily export data from your database into CSV files, making it easier to store, migrate or analyze data.