Resetting a table from a backup copy...

[metazai]

I have a problem with a MySQL/PHP script. It's supposed to take a passed table name, and use it to drop the table and copy the default backup table (with data) to replace it (a sort of reset). The trouble, of course, is that it's not working. IS there something obvious I'm just missing?

PHP Code:

<?
$lnk = mysql_connect('localhost', 'username', 'password') or die (mysql_error()); 
mysql_select_db('databasename',$lnk); 
$daydata=$_POST['passdate'];
$sqldrop = "DROP TABLE $daydata"; 
$sqlreset = "CREATE TABLE '$daydata' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL default '0', 'end_time' int(11) NOT NULL default '0', 'time_label' text NOT NULL, 'name' text NOT NULL, 'image' text NOT NULL, 'info_link' text NOT NULL, PRIMARY KEY ('id')) TYPE=MyISAM; INSERT INTO '$daydata' SELECT * FROM '".$daydata."_Template'"; 
echo ($sqldrop);
echo ($sqlreset);
mysql_query($sqldrop) or die(mysql_error()); ; 
mysql_query($sqlreset) or die(mysql_error()); ;
echo("The Schedule for ".$daydata." has been updated.</span>");
?>

[sde]

are there any errors when you run that sql query directly from mysql? or phpmyadmin?

[metazai]

Yes: phpmyadmin ran the drop fine (and so has the script on the page), but the second part, when I replace every instance of '$daydata' with, for instance 'Friday' (one of the names of the tables), I get:

You have an error in your SQL syntax near ''Friday' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL' at line 1

[sde]

i don't know if this is gonna be correct, but i'd first try to isolate my variables:

PHP Code:

$sqlreset = "CREATE TABLE '" . $daydata . "' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL default '0', 'end_time' int(11) NOT NULL default '0', 'time_label' text NOT NULL, 'name' text NOT NULL, 'image' text NOT NULL, 'info_link' text NOT NULL, PRIMARY KEY ('id')) TYPE=MyISAM; INSERT INTO '" . $daydata . "' SELECT * FROM '".$daydata."_Template'"; 


then, i have never used 2 queries in 1, so i would try to seperate the last query into 2

PHP Code:

$sqlreset = "CREATE TABLE '$daydata' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL default '0', 'end_time' int(11) NOT NULL default '0', 'time_label' text NOT NULL, 'name' text NOT NULL, 'image' text NOT NULL, 'info_link' text NOT NULL, PRIMARY KEY ('id')) TYPE=MyISAM";
$sqlinsert = "INSERT INTO '$daydata' SELECT * FROM '".$daydata."_Template'"; 


last, i didn't think that mysql supported sub-queries, but i guess i'm wrong if it works in phpmyadmin.

[metazai]

What do you mean by "sub-queries"?

[sde]

INSERT INTO '$daydata' SELECT * FROM '".$daydata."_Template'

that, .. i thought the 'select' inside an insert is a subquery.

[metazai]

