In this tutorial I am going to teach How to Export MySQL Table data to CSV in PHP.
A CSV is a comma separated values file which allows data to be saved in a table structured format.
Here I will be using well known PHP function fputcsv to create the CSV file. Basically the data will be stored in MySQL tables and we will convert this data to CSV format.
I just divided the tutorial to some steps to understand well
1) MySql table structure, it is just a demo employee table.
CREATE TABLE `employee` ( `id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, `job_title` varchar(100) DEFAULT NULL, `salary` double DEFAULT NULL, `notes` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee` (`first_name`, `last_name`, `job_title`, `salary`) VALUES ('Robin', 'Jackman', 'Software Engineer', 5500), ('Taylor', 'Edward', 'Software Architect', 7200), ('Vivian', 'Dickens', 'Database Administrator', 6000), ('Harry', 'Clifford', 'Database Administrator', 6800), ('Eliza', 'Clifford', 'Software Engineer', 4750), ('Nancy', 'Newman', 'Software Engineer', 5100), ('Melinda', 'Clifford', 'Project Manager', 8500), ('Harley', 'Gilbert', 'Software Architect', 8000);
2) MySql connection
Create a file called Config.php, and this will initiate the database connection from this file.
$dbhost = "localhost"; /* Host name */ $dbuser = "root"; /* User */ $dbpass = ""; /* Password */ $dbname = "sample_tables"; /* Database name */ $db = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname) or die("Unable to connect to DB server");
3) Listing of all data from Database
<?php include "config.php"; $query = "SELECT * FROM employee"; $result = mysqli_query($db , $query); $user_arr = array(); ?> <table cellspacing="0" cellpadding="0" border="1" class="table"> <?php while($row = mysqli_fetch_array($result)):?> <tr> <td> <?php print $row['id']?> </td> <td> <?php print $row['first_name']?> </td> <td> <?php print $row['last_name']?> </td> <td> <?php print $row['job_title']?> </td> <td> <?php print $row['salary']?> </td> </tr> <?php endwhile;?> </table> <a href="download.php" class="btn btn-default text-center" >Download</a>
4) Create and Download CSV file
We have created a file called download.php, from here you can download the CSV file. When you click on the Download button, this file will be called and you will prompted to download the CSV file.
Withing fputcsv you need to pass two parameters 1) file handle 2) the source array.
<?php include "config.php"; $filename = 'employee.csv'; $query = "SELECT * FROM employee"; $result = mysqli_query($db , $query); $user_arr = array(); // file creation $file = fopen($filename,"w"); while($row = mysqli_fetch_assoc($result)) { fputcsv($file, $row); } fclose($file); // download header("Content-Description: File Transfer"); header("Content-Disposition: attachment; filename=$filename"); header("Content-Type: application/csv; "); readfile($filename); // deleting file unlink($filename); exit();
So you have learned how to export data from MySQL table to CSV file.
- Just want to thank us? Buy us a Coffee
- May be another day? Shop on Amazon using our links.
Your prices won't change but we get a small commission.
Leave a Reply