Create a CSV file using PHP/MYSQL

Sep 16, 2013, by admin

As we know that CSV files are one of the most used way to back-up mysql database and tables. However it is very simple to export or backup your mysql database table as CSV file if you have PhpMyAdmin interface installed on your server. Actually problem arises when we have to deal with a shared hosting. But by using PHP script, you can export complete mysql database table to csv files. Here in this post I have given you a simple PHP script to create a CSV file. The script will call the database and select a table. Then it downloads the content line by line in a csv file.

1. <?php
2. $DBHOST = ‘localhost’;
3. $DBUSER = ‘root’;
4. $DBPASS = ”;
5. $DATABASE = ‘mytestdatabase’;
6. $TABLE = ‘emp’;
7. $FILE_NAME = ‘emp.csv’;
9. $dblink = mysql_connect($DBHOST, $DBUSER, $DBPASS) or die(“Can not connect.” . mysql_error());
10. mysql_select_db($DATABASE) or die(“Can not connect.”);
12. $result = mysql_query(“SHOW COLUMNS FROM ”.$TABLE.“”);
13. $i = 0;
15. if (mysql_num_rows($result) > 0) {
16. while ($row = mysql_fetch_assoc($result)) {
17. $my_csv_output .= $row[‘Field’].“,”;
18. $i++;}
19. }
20. $my_csv_output .= “n”;
21. $values = mysql_query(“SELECT * FROM ”.$TABLE.“”);
23. while ($rowr = mysql_fetch_row($values)) {
24. for ($j=0;$j<$i;$j++) {
25. $my_csv_output .= $rowr[$j].“, ”;
26. }
27. $my_csv_output .= “n”;
28. }
30. $FILE_NAME = $FILE_NAME.“_”.date(“d-m-Y_H-i”,time());
32. header(“Content-type: application/vnd.ms-excel”);
33. header(“Content-disposition: csv” . date(“Y-m-d”) . “.csv”);
34. header( “Content-disposition: filename=”.$FILE_NAME.“.csv”);
36. print $my_csv_output;
37. exit;
38. ?>