Export data from a MySQL database to a CSV file using PHP 8.
Hello, this time I want to share with you how we can create and export a CSV file with the data we have in a database.
In this case, I will use a MySQL database for the sake of comfort, because that is the database I habitually use when I code with PHP, but you can use whatever you prefer.
First, I will show you the file system of our application.
The file system looks like this:
Please create this file system within your code editor.
The second time, we’re going to start with the code, so… open the “connection.php” file and write the following code that I am going to share with you:
<?php
// Define constants for the database connection
define("DBHOST", "localhost");
define("DBUSER", "root");
define("DBPASS", "");
define("DBNAME", "users_csv");
// Create a DSN (Data Source Name) connection string
$dsn = "mysql:host=" . DBHOST . ";dbname=" . DBNAME;
try {
// Create a PDO instance and connect to the database
$db = new PDO($dsn, DBUSER, DBPASS);
// Set character encoding to UTF-8
$db->exec("SET NAMES utf8");
// Set PDO error mode to exception for proper error handling
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Set the default fetch mode to associative array
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
// In case of a connection error, display the error message
echo $e->getMessage();
}
// Now, the variable $db contains a connection to the database
?>This code allows us to establish a connection with the database. In my case, I had previously created a new database named ‘users_csv’. You can do the same, or you can name your database whatever you want, but remember to change the value of the ‘DBNAME’ constant.
Following that, open the ‘create_user_table.php’ file and write the following code:
<?php
// Include the database connection file
require_once "../connection/connection.php";
// Check if the database connection was successful
if (!$db) {
echo "Connection failed";
exit;
} else {
try {
// Create the 'user' table if it doesn't already exist
$sql = "CREATE TABLE IF NOT EXISTS `user` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`last_name` VARCHAR(255) NOT NULL ,
`phone` INT NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;";
// Execute the SQL query to create the table
$db->exec($sql);
// Display a success message if the table was created
echo "Table 'user' created successfully.";
// Close the database connection
$db = null;
} catch (PDOException $e) {
// Handle any PDO exceptions and display an error message if necessary
echo $e->getMessage();
}
}Then, open “insert_users.php” file and write the following code:
<?php
// Include the database connection file
require_once "../connection/connection.php";
// Check if the database connection was successful
if (!$db) {
echo "Connection failed";
exit;
} else {
try {
// Create five users in the users table
$sql = "INSERT INTO `user` (`id`, `name`, `last_name`, `phone`) VALUES (NULL, 'Augustus', 'Nash', '930390293'), (NULL, 'Franklin ', 'Levine', '23454354'), (NULL, 'Tracey ', 'Gray', '24323453'), (NULL, 'Kareem', 'Elliott', '9487855'), (NULL, 'Jefferey', 'Bennett', '34342354');";
// Execute the SQL query to add the five users
$db->exec($sql);
// Display a success message if the users was added to the table successfully
echo "The new users was added to the table successfully.";
// Close the database connection
$db = null;
} catch (PDOException $e) {
// Handle any PDO exceptions and display an error message if necessary
echo $e->getMessage();
}
}
?>Right now, we have the necessary codes to create the “users” table and insert five users into this table.
In the next step, what we are going to do is run these two files in the browser.
In my case, I use Xampp, so the following URLs are:
http://localhost/csv_project/sql/create_user_table.php
http://localhost/csv_project/sql/insert_user.phpIn that order .
If everything was fine, you should have seen the following messages:
Table 'user' created successfully.
The new users was added to the table successfully.That means everythings was fine.
Now, open the “index.php” file and write the following code.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Download csv file</title>
</head>
<body>
<style>
table {
border-collapse: collapse;
width: 50%;
}
table, th, td {
border: 1px solid black;
text-align: center;
}
button{
margin-top: 20px;
padding: 10px;
border-radius: 5px;
background-color: #4CAF50;
color: white;
border: none;
cursor: pointer;
}
</style>
<h1>Download csv file</h1>
<?php
include_once './components/table.php';
?>
<form action="./functions/download_csv.php">
<button type="submit" name="export" value="CSV Export">Export</button>
</form>
</body>
</html>
This file shows a table located in the “components” folder in a file called “table.php” and a button that allows us to export the data found within the “user” table.
If we want to see this code run, firstable we have edit the “table.php” file in the “components” folder, then we have edit “download_csv.php” file in the “functions” folder.
Write the following code in the “table.php” file.
<?php
// Include the database connection file
require_once "connection/connection.php";
// Check if the database connection was successful
if (!$db) {
echo "Connection failed";
exit;
} else {
try {
// Create five users in the users table
$sql = "SELECT * FROM `user`";
// Prepare the SQL query to add the five users
$statement = $db->prepare($sql);
// Execute the SQL query to add the five users
$statement->execute();
// Save the result in a variable named $users
$users = $statement->fetchAll(PDO::FETCH_ASSOC);
// Close the database connection
$db = null;
} catch (PDOException $e) {
// Handle any PDO exceptions and display an error message if necessary
echo $e->getMessage();
}
}
?>
<table>
<thead>
<tr>
<th>Name</th>
<th>Last name</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
<?php foreach ($users as $user): ?>
<tr>
<td><?= $user["name"] ?></td> <?php /* Show the user name*/ ?>
<td><?= $user["last_name"] ?></td> <?php /* Show the user last name*/ ?>
<td><?= $user["phone"] ?></td> <?php /* Show the user phone*/ ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>Now, write the following code in the “download_csv.php” file.
<?php
// Include the database connection file
require_once "../connection/connection.php";
// Check if the database connection was successful
if (!$db) {
echo "Connection failed";
exit;
} else {
try {
// Create five users in the users table
$sql = "SELECT * FROM `user`";
// Prepare the SQL query to add the five users
$statement = $db->prepare($sql);
// Execute the SQL query to add the five users
$statement->execute();
// Save the result in a variable named $users
$users = $statement->fetchAll(PDO::FETCH_ASSOC);
// Close the database connection
$db = null;
} catch (PDOException $e) {
// Handle any PDO exceptions and display an error message if necessary
echo $e->getMessage();
}
}
// Section to download the CSV file
// Define the filename
$filename = "users.csv";
// Define the delimiter (separating character)
$delimiter = ",";
// Create a file pointer
$f = fopen('php://memory', 'w');
// Get the column names
$fields = array_keys(current($users));
// Write the column names to the CSV file
fputcsv($f, $fields, $delimiter);
// Write all the user records to the CSV file
foreach ($users as $user) {
fputcsv($f, $user, $delimiter);
}
// Move the file pointer to the beginning of the file
fseek($f, 0);
// Set the HTTP headers to download the CSV file rather than displaying it
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
// Output all the remaining data on a file pointer
fpassthru($f);
// Close the file pointer
fclose($f);
// Stop the PHP script
exit;
?>Right now, we have completed our project, and you can open the “index.php” file in your browser.
If everything is ok, you should see something like this:
You can click on the “Export” button and you will see how the csv file with the data from the “user” table begins to download.
And that’s it!
Now, if you wish, you can create a class to handle the necessary database queries. This way, you can avoid writing the same query twice to retrieve all the users in the database.
Thanks for read my article, I hope you found my article helpful.
