How to Export MySQL to CSV in PHP

CSV files can be opened in all spreadsheet productivity programs.

By using PHP, exporting MySQL data to a CSV file is quick and straightforward. MySQL is an open source database server program. PHP is a server side scripting language that allows programmers to create dynamically generated Web pages. PHP was designed to interact with MySQL databases and is ideal for creating, manipulating or removing data. By downloading database tables to CSV files, you can access the data offline, back up your information or manipulate it with spreadsheet programs.

Things You'll Need

• MySQL database
• Text editor
• Website with PHP capabilities

Instructions

1. Open a text editing program and create a new document.

2. Type the following code into the document:

   <?php

   function exportMysqlToCsv($table,$filename = 'content.csv') {

   $csv_terminated = "\n";

   $csv_separator = ",";

   $csv_enclosed = '"';

   $csv_escaped = "\\";

   $query = "select * from $table";

   $result = mysql_query($query);

   $rowcount = mysql_num_fields($result);

   $data = '';

   for ($i = 0; $i < $rowcount; $i++) {

   $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,

   stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;

   $data .= $l;

   $data .= $csv_separator; }

   $output = trim(substr($data, 0, -1));

   $output .= $csv_terminated;

   while ($row = mysql_fetch_array($result)) {

   $data = '';

   for ($j = 0; $j < $fields_cnt; $j++) {

   if ($row[$j] == '0' || $row[$j] != '') {

   if ($csv_enclosed == '') { $data .= $row[$j]; }

   else { $data .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } }

   else { $data .= ''; }

   if ($j < $rowcount - 1) { $data .= $csv_separator; } }

   $output .= $schema_insert;

   $output .= $csv_terminated; }

   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

   header("Content-Length: " . strlen($output));

   // Output to browser with appropriate mime type, you choose ;)

   header("Content-type: text/x-csv");

   //header("Content-type: text/csv");

   //header("Content-type: application/csv");

   header("Content-Disposition: attachment; filename=$filename");

   echo $output;

   exit;

   } ?>

3. Save the file as "exportcsv.php."

4. Create a new text document.

5. Type the following code into the document:

   <?php

   $host = 'localhost';

6. Enter "$db = '<db name>';" and replace <db name> with the name of your database.

7. Enter "$user = '<uname>';" and replace <uname> with the username you use to access your database.

8. Enter "$pass = '<pass>';" and replace <pass> with the password you use to access your database.

9. Type the following code into the document:

   $link = mysql_connect($host, $user, $pass);

   mysql_select_db($db);

   require 'exportcsv.php';

10. Enter "$table="<tablename>";" and replace <tablename> with the name of the table you want to export from the database.

11. Type the following code into the document:

    exportMysqlToCsv($table);

    ?>

12. Save the file as "export.php."

13. Upload the two files you created to your Web server.

14. Open the "export.php" file in a Web browser.

Tips & Warnings

• Any text editing program will work, but an IDE program built for PHP will be more user-friendly. Learning some HTML code will allow you to create a form so you can input a new table name without having to edit the page each time.

• If you upload the files to a publicly accessible area, make sure to remove them when you're done. If you leave them, someone else can download your data.

References

• PHP: Mysql_connect
• Creativyst Software: The Comma Separated Value (CSV) File Format
• W3schools.com: PHP Header() Function
• PHP: For

Resources

• YouTube: How to Export a MySQL Database Using PHPMyAdmin
• Openoffice.org: Open CSV Files