PHP/MySQL Error

[cjgwin]

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

[Muddy_Funster]

you shouldn't script multiple queries within a single PHP string.  execute each query individualy.  so for each ";" in your $sql string, you should perform an independant mysql_query().

[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!