Jump to content
  • Advertisement
Sign in to follow this  
Third

[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.

If you intended to correct an error in the post then please contact us.

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 this post


Link to post
Share on other sites
Advertisement
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 this post


Link to post
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.

Thanks for the reply.

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites
Guest Anonymous Poster
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 Poster
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";
}



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 this post


Link to post
Share on other sites
Quote:
Original post by Third
Quote:
Original post by Anonymous Poster
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";
}




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 Poster
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";
}



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

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!