PHP, manual MySQL database backup script.
Posted: Sat Oct 22, 2011 10:55 am
Hi,
I am having some trouble with some code that I am trying to write.
Here's where I'm at so far:
But I keep hitting this error:
I'm pretty new to PHP and MySQL so if anybody can help that would be amazing.
Thanks in advance.
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 2Thanks in advance.