MySQL CSV Export PHP
User
mysql export data from users and profiles table to csv in php
Assistant
To export data from two MySQL tables (`users` and `profiles`) to a CSV file in PHP, you can use the following approach:
### Example Code
<?php
// Database configuration
$servername = "localhost";
$username = "root";
$password = "";
$database = "your_database";
try {
// Create a connection using PDO
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Query to get data from users and profiles tables (adjust the columns as per your needs)
$sql = "SELECT users.id, users.name, users.email, profiles.phone, profiles.address
FROM users
JOIN profiles ON users.id = profiles.user_id";
$stmt = $conn->prepare($sql);
$stmt->execute();
// Fetch all rows
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Define the CSV filename
$filename = 'users_profiles_export_' . date('Y-m-d') . '.csv';
// Set headers to download file rather than displaying it
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename="' . $filename . '"');
// Open file pointer connected to output stream
$output = fopen('php://output', 'w');
// Fetch the first row to get the headers
if (!empty($rows)) {
// Output column headings (CSV header row)
fputcsv($output, array_keys($rows[0]));
// Output rows
foreach ($rows as $row) {
fputcsv($output, $row);
}
} else {
echo "No data available to export.";
}
// Close file pointer
fclose($output);
// Close database connection
$conn = null;
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>### Explanation
1. **Database Connection**:
– Uses `PDO` to connect to the MySQL database.
– Fetches data from the `users` and `profiles` tables using a `JOIN`.
2. **CSV File Creation**:
– The `header()` function sets the content type to CSV and forces the browser to download the file.
– Uses `fputcsv()` to write data to the CSV file.
– The first row's keys are used as CSV column headers.
– Loops through each row to write the data.
3. **Output Stream**:
– `php://output` is used to directly write the CSV content to the browser.
### Usage
– Save the script as `export.php` on your server.
– Access it via your browser (`http://yourdomain.com/export.php`).
– The CSV file will automatically download.