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.
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,
- db.php
- export.php
- 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:
RELATED POSTS:
good script its works perfect...
ReplyDeletebased 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?
ReplyDeleteJust modify ur select query..
ReplyDeleteGreat script , work like a charm
ReplyDeletethank u so much .... :) :) :)
ReplyDeleteHow can i save export the file in "Read-only" file?by the way, thank you for this. very useful. ^_^
ReplyDeleteThank you. Simple code, written like a pro. Would you be able to tell me how I can export multiple tables with a single click.
ReplyDeletety 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?
ReplyDeletedoes it work with all characters?
ReplyDeleteDoes it work with Khmer Unicode character, UTF8?
ReplyDeleteNo work, in excel appears html code
ReplyDeleteGreat 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.
ReplyDeleteAdditionally, could the column names be defined in the xml differently than what is defined in the mysql table.
str_replace('', $field_name) missing parameter line 33
ReplyDelete$field_name = '' . str_replace(',',' ', $field_name) . '';
Deletei need export the mysql db as csv , Split and download csv part by part
ReplyDeleteExport MySQL Table Data into Excel Sheet Format in PHP http://goo.gl/R1SMBk
Deletei need mysql db as csv to download
ReplyDeletegood but it work if you need data for diferent, tablet in the same database.
ReplyDeleteWarning: 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
ReplyDeleteWarning: 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
Suppose I have some ids form other tables then exporting one particular table will print those ids not the values corrosponding to those ids.
ReplyDeleteHi 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
ReplyDeletethanks alot very simple explain code gooooood
ReplyDeleteThank you very much!!!!
ReplyDeletetoo simple n workable code.....
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks, it's work for me!
ReplyDeleteThanks a lot!
ReplyDeleteapostrophe in data causing trouble, how should I avoid that?
ReplyDeleteWarning: 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
ReplyDeleteThanks a lot. Nice work
ReplyDeleteDear Arunkumar,
ReplyDeleteThank you. It works beautifully, without any hassle. GREAT!
Thank arunkumar for you post Export the MySQL database table as CSV format using PHP, I had try and gool...
ReplyDeleteThanks alot for the code, it work perfect like a charm!!!
ReplyDeletecan you make more tutorial on mysqli.
ReplyDeleteThanks
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.
ReplyDeleteexample of a table that I created in HERE
thanks for tutorial
ReplyDeletegreats! i would to do
ReplyDeleteProfessional developer & Property management