I'm dumping a Mysql database ( for a backup on my local harddrive) using PhpMyadmin, This works fine. The trouble is when I re-upload the .sql file I get an error if the user had entered a # sign in any of 40+ text form fields. I think the Mysql sees these as comments in the middle of the values causing it to ignore the closing of the command.
INSERT INTO table ( field1, field2, field3 )
VALUES ( 'data', 'any text #5 train' , NULL);
All I can think to do is use a cumbersome list of eregi replaces for 40+ fields that are displayed as html and as a repopulated form data (for updating the form by the user) and there are 3 different types of these long forms...+ preview displays, on 3 diferent pages.This is an awful lot of replacing and re-replacing.....
Anyone know a quick and easy(LOL ) way to get Mysql to ignore a # tag if within single quotes in a list of values?
The above code was just an example to show what I was talking about,
and it will insert into th DB fine UNLESS....
it is being uploaded(executed with phpMyadmin) from a .sql file on my hard drive.
The first part of the actual code is below and it errors at the # sign in the values...
I really tried your script on my phpMyAdmin 2.3.3pl1 with MySQL 3.23.54, I tried to insert data into table by running query with Insert in SQL block and from text file, and in both cases it worked fine, inserted data correctly and didn't generate any errors.
I don't know what may be the problem with your phpMyAdmin
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( CONCAT(CHAR(35),'anyname@cs.com'));
CHAR(35) will return the ASCII character number 35, which is "#". If the pound sign is truly making phpMyAdmin think the rest of that line is a comment, the above insert statement should work.
If that turns out to be the case, maybe you need to upgrade your phpMyAdmin to at least Elena's version.
My host is using phpMyadmin 2.1.0 with MySQL 3.23.54
I think the problem you may be having in getting the error to reoccur is that there is no following statement to confuse it.
Just noticed that ...very sorry...
SQL-query:
# phpMyAdmin MySQL-Dump
# http://phpwizard.net/phpMyAdmin/
#
# Host: localhost:0000 Database : database1
# --------------------------------------------------------
#
# Table structure for table 'advertiser_mail_list'
#
DROP TABLE IF EXISTS advertiser_mail_list;
CREATE TABLE advertiser_mail_list (
advertiser_email_address varchar(100) NOT NULL,
PRIMARY KEY (advertiser_email_address)
);
#
# Dumping data for table 'advertiser_mail_list'
#
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( '#anyname@cs.com');
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( 'anyothername@cs.com');
If thats not it, maybe its a server setting or something. It is definitely happening though....
If I remove the # sign by hand it successfully executes. Put it back and it errors
Either way you have been very gracious in your help!