inserting a # into a mysql database

[Anon]

Hi There,

I did a mysql dump from a database and now I am using that info to create another database. However one of the lines is causing an error:

INSERT INTO packagers VALUES ( \'200001\', \'Cavendish Maples\', \'RR#2 Hunter River\', \'C0A 1N0\', \'963-5689\', \'7\', \'37.25\');

The problem lies with the #

How can I get around this?

Thanks,

-dr

[Anon]

Actually the # isn't the problem... but I don't know what is... here's the scoop.

here is the sql I am trying to enter:

CREATE TABLE packagers (
id mediumint(9) DEFAULT \'0\' NOT NULL auto_increment,
name varchar(225) NOT NULL,
address varchar(225) NOT NULL,
postal varchar(10) NOT NULL,
tel varchar(15) NOT NULL,
booked mediumint(9) DEFAULT \'0\' NOT NULL,
price float(5,2) DEFAULT \'0.00\' NOT NULL,
PRIMARY KEY (id)
);

#

Dumping data for table \'packagers\'

#

INSERT INTO packagers VALUES ( \'200000\', \'Anne\\'s Windy Poplars\', \'Cavendish\', \'C0A 1N0\', \'963-3166\', \'15\', \'34.50\');
INSERT INTO packagers VALUES ( \'200001\', \'Cavendish Maples\', \'RR#2 Hunter River\', \'C0A 1N0\', \'963-5689\', \'7\', \'37.25\');
INSERT INTO packagers VALUES ( \'200002\', \'Avonlea Cottages\', \'Cottonwood Drive\', \'C1A 3L4\', \'566-6598\', \'6\', \'33.95\');

Any help would be appreciated.

Thanks

[Anon]

Well, when I enter them each individually (each of the three rows), it works fine. But when I use phpmyadmin to enter all three at once I get:

Error

SQL-query:

INSERT INTO packagers VALUES ( \'200000\', \'Anne\\'s Windy Poplars\', \'Cavendish\', \'C0A 1N0\', \'963-3166\', \'15\', \'34.50\');
INSERT INTO packagers VALUES ( \'200001\', \'Cavendish Maples\', \'RR#2 Hunter River\', \'C0A 1N0\', \'963-5689\', \'7\', \'37.25\');
INSERT INTO packagers VALUES ( \'200002\', \'Avonlea Cottages\', \'Cottonwood Drive\', \'C1A 3L4\', \'566-6598\', \'6\', \'33.95\');

MySQL said: You have an error in your SQL syntax near '200002', 'Avonlea Cottages', 'Cottonwood Drive', 'C1A 3L4', '566-6598', '6', '33' at line 1

any help.. thanks,

-dr

[[deleted]]

It is probably a silly answer, but phpmyadmin is not exactly known for it's high quality. It make a sh*tload of mistakes with escaping characters etc.
Consider using the commandline or mysqlfront for win32

[Anon]

Does MyPHPAdmin\'s mysqldump function work accurately? ie, is it high enough quality that it produces the same result as running mysqldump from the commandline?

Is there an option to mysqldump that will dump all the mysql databases at once rather then have to run mysqldump 10 times for ten different databases?

Thanks,

-dr

[[deleted]]

Nope. phpmyadmin has nasty problems with addslashes and stripslashes. Use the commandline instead.

Dumping all databases at once is something you normally would never ever want to do, because the output file could be well into the gigabytes in size...

Instead, write a small script that dumps the databases one by one into seperate files.

[Anon]

good idea...

I could setup a cron job to do that daily and then perhaps FTP that backup file to an offsite computer.

Do you know any any such scripts? I guess I could always make my own if there isn't anything out there.

Thanks!

-dr

[Anon]

The option --all-databases makes a dump of the whole mysql-database-pool...

Dico Reyers wrote:

Does MyPHPAdmin\'s mysqldump function work accurately? ie, is it high enough quality that it produces the same result as running mysqldump from the commandline?

Is there an option to mysqldump that will dump all the mysql databases at once rather then have to run mysqldump 10 times for ten different databases?

Thanks,

-dr