Export the MySQL database table as CSV format using PHP | 2my4edge

22 July 2013

Export the MySQL database table as CSV format using PHP

Export the database as in the format of CSV to open it in the Microsft office (MS)-Excel. Most the social media sites and E-commerce site and more sites or this kind of database export coding for without going to admin panel, just in one click export the database table as in the format of CSV to open it in MS-Excel. Just on a click export the database table, using PHP code. let see how it works.

Export Database Table

DOWNLOAD               LIVE DEMO

This operation need some PHP code and header code make the operation successful from the browser, that's why we have to use the header code in this. and this code need some files those files are,
  1. db.php
  2. export.php
  3. index.php
as in the db.php file, make this.

database name --> 2my4edge
table name--> export_table
column names --> id,name,place

and as you, here i just declare what i have given in.

DB.PHP

<?php
$conn = mysql_connect('localhost', 'root', '') or die(mysql_error());
$db=mysql_select_db('2my4edge', $conn) or die(mysql_error());
?>

EXPORT.PHP

<?php
include('db.php');

//header to give the order to the browser
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=exported-data.csv');

//select table to export the data
$select_table=mysql_query('select * from export_table');
$rows = mysql_fetch_assoc($select_table);

if ($rows)
{
getcsv(array_keys($rows));
}
while($rows)
{
getcsv($rows);
$rows = mysql_fetch_assoc($select_table);
}

// get total number of fields present in the database
function getcsv($no_of_field_names)
{
$separate = '';


// do the action for all field names as field name
foreach ($no_of_field_names as $field_name)
{
if (preg_match('/\\r|\\n|,|"/', $field_name))
{
$field_name = '' . str_replace('', $field_name) . '';
}
echo $separate . $field_name;

//sepearte with the comma
$separate = ',';
}

//make new row and line
echo "\r\n";
}
?>

INDEX.PHP

this file is just for do the download, just give the link anchor tag, usually you know to make just click export the data, for that just give the link on anchor link, that will export the data table.
<a href="export.php"> export the database table </a>

as given in the above line. just do it as i mentioned the above.





RELATED POSTS:

37 comments:

  1. good script its works perfect...

    ReplyDelete
  2. based on ur script ur csv only preview all the data from ur database, right? but, how about if the user want to view or read the file of csv only one field such as place = India?

    ReplyDelete
  3. Just modify ur select query..

    ReplyDelete
  4. Great script , work like a charm

    ReplyDelete
  5. thank u so much .... :) :) :)

    ReplyDelete
  6. How can i save export the file in "Read-only" file?by the way, thank you for this. very useful. ^_^

    ReplyDelete
  7. Thank you. Simple code, written like a pro. Would you be able to tell me how I can export multiple tables with a single click.

    ReplyDelete
  8. ty for this beautiful code..can i use this same when accessing export.php file from putty that will automatically save this exported.csv file in a folder in ftp?

    ReplyDelete
  9. does it work with all characters?

    ReplyDelete
  10. Does it work with Khmer Unicode character, UTF8?

    ReplyDelete
  11. No work, in excel appears html code

    ReplyDelete
  12. Great tutorial! Would it be possible to mark the records as exported when the xml is generated and only export records that have not been exported on each run.

    Additionally, could the column names be defined in the xml differently than what is defined in the mysql table.

    ReplyDelete
  13. str_replace('', $field_name) missing parameter line 33

    ReplyDelete
    Replies
    1. $field_name = '' . str_replace(',',' ', $field_name) . '';

      Delete
  14. i need export the mysql db as csv , Split and download csv part by part

    ReplyDelete
    Replies
    1. Export MySQL Table Data into Excel Sheet Format in PHP http://goo.gl/R1SMBk

      Delete
  15. i need mysql db as csv to download

    ReplyDelete
  16. good but it work if you need data for diferent, tablet in the same database.

    ReplyDelete
  17. Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\fairy\connection.php:2) in C:\wamp\www\fairy\export.php on line 5

    Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\fairy\connection.php:2) in C:\wamp\www\fairy\export.php on line 6

    ReplyDelete
  18. Suppose I have some ids form other tables then exporting one particular table will print those ids not the values corrosponding to those ids.

    ReplyDelete
  19. Hi Arunkumar thanks to your code, its work great!! i have tested on my energy meter logger on my web. and now it can download to cvs format :D

    ReplyDelete
  20. thanks alot very simple explain code gooooood

    ReplyDelete
  21. Thank you very much!!!!

    ReplyDelete
  22. too simple n workable code.....

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. apostrophe in data causing trouble, how should I avoid that?

    ReplyDelete
  25. Warning: str_replace() expects at least 3 parameters, 2 given in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC11\data\localweb\projects\export-csv\export.php on line 38


    ReplyDelete
  26. Dear Arunkumar,
    Thank you. It works beautifully, without any hassle. GREAT!

    ReplyDelete
  27. Thank arunkumar for you post Export the MySQL database table as CSV format using PHP, I had try and gool...

    ReplyDelete
  28. Thanks alot for the code, it work perfect like a charm!!!

    ReplyDelete
  29. can you make more tutorial on mysqli.
    Thanks

    ReplyDelete
  30. to create a database or export to a website that is very xylophone, just that we need to learn, because we also learn the language program.
    example of a table that I created in HERE

    ReplyDelete

^