View Full Version : Exporting database content with PHP

02-05-2010, 01:10 PM

Basically what I want to do is have an automated back up of each table within my database, on a daily basis.

I'm pretty sure this is possible, but I am having trouble writing a script to do so... From what I can gather the script will need to do the following...

1) Use a select statement to select all data from the table in question
2) Take all the data and add it to a string
3) Write the data to a text file

I can do step one no problem (the easiest bit I know) but I have no idea how to do the other steps. Can anyone advise here?


[Paul Ferrie ]
02-05-2010, 01:22 PM
I cant help automating it but i can help with the script that pulls the table data and store it's in an external xls file.

$dbHost = "localhost";
$dbUser = "username";
$dbPass = "password";
$dbName = "db_1"

$link = mysql_connect($dbHost, $dbUser, $dbPass);
if (!mysql_select_db($dbName)) {
echo "Couldnt find database";
$query = "SELECT name, company, address, email, submitted, question, fsa FROM questions";
$result = mysql_query($query);
if(!$result) {
echo "read error";
$count = mysql_num_fields($result);
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
$line .= $value;
$data .= trim($line)."\n";
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);

# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
$fname = "database_entries.xls";
header("Content-Disposition: attachment; filename=$fname");
header("Pragma: no-cache");
header("Expires: 0");
echo $header."\n".$data;

This is not my code but like yourself needed to pull and store info from the database in an external file.

Hope it's of some help

02-05-2010, 01:42 PM
Hi, Many thanks for that...

As for automating, our hosting provider provides a tool called CRON JOBS which apparently will take the PHP and run it when specified, should I should be OK there.

Just one questions regarding this code...

It's saving it as an excel file right? Also where does it save it to?

[Paul Ferrie ]
02-05-2010, 01:47 PM
ah this script prompts you to download the file.

I will see if i can do a quick mode so it saves to a location on your server.

[Paul Ferrie ]
02-05-2010, 02:26 PM
Sorry time is getting away from me today.
I did a quick search and found this (http://www.999tutorials.com/php/create-excel-files-with-php/)
Hopefully there is enough there to get you moving in the right direction a bit quicker.

02-05-2010, 02:33 PM
Thanks for that, I'll check it out!

Came across a few scripts since then to be honest...

I think the problem will be trying to run it as a CRON Job, since I have no knowledge of CRON commands!

thanks once again

[Paul Ferrie ]
02-05-2010, 02:38 PM
Check this (http://www.htmlcenter.com/blog/running-php-scripts-with-cron/). I was up to speed in a couple of minutes

02-05-2010, 02:43 PM
;919079']Check this (http://www.htmlcenter.com/blog/running-php-scripts-with-cron/). I was up to speed in a couple of minutes

I've literally just finished reading that, lol...

Giving it a go now

[Paul Ferrie ]
02-05-2010, 02:44 PM
I've literally just finished reading that, lol...

Giving it a go now

Let me know how you get on. Not got time to test myself.

02-05-2010, 02:45 PM
I get one of two results:

1) /bin/sh: /dev/null2: Permission denied
2) /bin/sh: /: Is a directory

but I think this has gone beyond the scope of PHP

02-05-2010, 04:39 PM
To update, this script seems to work fine with my Cron job but does anyone know how it can be altered so it saves as either a xls or csv and is sent to a specified email account, kinda like a form - mail script?

$host = 'localhost';
$user = 'myuser';
$pass = 'mypass';
$db = 'mydb';
$table = 'my tbl';
$file = '$table_export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].", ";
$csv_output .= "\n";

$filename = $file."_".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");
print $csv_output;