Yeah, that line came from directly from phpmyadmin, actually (I used their PHP code creator button, under "Operations" for a table, to copy structure and content from the table to another with a different name, then I stripped out the fixed name and put in a variable.

Breaking down the queries doesn't seem to help. Again, it runs the first, the DROP, with no problem, but this code:

PHP Code:

<?
$lnk = mysql_connect('localhost', 'username', 'password') or die (mysql_error()); 
mysql_select_db('database',$lnk); 
$daydata=$_POST['passdate'];
$sqldrop = "DROP TABLE $daydata"; 
$sqlreset = "CREATE TABLE '$daydata' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL default '0', 'end_time' int(11) NOT NULL default '0', 'time_label' text NOT NULL, 'name' text NOT NULL, 'image' text NOT NULL, 'info_link' text NOT NULL, PRIMARY KEY ('id')) TYPE=MyISAM"; 
$sqlinsert = "INSERT INTO '$daydata' SELECT * FROM '".$daydata."_Template'";
mysql_query($sqldrop) or die(mysql_error());
mysql_query($sqlreset) or die(mysql_error());
mysql_query($sqlinsert) or die(mysql_error());
echo("The Schedule for ".$daydata." has been updated.</span>");
?>

Gives me this error message:

You have an error in your SQL syntax near ''Friday' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL' at line 1

[sde]

did you try isolating the variable? i mean breaking the string up with a "beggining of string" . $variable . "rest of the string";

" . $daydata . "

[metazai]

Oh, sorry . . . yes, I'd tried it before as well, but I just tried it again:

PHP Code:

$sqldrop = "DROP TABLE $daydata"; 
$sqlreset = "CREATE TABLE '".$daydata."' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL default '0', 'end_time' int(11) NOT NULL default '0', 'time_label' text NOT NULL, 'name' text NOT NULL, 'image' text NOT NULL, 'info_link' text NOT NULL, PRIMARY KEY ('id')) TYPE=MyISAM"; 
$sqlinsert = "INSERT INTO '".$daydata."' SELECT * FROM '".$daydata."_Template'"; 


Resulting in this:
You have an error in your SQL syntax near ''Friday' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL' at line 1

[sde]

when you run the query in phpmyadmin, it should print out the query up top after it is executed.

can you paste it as phpmyadmin re-prints the query?

then, also paste an echo $sqlreset; to compare it to.

[metazai]

Ok, here's from phpadmin:

Error

SQL-query :

CREATE TABLE 'Friday'(

'id'tinyint( 4 ) NOT NULL auto_increment,
'start_time'int( 11 ) NOT NULL default '0',
'end_time'int( 11 ) NOT NULL default '0',
'time_label'text NOT NULL ,
'name'text NOT NULL ,
'image'text NOT NULL ,
'info_link'text NOT NULL ,
PRIMARY KEY ( 'id' )
) TYPE = MyISAM

MySQL said:


You have an error in your SQL syntax near ''Friday' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL' at line 1





And here's the echo:

CREATE TABLE 'Friday' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL default '0', 'end_time' int(11) NOT NULL default '0', 'time_label' text NOT NULL, 'name' text NOT NULL, 'image' text NOT NULL, 'info_link' text NOT NULL, PRIMARY KEY ('id')) TYPE=MyISAMYou have an error in your SQL syntax near ''Friday' ('id' tinyint(4) NOT NULL auto_increment, 'start_time' int(11) NOT NULL' at line 1

[sde]

you are getting that error in phpmadmin then too .. ?? i thought it was working fine in phpadmin.

i think you need back-ticks around your table and column names

PHP Code:

CREATE TABLE `Friday` (
`id` TINYINT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`start_time` INT( 11 ) UNSIGNED DEFAULT '0' NOT NULL ,
`end_time` INT( 11 ) UNSIGNED DEFAULT '0' NOT NULL ,
`time_label` TEXT NOT NULL ,
`image` TEXT NOT NULL ,
`info_link` TINYTEXT NOT NULL ,
PRIMARY KEY ( `id` )
); 


[sde]

yes, back ticks or nothing at all:

PHP Code:

CREATE TABLE Friday (
id TINYINT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
start_time INT( 11 ) UNSIGNED DEFAULT '0' NOT NULL ,
end_time INT( 11 ) UNSIGNED DEFAULT '0' NOT NULL ,
time_label TEXT NOT NULL ,
image TEXT NOT NULL ,
info_link TINYTEXT NOT NULL ,
a TINYINT NOT NULL ,
PRIMARY KEY ( id )
); 


[metazai]

Perhaps I should also post this . . . this is the readout created when the function is executed correctly through phpmyadmin. I've replaced the database name with DATABASENAME, as it would give away something about the site I'm not allowed to, but other than that it's verbatim.

I have tried to rephrase the query with these 'DATABASENAME'. extensions with still no luck. I originally trimmed them out because of this line in my own code:

mysql_select_db('databasename',$lnk);

CREATE TABLE `DATABASENAME`.`Friday` (
`id` tinyint( 4 ) NOT NULL auto_increment,
`start_time` int( 11 ) NOT NULL default '0',
`end_time` int( 11 ) NOT NULL default '0',
`time_label` text NOT NULL ,
`name` text NOT NULL ,
`image` text NOT NULL ,
`info_link` text NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = MyISAM ;

INSERT INTO `DATABASENAME`.`Friday`
SELECT *
FROM `DATABASENAME`.`Friday_Template`

[metazai]

Sorry, just saw your post. Back ticks? Really? Let me try it.