- Drop me a line! Contact me here.
Generating a CSV file using MySQL
Generating a CSV file using MySQL
Generating a CSV file is a pretty common task when you're building a site that allows users to add data to the site database. They might register, or add comments to things, or anything really. Every so often someone will want to export that data to a different app, Excel for example. CSV is a simple solution.
Actually generating the CSV file on the other hand, while easy, is a boring task in PHP. You need to open the database connection, select the right data, fopen a file to save it or send headers to force a downlown, lloop through the selected data formatting it and outputing it somehow - lots of hassle. A much simpler approach is to get MySQL to do all the work for you.
The first thing to do is to create an SQL SELECT statement that grabs all the data you need in the CSV. A simple example would be something like:
Once you have a data set that works you need to tell MySQL to put the data into a file rather than sending it to the query buffer. This is done with the INTO syntax.
The added line "INTO OUTFILE 'path/to/csv/users.csv'" tells MySQL to save the data into a file. You need to be careful of two things: first that the path is correct, and second that the file doesn't already exist otherwise the query will fail.
This isn't going to generate a proper CSV file yet though. We also need to tell MySQL how to format the data. Fortunately there are a couple of simple functions of the SELECT query, FIELDS and LINES, that can do all the work.
TERMINATED BY simply means 'put this character after the data or line. OPTIONALLY ENCLOSED BY tells MySQL to put quotation marks around any data that contains characters that could break the file such as new lines.
Lastly you may need to add a line for field names at the top. This can be achieved with a UNION.
That's all there is to it.
Excel Note: A common problem is that Excel will refuse to open a CSV file is the first two characters in the file are 'id'. If your first column is called 'id' and you're including a line for field names you'll need to call it something else.
Actually generating the CSV file on the other hand, while easy, is a boring task in PHP. You need to open the database connection, select the right data, fopen a file to save it or send headers to force a downlown, lloop through the selected data formatting it and outputing it somehow - lots of hassle. A much simpler approach is to get MySQL to do all the work for you.
The first thing to do is to create an SQL SELECT statement that grabs all the data you need in the CSV. A simple example would be something like:
SELECT `users`.`last_name`,
`users`.`first_name`,
`users`.`email_address`,
DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),
IF (`users`.`registered`=1, 'Yes', 'No')
FROM `users`
WHERE `users`.`active` = 1
ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASCOnce you have a data set that works you need to tell MySQL to put the data into a file rather than sending it to the query buffer. This is done with the INTO syntax.
SELECT `users`.`last_name`,
`users`.`first_name`,
`users`.`email_address`,
DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),
IF (`users`.`registered`=1, 'Yes', 'No')
INTO OUTFILE 'path/to/csv/users.csv'
FROM `users`
WHERE `users`.`active` = 1
ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASCThe added line "INTO OUTFILE 'path/to/csv/users.csv'" tells MySQL to save the data into a file. You need to be careful of two things: first that the path is correct, and second that the file doesn't already exist otherwise the query will fail.
This isn't going to generate a proper CSV file yet though. We also need to tell MySQL how to format the data. Fortunately there are a couple of simple functions of the SELECT query, FIELDS and LINES, that can do all the work.
SELECT `users`.`last_name`,
`users`.`first_name`,
`users`.`email_address`,
DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),
IF (`users`.`registered`=1, 'Yes', 'No')
INTO OUTFILE 'path/to/csv/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM `users`
WHERE `users`.`active` = 1
ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASCTERMINATED BY simply means 'put this character after the data or line. OPTIONALLY ENCLOSED BY tells MySQL to put quotation marks around any data that contains characters that could break the file such as new lines.
Lastly you may need to add a line for field names at the top. This can be achieved with a UNION.
SELECT 'Last Name','First Name','Email Address','Date','Registered'
UNION
SELECT `users`.`last_name`,
`users`.`first_name`,
`users`.`email_address`,
DATE_FORMAT(`users`.`created_date`, '%d/%m/%Y'),
IF (`users`.`registered`=1, 'Yes', 'No')
INTO OUTFILE 'path/to/csv/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM `users`
WHERE `users`.`active` = 1
ORDER BY `users`.`last_name` ASC, `users`.`first_name` ASCThat's all there is to it.
Excel Note: A common problem is that Excel will refuse to open a CSV file is the first two characters in the file are 'id'. If your first column is called 'id' and you're including a line for field names you'll need to call it something else.
Comments
Comments are not currently being accepted for this article.
Sidebar
Published:
23/06/2007
Views:
3296
Author:
Chris Neale
Labels:
Print:
Sidebar:
- © Ooer.com Chris Neale 2007
- PHPGD.com
- Powered by PHP
- Database by MySQL
- DB Queries: 7
- DB Time: 0.0558 seconds
