cjgwin

@Muddy_Funster genius! I didn't realise that would have an impact! Each SQL statement is now stored in $sql[x] and run in a foreach(). It also looks much cleaner this way I'll remember this for future reference. Thanks man!

Hi guys, I'm having a really frustrating problem with some PHP and MySQL. I've tested the MySQL in PHPMyAdmin and it runs fine, which is why I've posted this in PHP problems. All I want to do is copy one table to another (dropping the other first) as a backup, then recreate the original table but blank. I've tried running the functions in PHPMyAdmin, which works. Then I used its "Create PHP Code" function to make the PHP, which I edited to remove line breaks etc. Even if I used the unedited PHP made by PHPMyAdmin, it still doesn't work! The error is always a syntax one: "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 mobackup (campaign_id varchar(20) NOT NULL, number varchar(13) NOT ' at line 1" Here's the code: <?php $connection = mysql_connect(connection details hidden for security purposes, but they definitely work); mysql_select_db("betfredpiri", $connection); $sql = "DROP TABLE IF EXISTS mobackup; CREATE TABLE mobackup (campaign_id varchar(20) NOT NULL, number varchar(13) NOT NULL, message varchar(160) NOT NULL, mo_id int(15) NOT NULL, shortcode int(7) NOT NULL, received_time varchar(20) NOT NULL, network varchar(15) NOT NULL, retry tinyint(1) NOT NULL, FULLTEXT KEY message (message)) ENGINE = MyISAM DEFAULT CHARSET = latin1; INSERT INTO mobackup SELECT * FROM mo; DROP TABLE IF EXISTS mo; CREATE TABLE mo (campaign_id varchar(20) NOT NULL, number varchar(13) NOT NULL, message varchar(160) NOT NULL, mo_id int(15) NOT NULL, shortcode int(7) NOT NULL, received_time varchar(20) NOT NULL, network varchar(15) NOT NULL, retry tinyint(1) NOT NULL, FULLTEXT KEY message (message)) ENGINE = MyISAM DEFAULT CHARSET = latin1;"; mysql_query(mysql_real_escape_string($sql)); ?> I've also tried it with the following statements: <?php $sql2 = "DROP TABLE IF EXISTS mobackup;" ."CREATE TABLE mobackup (campaign_id varchar(20) NOT NULL, " ."number varchar(13) NOT NULL, " ."message varchar(160) NOT NULL, " ."mo_id int( 15 ) NOT NULL, " ."shortcode int(7) NOT NULL, " ."received_time varchar(20) NOT NULL, " ."network varchar(15) NOT NULL, " ."retry tinyint(1) NOT NULL, " ."FULLTEXT KEY message (message) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;" ."INSERT INTO mobackup SELECT * FROM mo;" ."DROP TABLE IF EXISTS mo;" ."CREATE TABLE mo (campaign_id varchar(20) NOT NULL, " ."number varchar(13) NOT NULL, " ."message varchar(160) NOT NULL, " ."mo_id int(15) NOT NULL, " ."shortcode int(7) NOT NULL, " ."received_time varchar(20) NOT NULL, " ."network varchar(15) NOT NULL, " ."retry tinyint(1) NOT NULL, " ."FULLTEXT KEY message (message)) ENGINE = MyISAM DEFAULT CHARSET = latin1;"; ?> and <?php $sql3 = "\n" . "DROP TABLE IF EXISTS `betfredpiri`.`mobackup`;\n" . " CREATE TABLE `betfredpiri`.`mobackup` ( `campaign_id` varchar( 20 ) NOT NULL ,\n" . " `number` varchar( 13 ) NOT NULL ,\n" . " `message` varchar( 160 ) NOT NULL ,\n" . " `mo_id` int( 15 ) NOT NULL ,\n" . " `shortcode` int( 7 ) NOT NULL ,\n" . " `received_time` varchar( 20 ) NOT NULL ,\n" . " `network` varchar( 15 ) NOT NULL ,\n" . " `retry` tinyint( 1 ) NOT NULL ,\n" . " FULLTEXT KEY `message` ( `message` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;\n" . "\n" . "INSERT INTO `betfredpiri`.`mobackup` SELECT * FROM `betfredpiri`.`mo`;" . "DROP TABLE IF EXISTS `betfredpiri`.`mo`;\n" . " CREATE TABLE `betfredpiri`.`mo` ( `campaign_id` varchar( 20 ) NOT NULL ,\n" . " `number` varchar( 13 ) NOT NULL ,\n" . " `message` varchar( 160 ) NOT NULL ,\n" . " `mo_id` int( 15 ) NOT NULL ,\n" . " `shortcode` int( 7 ) NOT NULL ,\n" . " `received_time` varchar( 20 ) NOT NULL ,\n" . " `network` varchar( 15 ) NOT NULL ,\n" . " `retry` tinyint( 1 ) NOT NULL ,\n" . " FULLTEXT KEY `message` ( `message` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;\n"; ?> Both adaptations of PHPMyAdmin's export to a PHP statement. Any ideas would be most welcome! I'm tearing my hair out over this. Jamie