Page 1 of 1

PHP, manual MySQL database backup script.

Posted: Sat Oct 22, 2011 10:55 am
by mikeashfield
Hi,

I am having some trouble with some code that I am trying to write.
Here's where I'm at so far:

Code: Select all

<?php
$con = mysql_connect("","","");
mysql_select_db("ARRIVA_DUTIES", $con);
$sql="DROP DATABASE ARRIVA_DUTIES_BACKUP";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
mysql_close($con);
?>
<?php
$con = mysql_connect("","","");
mysql_select_db("ARRIVA_DUTIES", $con);
$sql="
CREATE DATABASE `ARRIVA_DUTIES_BACKUP` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
 CREATE  TABLE  `ARRIVA_DUTIES_BACKUP`.`tbl_duties` (  `duty_id` int( 5  )  NOT  NULL  AUTO_INCREMENT  COMMENT  'Defines the duty.',
 `duty_number` varchar( 11  )  NOT  NULL  COMMENT  'The duty number as assigned by arriva.',
 `duty_type` varchar( 15  )  NOT  NULL  COMMENT  'Defines the type of duty (examples: Middle, Early, Late, Sunday, Spare).',
 `duty_start` time NOT  NULL  COMMENT  'Defines the duty start time.',
 `duty_finish` time NOT  NULL  COMMENT  'Defines the duty finish time.',
 `duty_paytime` varchar( 5  )  NOT  NULL ,
 PRIMARY  KEY (  `duty_id`  ) ,
 UNIQUE  KEY  `duty_number` (  `duty_number`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1 COMMENT  =  'Stores information related to the duties.' AUTO_INCREMENT  =124;

INSERT INTO `ARRIVA_DUTIES_BACKUP`.`tbl_duties` SELECT * FROM `ARRIVA_DUTIES`.`tbl_duties`;
 CREATE  TABLE  `ARRIVA_DUTIES_BACKUP`.`tbl_problems` (  `problem_id` int( 11  )  NOT  NULL  AUTO_INCREMENT ,
 `problem_type` varchar( 4  )  DEFAULT NULL ,
 `search_value` varchar( 15  )  DEFAULT NULL ,
 `reporter_name` varchar( 50  )  DEFAULT NULL ,
 `report_desc` varchar( 300  )  DEFAULT NULL ,
 PRIMARY  KEY (  `problem_id`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1 AUTO_INCREMENT  =1;

INSERT INTO `ARRIVA_DUTIES_BACKUP`.`tbl_problems` SELECT * FROM `ARRIVA_DUTIES`.`tbl_problems`;
 CREATE  TABLE  `ARRIVA_DUTIES_BACKUP`.`tbl_reporter_requests` (  `request_id` int( 11  )  NOT  NULL  AUTO_INCREMENT ,
 `request_timestamp` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ,
 `request_ip` varchar( 15  )  DEFAULT NULL ,
 PRIMARY  KEY (  `request_id`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1 AUTO_INCREMENT  =168;

INSERT INTO `ARRIVA_DUTIES_BACKUP`.`tbl_reporter_requests` SELECT * FROM `ARRIVA_DUTIES`.`tbl_reporter_requests`;
 CREATE  TABLE  `ARRIVA_DUTIES_BACKUP`.`tbl_requests` (  `timestamp_id` int( 11  )  NOT  NULL  AUTO_INCREMENT ,
 `auto_timestamp` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ,
 `request_ip` varchar( 15  )  DEFAULT NULL ,
 `request_type` text,
 `request_value` int( 5  )  DEFAULT NULL ,
 `request_referer` varchar( 60  )  DEFAULT NULL ,
 PRIMARY  KEY (  `timestamp_id`  ) ,
 UNIQUE  KEY  `auto_timestamp` (  `auto_timestamp`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1 AUTO_INCREMENT  =259;

INSERT INTO `ARRIVA_DUTIES_BACKUP`.`tbl_requests` SELECT * FROM `ARRIVA_DUTIES`.`tbl_requests`;
 CREATE  TABLE  `ARRIVA_DUTIES_BACKUP`.`tbl_users` (  `user_rak` varchar( 30  )  NOT  NULL ,
 `user_username` varchar( 40  )  NOT  NULL ,
 `user_password` varchar( 30  )  NOT  NULL ,
 `user_actualname` varchar( 50  )  NOT  NULL ,
 `user_email` varchar( 100  )  DEFAULT NULL ,
 PRIMARY  KEY (  `user_rak`  ) ,
 UNIQUE  KEY  `user_username` (  `user_username`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1;

INSERT INTO `ARRIVA_DUTIES_BACKUP`.`tbl_users` SELECT * FROM `ARRIVA_DUTIES`.`tbl_users`;
 CREATE  TABLE  `ARRIVA_DUTIES_BACKUP`.`tbl_weeks` (  `week_id` int( 5  )  NOT  NULL  AUTO_INCREMENT  COMMENT  'Defines the week.',
 `week_number` int( 5  )  NOT  NULL  COMMENT  'The week number as assigned by arriva.',
 `week_dutyday1` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 1 of a particular week.',
 `week_dutyday2` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 2 of a particular week.',
 `week_dutyday3` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 3 of a particular week.',
 `week_dutyday4` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 4 of a particular week.',
 `week_dutyday5` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 5 of a particular week.',
 `week_dutyday6` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 6 of a particular week.',
 `week_dutyday7` int( 5  )  DEFAULT NULL  COMMENT  'Defines the duty number  for day 7 of a particular week.',
 PRIMARY  KEY (  `week_id`  ) ,
 UNIQUE  KEY  `week_number` (  `week_number`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1 COMMENT  =  'Defines each week and its duties.' AUTO_INCREMENT  =78;

INSERT INTO `ARRIVA_DUTIES_BACKUP`.`tbl_weeks` SELECT * FROM `ARRIVA_DUTIES`.`tbl_weeks`;
";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  echo "Completed!";
mysql_close($con);
?>

But I keep hitting this error:

Code: Select all

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `ARRIVA_DUTIES_BACKUP`.`tbl_duties` ( `duty_id` int( 5 ) NOT ' at line 2
I'm pretty new to PHP and MySQL so if anybody can help that would be amazing.
Thanks in advance. :D

Re: PHP, manual MySQL database backup script.

Posted: Sat Oct 22, 2011 2:24 pm
by twinedev
Tried running the sql by hand on my server, and it ran just fine, perhaps try breaking them up to separate queries to execute instead of one huge one.

Additionally, You can clean up the code with the following:

Code: Select all

<?php

define ('LIVE_DATABASE',	'ARRIVA_DUTIES');
define ('BACKUP_DATABASE',	'ARRIVA_DUTIES_BACKUP');

$con = mysql_connect("","","");
mysql_select_db(LIVE_DATABASE, $con);

// CLEAR OUT OLD BACKUP DATABASE
mysql_query('DROP DATABASE `'.BACKUP_DATABASE.'`')
	or die ('Error: ' . mysql_error());

// CREATE NEW BACKUP DATABASE
$rsDatabase = mysql_query('CREATE DATABASE `ARRIVA_DUTIES_BACKUP` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci')
	or die ('Error: ' . mysql_error());

// LOAD UP LIST OF TABLES
$aryTables = array();	
$rsTables = mysql_query('SHOW TABLES IN `'.LIVE_DATABASE.'`')
	or die ('Error: ' . mysql_error());
if ($rsTables && mysql_num_rows($rsTables)>0) {
	while	($aryRow = mysql_fetch_row($rsTables)) {
		$aryTables[] = $aryRow[0];
	}
	mysql_free_result($rsTables);
}
unset($rsTables);

// COPY TABLES
foreach($aryTables as $strTable) {
	echo "Processing: $strTable <br>\n";

	mysql_query('CREATE TABLE `'.BACKUP_DATABASE.'`.`'.$strTable.'` LIKE `'.LIVE_DATABASE.'`.`'.$strTable.'`')
		or die ('Error: ' . mysql_error());

	mysql_query('INSERT INTO `'.BACKUP_DATABASE.'`.`'.$strTable.'` SELECT * FROM `'.LIVE_DATABASE.'`.`'.$strTable.'`')
		or die ('Error: ' . mysql_error());
}

echo "<br>\nDONE!\n";

mysql_close($con);

?>
Note, if you are not wanting to copy every table, for the block "LOAD UP LIST OF TABLES", just replace it with something like the following to list them all

Code: Select all

$aryTables = array('tbl_duties','tbl_duties','tbl_problems');
-Greg

Re: PHP, manual MySQL database backup script.

Posted: Sat Oct 22, 2011 4:07 pm
by mikeashfield
Hey,

Thanks Greg,
The SQL code was running just fine in phpMyAdmin so I knew it was something to do with the PHP code itself. Still haven't a clue where I was wrong. But the code that you gave worked perfectly! Thanks! :D


Mike