[RESOLVED] Database backup script

[JeremyA]

I created a script that outputs the following code into a PHP file for EACH table in the database. I am using this as a "ready to run" backup script that i can just include and it will drop all tables, recreate the tables and then reinsert each value.

For some reason i can only get the code to drop the tables, any help is much appreciated.

mysql_query("DROP TABLE IF EXISTS usr");
$sql="CREATE TABLE page (pge string(1) not_null primary_key, mt string(10000) not_null, mim string(50) not_null, imh string(3) not_null, imw string(3) not_null, tile string(100) not_null, mh string(100) not_null, cue string(1) not_null, su string(1) not_null, PRIMARY KEY (page)) TYPE=MyISAM";
mysql_query($sql);

mysql_query("INSERT INTO usr VALUES ('1', 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <span style=\"font-weight: bold;\">sed diam voluptua</span>. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, <span style=\"font-style: italic;\">consetetur sadipscing elitr</span>, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. <br><ul><li><span style=\"font-weight: bold;\">ro eos et accusam eAt vet justo duo dolores et ea rebum</span>.</li><li>&nbsp;Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. <br></li><li style=\"font-weight: bold;\">Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.</li><li>&nbsp;At vero eos et accusam et justo duo dolores et ea rebum. <br></li><li style=\"font-weight: bold;\">Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.</li></ul>Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. <br>', 'StandingHandInPocket.JPG', '350', '233', 'Home', 'Welcome!', 'n', 'n',)");
mysql_query("INSERT INTO page VALUES ('2', 'asd<span style=\"font-weight: bold;\">fgafgadfg</span><br>', NULL,NULL,NULL,'page 2', 'header2', NULL,NULL)");
mysql_query("INSERT INTO page VALUES ('5', '<br>', NULL,NULL,NULL,NULL,NULL,'y', NULL)");
mysql_query("INSERT INTO page VALUES ('3', NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)");
mysql_query("INSERT INTO page VALUES ('4', NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)"); 

Thanks,
Jeremy

[scragar]

have you tried running:

mysql_query($sql) or die(mysql_error()); 

?

if you get an error message it's much easier to find the problem.

[chazzy]

if you're debugging something, always add debug information

mysql_query("DROP TABLE IF EXISTS usr") or die("Unable to drop usr table: ".mysql_error()); 

Of course you would remove that once you know it's working.

Does the user you're connecting as have the DROP privilege?

why is the rest of the script about the "page" table?

[JeremyA]

oh that was a typo on my part, ill try and debug and see what comes up...

Yeah the user im logged in as has full privileges

[JeremyA]

It just errors the MySQL syntax, there is no specific 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 'string(100) not_null, conm string(100) not_null, lk1 string(100) not_null' at line 1

and as i go down the line and remove the create table code it just errors the next one in line.

[scragar]

1 quick point, that bit isn't in the code you posted, no chance your missing part out that would be helpful is there?

[JeremyA]

its basically the same, the script outputs the same for each table so it should be uniform. Also it errors on EVERY table creation when i take out the previous one it will still error. But here is the code the above error came from...

mysql_query("DROP TABLE IF EXISTS coating");
$sql="CREATE TABLE coating (bri string(100) not_null, conm string(100) not_null, lk1 string(100) not_null, lk2 string(100) not_null, lk3 string(100) not_null, lk4 string(100) not_null, lk5 string(100) not_null, bt1 string(100) not_null, bt2 string(100) not_null, ba string(10) not_null, btcol string(7) not_null, nlettx string(300) not_null, lgo string(100) not_null, lgow string(3) not_null, lgoh string(3) not_null, sh1 string(15) not_null, sh2 string(15) not_null, id string(1) not_null primary_key, PRIMARY KEY (id)) TYPE=MyISAM";
mysql_query($sql) or die(mysql_error()); 

[scragar]

you do realise that NOT NULL should be done with a space not an underscore right(which I can't believe I didn't notice before)?

You also realise that string is not a valid field type as well, right? it's either VARCHAR for variable up to the length, or CHAR which is fixed to the length(CHAR is quicker to access since there's less reading, but it's also more space...)

[JeremyA]

oh wow, im surprised i didnt catch that as well. along with the sting() thats an error on page which finds the field type.

Thanks for your help, sorry for not looking over my more carefully :/