# [web] Dumping a database to a text file

This topic is 4647 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

Hello, I'm trying to dump a copy of my database to a plain text file using MySql and PHP. I researched the various methods of doing it. The first method I tried was the mysql dump command using exec. However, when I tried this I got a "Unable to fork error". I then tried to use the command "SELECT * INTO OUTFILE '/temp/database.txt'", it says "Access denied" for the username and password that I am using. Now I assume both errors that I'm getting for these two ways are permissions related but I checked the permissions on the server and they should be set right. My question is, is there a way to fix those errors? Or is there a better way to do it?

##### Share on other sites
Where is the database? On a webhost or on a server you own? If the former, check the cpanel of your site. It usually provides a way to download a backup. If the latter, use the mysqldump command, or simply copy the files in the mysql directory (not sure if the latter is textual). You can also install phpMyAdmin (www.phpmyadmin.net) and use the export functionality.

##### Share on other sites
What I need is a way to allow certain other people to go to the .php page that will allow them to save the database to a text file. I don't need it for a backup, just as a way to allow some other people to get the database. I can't use phpAdmin either. Also, is there any other alternatives to mysqldump because if possible I would prefer not to have to use that way because of the security issues.

##### Share on other sites
You could simply loop through all the tables, then loop through all the columns and subsequently each row in a table and output that into a buffer, then output it. That's how phpmyadmin creates it's exports as well.

You could also get phpmyadmin, rip out the export code and modify it to suit your needs. The GPL is a wonderfull thing [smile]

##### Share on other sites
So using the command "SELECT * INTO OUTFILE" won't work for what I need?

##### Share on other sites
SELECT *
INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM table_to_dump;

Is what I use to dump a database table. It dumps to a CSV file that I can load in Excel. Make sure you have write permissions to where you are creating the outfile.

##### Share on other sites
alternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...

$sql = "SELECT * FROM tbl_to_export";$result = mysql_query($sql); while($data = mysql_fetch_array($result)) { echo$data["column1"] . "," . $data["column2"] ....... "\n"; } #### Share this post ##### Link to post ##### Share on other sites Quote:  Original post by Anonymous Posteralternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...$sql = "SELECT * FROM tbl_to_export";$result = mysql_query($sql);while($data = mysql_fetch_array($result)){ echo $data["column1"] . "," .$data["column2"] ....... "\n";}

That's what I am currently doing. However I would like to actually be able to save it as a text file. Is there a way to take the text in an HTML file and output it to a text file?

##### Share on other sites
Quote:
Original post by Third
Quote:
 Original post by Anonymous Posteralternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...$sql = "SELECT * FROM tbl_to_export";$result = mysql_query($sql);while($data = mysql_fetch_array($result)){ echo$data["column1"] . "," . $data["column2"] ....... "\n";} That's what I am currently doing. However I would like to actually be able to save it as a text file. Is there a way to take the text in an HTML file and output it to a text file? Simply select file | Save As in your browser, and select the file type as text. Even if you save as an HTML file it wont make any difference, as the file wont contain any HTML markup, it will be 100% pure text but with an .htm extension. BTW the prev Anon Poster was me.... EDIT to add: An HTML file is just pure text, but with HTML formatting tags for the browser to interpret. As your echo statement doesnt echo any HTML, the resulting page will be pure text. #### Share this post ##### Link to post ##### Share on other sites Quote: Original post by Third Quote:  Original post by Anonymous Posteralternatively, why not just write a simple PHP script that echos all the data to the screen, you can then format it as you like, either as raw data comma seperated, or as an SQL INSERT, whatever you like...$sql = "SELECT * FROM tbl_to_export";$result = mysql_query($sql);while($data = mysql_fetch_array($result)){ echo $data["column1"] . "," .$data["column2"] ....... "\n";}

That's what I am currently doing. However I would like to actually be able to save it as a text file. Is there a way to take the text in an HTML file and output it to a text file?

have PHP write it to a temporary text file on the server than forward the user to a download location....

Or even better, google an existing PHP zip file class than have the server dump the tables into a formatted string(that you'd write to a text file or whatnot in any format you'd like) than zip the file using the zip class and send the header information that this is a zip file and echo the zip file the zip class makes for you...

Theres a bunch of ways

1. 1
Rutin
37
2. 2
3. 3
4. 4
5. 5

• 11
• 10
• 13
• 104
• 11
• ### Forum Statistics

• Total Topics
632978
• Total Posts
3009681
• ### Who's Online (See full list)

There are no registered users currently online

×