MySQL dump file

tomwerner

I am trying to create an admin panel for a site and in particular one of the features is similar to the backup/restore option in phpBB if anyone has used it. Anyway I have got this code snippet with the appropriate SQL INSERT syntax:

while ($myrow = mysql_fetch_row($result)) {
printf("INSERT INTO articles (Title,Author,Date,Blurb,Location) VALUES (%s,%s,%s,%s,%s);\n\n",
$myrow[1], $myrow[2], $myrow[3], $myrow[4], $myrow[5]);

this is what I have been using to test the information that is printed out. What I would like to know is how can I put all of the text into a variable so that I can then it write to a file. Does the code need to be altered to make it usble and viable to be put into a file?

This is what gets printed out so far:

INSERT INTO articles (Title,Author,Date,Blurb,Location) VALUES (Building Work Continues,Tom Werner,11-01-2003,Blurb,hg774);

INSERT INTO articles (Title,Author,Date,Blurb,Location) VALUES (New Term Starts,Tom Werner,08-01-2003,BLurb,eib8636);

Which is fine to be used to restore a DB at a later date because the table structure will always be the same!

Before someone says it:

The webhost does not have shell access
I don't want to use PHPMyAdmin because I need to make this thing as idiot proof as possible

Any suggestions?

nnichols

This is a generic script that I use to backup individual tables. It outputs a sql file and asks you to download it.

<?

// enter the db details here
$host = '';
$user = '';
$pswd = '';
$dbname = '';
$tbl_name = '';

// connect to server
$db = mysql_connect($host, $user, $pswd) or die('Failed to connect to db server. Error: ' . mysql_error());
mysql_select_db($dbname) or die('Failed to select db. Error: ' . mysql_error());

// get all data from table $tbl_name
//
$sql = "SELECT * FROM $tbl_name";
$res = mysql_query($sql, $db);

$num_fields = mysql_num_fields($res);
$num_rows = mysql_num_rows($res);
$j = 0;

$output = "INSERT INTO $tbl_name VALUES \n";
while ($row = mysql_fetch_row($res)) {

if ($j == 0)
	$output .= ' (';
else
	$output .= ', (';
$j++;

for ($i = 0; $i < $num_fields; $i++) {
	if ($i == 0)
		$output .= '\'' . $row[$i] . '\'';
	else
		$output .= ', \'' . $row[$i] . '\'';
	}

if ($j == $num_rows)
	$output .= ");\n";
else
	$output .= ")\n";
}
$output .= "\n\n";
//
// end of building insert

// We'll be outputting a plain text file
header("Content-type: text/plain");

// It will be called nick_dev_dump.sql
header("Content-Disposition: attachment; filename=$tbl_name.sql");

// prevent caching
header("Pragma: no-cache");
header("Expires: 0");

echo $output;

?>

Hope this helps 😉

tomwerner

Thanks I couldn't find anything similar on all the usual sites, I'll try it tonight. Thanks 🙂

apepelis

then just wrap the code in this

$result = mysql_list_tables($dbname);

if (!$result) {
    print "DB Error, could not list tables\n";
    print 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
   //insert the append to output here
}

and you can go through all your tables and dump them to a file. This is especially good if your host limits html traffic on your site, but not FTP data (as it the case with my host).