We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


OSCommerce - How to Backup your Database?

Medium Priority
Last Modified: 2013-12-12
I have noted this message in a readme file for a new OSCommerce contribution and I'd like to know what I can do about it...

"Whenever Importing any new SQL files to your Database ALWAYS do a FULL
enables a quick restore alternative if errors do occur"

I have asked my developer to create a file which will download this 'backup'.  He says he can only create a file to Export this database.  However, when I run the file (PHP) I appear to get a huge list of text saying 'Insert this'...SQL type language.  It goes on and on though I haven't left it for longer than half an hour due to site bandwidth issues.

Attached is the code in full - there appears to be no harm posting it.
Tell me what you think and how you think you might go about exporting a database so that i'm left with a dump file.

PS-> The high point allocation suggests links to external documents and examples.

function get_tables($MySQLDatabase){
	global $SQL99;
   $SQL99->SQLQuery="show tables";
	$i = 0;
	while ($SQL99->FetchRow()){
  return $res;
	$SQL->SQLQuery="desc ".$tabs[$i];
	echo "create table ".$tabs[$i]." ( \n ";
	while ($SQL->FetchRow()) {
		if ($s) echo ", ";
		echo $SQL->SQLRow["Field"]." ".$SQL->SQLRow["Type"];
		if ($SQL->SQLRow["Key"]=="PRI") echo " not null primary key ";
		echo " ".$SQL->SQLRow["Extra"];
		echo "\n";
	echo "); \n\n";
	$SQL->SQLQuery="select * from ".$tabs[$i];
	while($SQL->FetchRow()) {
		echo "insert into ".$tabs[$i]." (";
		for ($ii=0;$ii<$SQL->SQLResultNumColumns;$ii++){
			if ($ii) echo ",";
			echo $SQL->SQLColumnName[$ii];
		echo ") values (";
		for ($ii=0;$ii<$SQL->SQLResultNumColumns;$ii++){
			if ($ii) echo ",";
			echo "'".str_replace("'","''",$SQL->SQLRow[$SQL->SQLColumnName[$ii]])."'";
		echo "); \n";
//create_file("dump_files/".$filename, $ret_str);
//$ziper = new zipfile();
//$dump_buffer = $ziper -> file();
 // header('Pragma: public');
 // header('Content-type: application/zip');
//  header('Content-length: ' . strlen($ret_str));
 // header('Content-Disposition: attachment; filename="'.$zipfilename.'"');
  //echo $dump_buffer;
 // echo $ret_str;

Open in new window

Watch Question

Hi Chris,

The SQL dump you're seeing is using the MySQL way of making backups. Even the MySQL-provided application mysqldump does exactly this, regenerates the SQL statements that were used to create and populate the database in the first place, allowing for a clean restore point should the database fail. Another way of backing up MySQL data is to have a file copy of the actual storage files, but most web hotel customers have no access to these.

The biggest problem with the (unfinished-looking) script above is that it writes the dump directly to the HTTP stream (user's browser), when it could be writing it to a text file to be zipped and given for the user to download. Even safer would be to not serve the backup file through HTTP at all, but save it at the web server and make the user fetch it with a secure connection.

There are some potential flaws in the script, too. Chances are that OSCommerce isn't made of tables only - it could have underlying views or procedures that this script ignores. Potential auto_increment values are also ignored in this example. Apparently OSCommerce also comes with it's own backup tool, "Database Backup Manager".

Most web hotel service providers offer the tool PHPMyAdmin for users to manage their database - this tool has a very reliable exporting (backup) feature which can serve the backup files readily zipped, reducing bandwidth requirements (guide: http://www.siteground.com/tutorials/php-mysql/mysql_export.htm). It can't be made to make the exports with a scheduler like a custom script could, though.

Hope this gets you on the right path!

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


A well written response.

Note, I used a Curl batch file to write the response from http to a text file.  The Internet Browsers were crashing under the 32meg file
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.