how to convert a mysql table to text file in php ?

Question

What is the php script for converting a mysql table to text file? I have converted it to CSV format which opens up as a excel file. I also want the table data in a text file. How do I do it?

For converting it to csv format, I used the header data as:

$filename = "export_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");

I tried using header("Content-type: text/plain"); and used the .txt extension. It didn't work.. Someone guide me please.

Answer 1

mysqldump will create the file in CSV format for you:

mysqldump -u User1 -p -t -T/path/to/sav [database] --fields-enclosed-by=\" --fields-terminated-by=, --lines-terminated-by=\r\n --no-create-db --no-create-info

--fields-enclosed-by=\" ensures that all of your fields are encapsulated by quotations, and --fields-terminated-by=, makes those fields comma-separated.

Answer 2

what about this one - SQL (cvs)

SELECT          *  
FROM            TABLE1 
INTO            OUTFILE 'path\file.cvs' 
                FIELDS TERMINATED BY '\,' 
                OPTIONALLY ENCLOSED BY '\"'
                LINES TERMINATED BY '\n'

If you want plain text -

SELECT          *  
FROM            TABLE1 
INTO            OUTFILE 'path\file.sql'

mysql doc

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the file name.

Answer 3

A .csv file is a plain text file, you're just sending headers that cause it to be opened in Excel.

If you want the same format file, but not opened in Excel, just do not send these headers:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");

And instead send this one:

header('Content-Type: text/plain');

If you want to force the text-file download, try this:

header('Content-Type: application/force-download');

Answer 4

I found a way to save my table in text format, saved as a ms word application with .doc extension. And the headers are given below

header("Content-Type: application/msword");
header("Content-disposition: attachment" . date("Y-m-d") . ".doc");
header("Content-disposition: filename=".$filename.".doc");

But now I'm trying to display it in a table format in the text file.. Because it looks like junk data without any alignment. Adding tab space doesn't help.. Any suggestions?