Tech Chorus

Do you want to export data stored in a MySQL database to CSV file?

The solution is damn easy if you already know how to connect to MySQL database and read or display data from a PHP script. Let's start working on it.

Let us first create a sample table. In our example scenario, let us create a table to store contact information.

CREATE TABLE `contacts` (
`first_name` VARCHAR( 50 ) NOT NULL ,
`middle_name` VARCHAR( 50 ) NOT NULL ,
`last_name` VARCHAR( 50 ) NOT NULL ,
`email` VARCHAR( 320 ) NOT NULL ,
`phone` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM;

In our MySQL database table 'contact' we store first name, middle name, last name, email address and phone number. Time to insert sample data to our table.

INSERT INTO `contacts` (
`first_name` ,
`middle_name`,
`last_name`,
`email`,
`phone`
)
VALUES 
('Brendan','Deleon','Thane','egestas.Sed@convallisest.ca','1-668-404-1712'),
('Thaddeus','Herrera','Upton','pede@purussapiengravida.ca','1-813-414-5014'),
('Kibo','Mcintosh','Chester','neque@interdum.com','1-570-799-1809'),
('Palmer','Branch','Reuben','diam.luctus@Morbi.com','1-743-889-4016'),
('Cain','Price','Timothy','felis.orci.adipiscing@faucibuslectusa.com','1-952-159-0353'),
('Jasper','Hebert','Noah','Phasellus@consectetuer.ca','1-148-209-9963');

We just inserted 6 records to our contacts table.

Time to write the PHP script. Let's do it step by step to understand the process better.

Step 1: Establish connection to database server and select the database.

Step 2: Query the database and store the result in $result.

Step 3: We accumulate the contents of CSV in the variable $file. We set it to null value to make sure it is empty. We fetch the data from the query result and loop through the rows. After each column value is appended to $line, we also append the ','. We separate the values by comma. At the end of each line we append the newline character. The PHP constant PHP_EOL makes our script cross platform compatible. Once the control comes out of our loops, the CSV content is stored in $file.

Step 4: We set the appropriate HTTP headers. The 'Content-Type' header indicates that the data is in CSV format. The 'Content-disposition' header indicates to the browser that the data has to be offered as a file download. Once we set the headers, we just echo our $file variable which contains the CSV data.