Query MySQL and Export Data as CSV in PHP
Exporting relational data from a MySQL database to a CSV file is a common requirement for reporting, but doing it correctly requires attention to how your data is retrieved and how the output stream is handled. The core challenge often lies not in the SQL query itself, but in managing the connection lifecycle and ensuring that debug information or HTML artifacts don't contaminate the final file.
When attempting to export data using standard PHP mysqli functions, a frequent mistake involves echoing the result directly before trying to save it. If you try to capture the output of an echo statement into a string variable and then write that to a file, you often end up with "junk" at the beginning of the CSV. This usually happens because mysqli_query or other functions might trigger warnings, notices, or debug headers (like Xdebug error tables) if the connection isn't configured correctly for output buffering or if debugging is active in your environment.
To generate a clean CSV, you need to treat the database result as a stream of rows and write them directly to an output file handle or use file_put_contents. The most robust approach involves executing a query that joins your tables based on the user ID, iterating through the result set, and manually formatting each row into CSV format. This method gives you full control over escaping quotes and handling newlines, preventing data corruption when values contain commas or special characters.
Here is a pattern for doing this using mysqli:
$host = 'localhost';
$db = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
$sql = "SELECT u.username, p.product_name, o.order_date, t.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_GET['user_id']]);
$outputFile = 'export_' . date('Y-m-d_His') . '.csv';
$handle = fopen($outputFile, 'w');
// Write headers first
fputcsv($handle, ['Username', 'Product Name', 'Order Date', 'Total Amount']);
while ($row = $stmt->fetch()) {
fputcsv($handle, [
$row['username'],
$row['product_name'],
$row['order_date'],
$row['total_amount']
]);
}
fclose($handle);
Using fopen and fputcsv is generally safer than manually concatenating strings. The fputcsv function automatically handles quoting fields that contain commas, double quotes, or newlines, which is critical for maintaining CSV integrity. If you stick to simple string concatenation, a product name like "Hello, World" will break the column alignment unless you explicitly escape the internal comma with a double quote (e.g., "\"Hello, World\"") and wrap it in quotes.
One specific pitfall mentioned in similar scenarios is seeing Xdebug error tables or HTML fragments inside the CSV file. This typically indicates that your PHP environment has output buffering enabled or that debugging tools are injecting content into the response stream before the file write operation completes. To prevent this, ensure you disable output buffering during the export process if necessary:
ob_start(); // Capture output if needed later, or simply avoid echoing anything
// ... execute query and write to file ...
ob_end_flush();
However, the cleanest solution is often to redirect all standard output to /dev/null or ensure no echo statements precede the file writing logic. In a production environment using frameworks like Laravel, you can utilize the database facade to run the raw query and process the results similarly, ensuring that no application-level logging interferes with the export stream.
Finally, remember that this approach requires read access to the specific tables involved in your join. If your application uses prepared statements for security (which it should), ensure that the user ID parameter is strictly validated and sanitized before execution to prevent SQL injection risks during the export process.